I have a script which executes a stored procedure from my oracle database and spools the results to a file. Now I want to spool the files to a unix file path. I'm executing the script using TOAD. How do I specify the unix file path? Or is there an alternative way to do this?Here is the script:set linesize 512 set colsep ""set pagesize 0set newpage noneset feedback offset termout offset recsep offset sqlprompt ""set heading offset echo offset serveroutput onset trimspool onexec DBMS_OUTPUT.enable(10000);spool /u02/TEST/test.LSTexec generate_test;spool offquit
HelloI ahve a field called name where there are entries like "HELLO - THERE - MR" and "HOW ARE-U". I need to remove the spaces around the hypen only. Does someone know how to do this?
Where can I specify the location of the SQL*PLUS edit/spool directory such that I can change it from the default? I'm running on Linux (Ubuntu 6.10) if that helps...
I'm currently using sql plus to export data from oracle to text with spool command, in this way:sqlplus user/[email protected] @F:\pathtosql\query.sql (this line is in a bat file)where the file query.sql contain the spool comandspool F:\textfile.txt;set head on;set pagesize 0;set linesize 512;set trimspool on;set feedback off;set termout off;select field1, field2, ..., fieldnfrom table;spool off;exit;mi problem is when I run .bat (contining the sqlplus command) and for some reason the source database is not available the windows remain open requesting for a logon information, I want the window
I'm just about to lose my mind.I'm running the following in SQL Plus - set echo offset feedback offset linesize 1000set sqlprompt ''spool c:\TEMP2\App_Eng1.csv replaceSelect A.OBJECTOWNERID||','||B.OBJECTVALUE1 from PSPROJECTITEM B, PSAEAPPLDEFN AWhere B.PROJECTNAME = 'PRJ732075'and B.SOURCESTATUS = 2and B.TARGETSTATUS = 1and B.OBJECTVALUE2 = ' 'and B.OBJECTTYPE=33and A.AE_APPLID = B.OBJECTVALUE1spool off and I get the following in return - SQL> set echo offSQL> set feedback offSQL> set linesize 1000SQL> set sqlprompt ''spool c:\TEMP2\App_Eng1.csv replaceSP2-0024: Nothing to change
When I have done like below,C:\Documents and Settings\himanshu.chaudhari>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 14 07:38:22 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn sys@dev as sysdbaEnter password:Connected.SQL> spool e:/spool/test.logSQL> @e:/spool/ms_script.sqlGot error like ,Oracle SQL*PLUS has encountered a problem and needs to close. We are sorry for the inconvenience.AppName: sqlplus.exe AppVer: 0.0.0.0 ModName: oranls10.dllModVer: 10.2.0.1 Offset: 0005d6ffI am using,Operating System: Microsoft Windows XP Professional Ver
I have a user who is either able or unable to spool when running a script depending on what Oracle user she logs in as. She's always logging in as the same Linux user but connecting to Oracle as different Oracle users via SQL Plus. When spool files are created they are created with that user's account and group settings, so the OS account seems to be fine. I'm having a problem with the idea that there is an Oracle role or something that controls this. Spool isn't UTL_FILE. Is this a SQL Plus application setting or something? We're using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Help Please,I have follwoing Oracle SQL, I want to convet it to DB2 SQL. Thanks in advance.SELECT nl.title, NVL(TO_CHAR(ac.currentcy_date,'MM/DD/YYYY'),' '), NVL(TO_CHAR(ADD_MONTHS(ac.currentcy_date, co.current_cy_interval),'MM/DD/YYYY'),'Needs Initial Review'), n.menu_position FROM appl_currentcy_review ac, currentcy_review_sections crs, navbar_links nl, navbars n, appl_profile ap, company co WHERE ac.appl_id(+) = '+ applID +' --java varAND ac.currentcy_section_link_id(+) = crs.currentcy_section_link_id AND crs.currentcy_section_link_id = nl.link_id AND nl.link_id = n.link_id AND n.n
What User name and Pass word I am going to use since I cannot log on to Oracle SQL*Plus. What shoul I do?
I've searched....and i've searched...where would i find that beautiful Oracle SQL reference guide for 8,9,10 in HTML....like this bootleg I found:http://www.daimi.au.dk/~oracle/sql/or is there such a thing anymore?
Can anyone please let me know is there any clause with the DB2 LOAD utility that is compatible with the Oracle SQL*LOADER WHEN clause in the control file? I am currently using Oracle SQL*LOADER to import records from the log file. We are using WHEN clause in the SQL* LOADER control file to specify selection criteria. We are unable to find similar selection criteria with the DB2 LOAD command.
I am trying to download the windows version of ORACLE SQL DEVELOPER , after I accept the license the server times out almost immediately. This has been happening for over a week.
Is there an equivalent of the following document:Oracle® DatabasePL/SQL User's Guide and Reference10g Release 2 (10.2)B14261-01for Oracle SQL. I have downloaded:Oracle® DatabaseSQL Reference10g Release 2 (10.2)B14200-02but the latter is more of a reference than a tutorial. I like the tutorial nature of the PL/SQL Guide and Reference and would love to have a similar document for SQL.
I just wanna ask if where i can download Oracle SQL*Plus help related like word help.?ty
I created a store procedure with one input and one output parameter. Output parameter is BLOB.I am calling this procedre by java and inserting the value of that into Oracle table. When i am runing this progarme i am getting the following error."com.ibm.db2.jcc.a.be cannot be cast to oracle.sql.BLOB".Please help me to reslove i am new to DB2. Below is the extract of Java code. I am using Type 4 driver. static void callblobSP(Connection sourceConn, Connection targetCon) {String procName = null;String sql = null;ResultSet rsSource = null;CallableStatement callStmt = null; try{Blob outPicture = nu
Can anyone please let me know is there any clause with the DB2 LOAD utility that is compatible with the Oracle SQL*LOADER WHEN clause in the control file? I am currently using Oracle SQL*LOADER to import records from the log file. We are using WHEN clause in the SQL* LOADER control file to specify selection criteria. We are unable to find similar selection criteria with the DB2 LOAD command.
Oracle SQL Developer 1.5.4 (1.5.4.59.40)[Released 03 March 2008]2008 --> 2009
HelloI try to connect to a SQL Server 2005 Database under Oracle SQl Developper but I always obtain this error :oracle.jdeveloper.cm.CMException: Unable to find driver: net.sourceforge.jtds.jdbc.Driverat oracle.dbtools.raptor.conntypes.RaptorConnectionWrapper.createConnection(RaptorConnectionWrapper.java:164)at oracle.dbtools.raptor.conntypes.RaptorConnectionWrapper.getJdbcConnection(RaptorConnectionWrapper.java:191)at oracle.dbtools.raptor.conntypes.RaptorConnectionWrapper.getPresentation(RaptorConnectionWrapper.java:66)at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.createConnectio
HiI am trying to download the Oracle SQL Developer Data modeler viewer but get an error (404) every time?I am following the link at http://www.oracle.com/technology/software/products/sql/datamodeler.html after accepting license.I am using IE v7.0 and I tried the SQL Developer tool and got the same error??Any ideas.....Edited by: user6765482 on 17-Nov-2009 04:27
I am new to this Forum.. and intrested in learing SQL , Could any one Suggest me how to start learning Oracle SQL Soon.
Since this morning, I try to download Oracle SQL Developer 2.1I tried the 32 bits version with and without JDK, unsuccessfully...... is there a probleme with it ?
Suraj Neupane said: I am dumping SQL for some reports.DB is Oracle 9i and I'm sending date prompt from Cognos to the SQL.The prompt is from the calendar and it sends date as yyyy-mm-dd instead of Oracles dd/mon/yy format.Some field in oracle are string value converted to date such as to_date(oracle_string,'mm/dd/rr')The only way I found to pass the cognos date is like this:and to_char(oracle_date,' yyyy-mm-dd') between #prompt('StartDate')# and #prompt('EndDate')# and it works fine.In the string's case, it doesn't work. I tried and to_date(cd.text_value,'mm/dd/yy') = to_date('01/22/07','mm/dd/
We have 10.2.04 RAC database running in production.The application does lot of DML.I need to tune query regularly.Could anybody sugggest a good book for Oracle SQl tuning.Thanks and Regards,jitenderEdited by: user10552550 on Feb 22, 2010 5:53 AM
I just need free e-course materials for Exam #1Z0-001 Introduction to Oracle: SQL and PL/SQL. In which website I have to go & download the course materials.I don't need question paper.I just searched in oracle.com website but I didn't get it And I would appreciate if you can tell me in which website I have to go.
I am trying to learn Oracle and make sure my SQL is correct for my database. I would like to convert an Access DB to SQL scipt that ORacle can read. I tried to convert the NorthWind DB in Access to SQL via the MS SQL Server. This produced an SQL file, but I don't think I run that in my Oracle SQL Plus. It does not give me Create TAbles or anything.I want to build the Northwind Access DB in Oracle as a learning device, but I need that SQL first.Any help?rbgerman
can anybody let me know the link to documentation of Oracle SQL Developer 11G admin guide ?
there how would i change the following oracle sql code to t-sql. select NEXT_DAY(((TO_DATE(T1."DDATE" ,'yyyymmdd' )) + (-1)),'Sunday'), T2."TWARD", DECODE(LEAST(RTRIM(T1."DTIME" ) ,'11:00:00'),NULL,0 ,RTRIM(T1."DTIME" ),1,0), T1."DURNO"from "IBA"."LIVE_PATDSCHF" T1, "IBA"."LIVE_PATMI1AF" T3, "IBA"."LIVE_PATTRANF" T2where T3."DAADMNO" = T2."DTADMN"(+) and T2."TWARD" in ('ADA', 'ALE', 'AMO', 'BOB', 'CAT', 'DOL', 'ELI', 'EMU', 'FEL', 'CCU', 'LYD', 'MAR', 'MEL
I am planning to do the certification oracle sql/plsql. So please tell any one oracle sql/plsql Certification model has changed or not. If changed when is changed and whats the syllabus now. Please provide any help full materials to [email address removed by moderator for security purposes]Thanks for all your supportEdited by: user11254359 on Aug 17, 2010 5:01 AMEdited by: Certification Forum Moderator on Aug 26, 2010 11:11 AM
I thought there used to be an Oracle SQL Forum out here....Is there and where can I find it???
is there any exam free of cost for oracle sqlthansk
i kinda new with SQLserver2008..currently i have a code that generates some function and its work on oracle SQL. What i should do now is how to convert the code to MSSQL server code. Kindly need all of guru's out there to guide me on this. Here are the codesQuote: create or replacePROCEDURE A (tbl_name varchar2 default ' ',l_name varchar2 default ' ',f_name varchar2 default ' ',full_name varchar2 default ' ',ssn varchar2 default ' ',gender_col varchar2 default ' ',employee_id varchar2 default ' ') ISv_tbl varchar2(30) := tbl_name;v_lname varchar2(30) := l_name;v_fname varchar2(30) := f_name;v_
I installed Oracle 8 for Windows NT 4.0 and Oracle SQL*Plus version 8.0.3.0.0Oracle is new to me. When I did a select * from emp, SQL can't find the table.What I did wrong?
I am doing some integration from Oracle to MySQL and need to know the quivelent MySQL sql for the following Oracle sql:select trunc(sysdate) from dual Basically selecting the current system date without the time part (ie. 13/02/2007).
Can any one send me all the major diffs between oraclesql server, msaccess.If u find any urls also send me those.If u worked on oracle or sql server and if u find any changes( syntax)also send me those.This is mainly for interview
I have downloaded Oracle 8.0.6.0.0 from this site and installed it. I have not been able to logon to Oracle SQL*Plus. I have tried to log as scott/tiger and used all possible values for the host string but nothing seems to work. The summary of the host string used and the error messages is:Username: scottpassword: tiger1. host string : blankerror message : ora-12203 : TNS: Unable to connect to destination2.host string: ODBC:POLITEerror message : ORA-03121 : No interface driver connected - function not performed3. host string: example1.worlderror message: ORA-12545 :Connect failed because targe
guys... Need some help with my queryI converted some Access query to Oracle, but I'm not getting the same number of records. Below are the 2 queries :Oracle SQLSELECT *FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR CNTR, TOPS.NOMINATED_VSL NOMWHERE ((VES.VV_CD = BERTHING.VV_CD) AND (VES.VV_CD = CNTR.DISC_VV_CD) AND (CNTR.NOM_LOAD_VV_CD = NOM.NOM_VV_CD(+)) AND (CNTR.LOAD_VV_CD = VES.VV_CD(+)))Access SQLSELECT *FROM (((TOPS_VESSEL_CALL INNER JOIN TOPS_BERTHING ON TOPS_VESSEL_CALL.VV_CD = TOPS_BERTHING.VV_CD) INNER JOIN TOPS_CNTR ON TOPS_VESSEL_CALL.VV_CD = TOPS_CNTR.DISC_VV_CD) LEF
I am new user of oracle and is learning. And now I am working for a project which use oracle8.17.when writing stored procedure and trigger, I found I can not perform byte operation in oracle sql. For example,when '7 and 3', it shold come out with 3.But how can I do this in oracle sql?Is anyone can help me?
Does Oracle have any PDF documents solely on the topics of Oracle SQL & PL/SQL? I've located Oracle's general documentation (http://download-west.oracle.com/docs...1_01/index.htm), but I'm looking for something more consolidated that I can point our developers to.
Can anyone recommend a proprietry tool that performs impact analysis / reverse engineering on hand-coded Oracle SQL statements, so that for each SQL script, it determines exactly how it performs, in terms of what tables / columns are accessed or affected, column mappings on INSERT statements, etc., into some sort of repository ? To give an example, if I had the below example SQL statement :INSERT INTO ordered_products( product_id,product_name,store_id,store_name,number_ordered)SELECT /*+ PARALLEL (p 2) */p.product_idp.product_name,s.store_id,s.store_name,SUM(ol.order_quantity) number_orderedFR
anyone know how to chage this informix sql to oracle sql? select cust_name[1,40] from customer;the original cust_name is char(60), i just want its first 40 character. how to do that?thanks for ur help! best
Can someone throw light on oracle.sql.* pacakge. There are tons of classes available like LobPlsqlUtil but I don't find any proper documentation on usage. Is it possible to use these Classes to write to CLOB or do we have to use only DBMS_LOB pacakge in Java, which causes some performance issues. Any help would be really appreciated.
Hello, I wanted to know if Oracle SQL Plus 8 was compatible with windows 2000 ?If yes, are there any bugs Known ?
I just want to convert a simply String or Document type into the Oracle CLOB datatype, because I use a stored Procedure with an CLOB argument.(not to mix with writing a CLOB into a oracle database).I need some help, I tried this here:public void setdataPackage(Document data, float cid) {Connection conn = null;CallableStatement cs = null;try {conn= pool.getConnection(2000);conn.setAutoCommit(false);CLOB newClob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_CALL);newClob.setString(1, data.toString());if(conn != null){cs = conn.prepareCall("{call SETDATAPACKAGE(?, ?)");tr
It's been hard pinning down exactly what exams have to be taken first, but from what I've seen, you have to take an Oracle SQL exam and a DBA fundamentals exam to get the OCA certification, right?What study materials do you recommend for the Oracle SQL exam? Sorry if this has been posted before.
Hello, I'm developing web app with PHP and MySQL. And some how I also have to use Oracle DB. I'm just very new to Oracle/ Oracle SQL and have a problem to limit number of data to show for search result.My problem is how to do search result pagenation with Oracle/Oracle SQL.MySQL is simply use LIMIT clause.I found out that I can use ROWNUM to limit search result to output.So, I've tried following SQL statement.Limit 20 per page and 25 total result for this example.SELECT * FROM RECORD WHERE (ROWNUM >= 1 AND ROWNUM <= 20) AND AVAIL='Y" ORDER BY DATE DESCThen netxt page query will be:S
Can someone please point me to a good online resource that has code samples on oracle sql.I have written MS SQL stored procedures but oracle has differences that I need to clear up.How do I call a stored procedure within a stored procedure?I tried putting this inside a stored procedure but it fails.call FA.SP_SCRIPT_A('123','TRTD000.1');
where can i get practice exams for Oracle SQL & PL/SQL? any web site please.
I keep getting error messages when i am using ORACLE SQL DEVELOPER with oracle database. Do you guys know is it just me or it has bugs?Raju
I am trying to pass an array from java to Oracle. I have used this same code in other pages and it works fine. Now I am getting a stack dump:I get 500 Internal Server error:java.lang.IllegalAccessError: oracle/sql/TypeDescriptor at oracle.sql.ArrayDescriptor.initPickler(ArrayDescri ptor.java:1951) at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor. java:160)any insight would be nice. I have the (java)object binding to and oracle object oracle.sql.ARRAY inList;ArrayDescriptor desc;desc = new ArrayDescriptor("WMI_ARRAY_NTT", dbConn);inList = new ARRAY(desc, dbConn, uids);
I'm taking a database besign class but I cannot get my queries to work, this is what I've got.Using oracle SQL plus I made user name mnfg and granted all permissions, Created two tables Employee and Product with this codeCREATE TABLE employee (TEAMNAME varchar2(6), EMPNUM varchar2(6),EMPLNAME varchar2(32), EMPFNAME varchar2(20), SUPERVISOR varchar2(5), MFGLINE varchar2(10), primary key (EMPNUM));CREATE TABLE product (PRODNUM varchar2(4), PRODDESC varchar2(46), MFGLINE varchar2(1), BOMROLLUP varchar2(6), QTYONHAND varchar2(3), PRIMARY KEY (ProdNum));This is my assignment and the two tables with