Client Tools :: How To Capture Error Code In Sqlplus
Sep 10, 2013
I have a .sql file that is used as a wrapper file that when executes within sqlplus (9.2.0.1.0), executes a bunch of .sql files within it. Example below:
Each of the .sql file (A,B,C,D,E) Spools individual output of sql statment within them. Each of the indv .sql file queries different tables with different filters(where) clause.
I would like to capture any error (OS,SQL,DB) into indv error file (A_ERROR.log). The reason being is because later in the process we need to validate if there were any errors before processing and loading the data into our SQL database
I have a script which connects to Oracle and return 2 date value. few days back the database was down and when the script execute and return same garbage value in the spool file.
I added a exception handler WHENEVER SQLERROR EXIT 1 befor the connection start and after connection start. But in both the case it is also writing to the spool file.
I would like to exit the connection with a value <> 0 if any connection issue happens like (user id, password, database down error type of issue.) No information should go to the spool file
it seems to me that the file tnsnames.ora is not read when i execute sqlplus.whit the command: sqlplus username / password @ servicename i receive the error ORA-12154, while if i pass the whole connection string i can connect without problems
I am trying to do a case statement where I select to see if an ID exists or not and if it doesnt i want to run a .sql file but its not working properly.
select case when exists (select ID from VERSION where ID = '16.1') then 'ID Exists' else @script.sql end from dual;
Its not allowing me to run the @script.sql with the else statement.
I used the following command : ------------------------------------------------- D:oracleproduct10.1.0Db_2jdkinjava -Djava.security.properties=D:oracleproduct10.1.0Db_2sqlplusadminiplusprovider -jar D:oracleproduct10.1.0Db_2oc4jj2eehomejazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell --------------------------------------------------------
Output as follows: -------------------------------------------- oracle.security.jazn.JAZNRuntimeException: Configuration file "configjazn.xml" does not exist. Check your JAAS configuration settings.at oracle.security.jazn.JAZNConfig.getJAZNProperties(Unknown Source) ... ... ... Realm [iSQL*Plus DBA] does not exist in system. -----------------------------------------------
I m currently written a sqlplus query and executing as below
sqlplus -s << ! set head off pages off trimspool on termout off spool sysdate.txt select sysdate from dual; spool off !
With this query the sysdate is getting written in the sysdate.txt but it is also getting printed in the screen/terminal., which I don't need to. Is their any options available for that.
Note: I came to know that "Termout off" will work only when we execute the query from a file and as a result I can still see the sysdate getting displayed in the screen.
I need to parse .sql file to tree and I can't find how to do it.
(We apply sql files by sqlplus and we have some problems with '/': after anonymous blocks - it haven't applied without '/'; Also not-anonymous block applied twice when we have both ';' and '/'. I need to report about problems before file will apply.) I've tried to use ANTLR with it grammar but it build awful tree.
I am able to start SQLPLUS from Start | Program files | Oracle - OraHome92 | Application development |
Unfortunately, when trying to start it from the "command prompt" window with the following command SQLPLUS, it generates the infamous error message like
"... unknown etc... "
Any variables i need to change on my client machine ?
I have some problem with '/' into the .sql files: after anonymous blocks - it haven't applied without '/'; Also not-anonymous block applied twice when we have both ';' and '/'. I need to report about problems before file will apply.)
I am using Windows 7 and oracle 10g; From sqlplus* I can not use editor (using ed). but when I see the variable from DEFINE.. It shows that default editor is notepad..
I can use editor in winxp.How to get (use) editor ?
I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.
My sql file has 3 lines 3 are correct and 1 is incorrect. For example: INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING'); INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING'); INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);
Lets say file is called 1.sql
My shell script invokes this SQL as follows: (Where $File1 = 1.sql)
$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE} connect $DB_USER/$Password1@$Database1 SET AUTOCOMMIT OFF @$File1 WHENEVER SQLERROR EXIT ROLLBACK; EOF [code]......
So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.
I try to login as SYS@sid AS SYSDBA When I login via TOAD, I am able to. However, as in (1), all my attempts to login from command-line SQL *Plus fails. The error I get is ORA-1031 Insufficient Privileges Even if I am on the physical server and try to run the SQL *Plus, I get the same error -- Insufficient Privileges
Here are the environment details:
Server: Windows Server 2008 R2 Server: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Client: Windows 7 Professional Client: SQL*Plus: Release 11.2.0.1.0 Production
i do have a set of monthly process, where i need to run query one after the other. Some query will take one hours time to complete but some will take 10 hours or more to execute. My problem is that we have wait or machine shouldn't hang or log off during that time ...
I am executing the query in TOAD and windows SQL*Plus. Query can run Parallel at a time.
Is the there anyway to run the query in the background like what we do in the unix nohup command in toad?
I am using the SQL*PLUS COPY command to move the data from my database to another remote database. The data in my database also contains DATETIME format. But since COPY command cannot handle DATETIME format, I am wondering is there any workaround for this.
Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.
I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts" echo $SCRIPTS_PATH
The way to create a short cut on desktop to have an sql*plus (command line).
i.e. I want to have an short cut on desktop when I click that short cut that would connect me to a specific database with a username and password (which i supply while creating the shortcut-I would provide the database name, username and password)
I am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792'); BEGIN PACKAGE.PROC(201011,'144792'); END;
* ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- PWEEK NUMBER IN PCLIENT VARCHAR2 IN CRESULTS REF CURSOR IN/OUT
Creating thread so we can share experience on using the "Code review options" that comes along with "Toad formatter".
Personally I have used it only for formatting code but not to enforce SQL Standards. any inputs on this and if it's advisable to use this as corporate standard.
when i try to connect with sqlplus or toad then i get an error
Microsoft Windows [Version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:Users>sqlplus SP2-1503: Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly C:Users>
and i want to tell what i do before it-my all files were be converted in .FLT then i shorted out this problem after that when i tried to connect with sqlplus or toad then i get above error.and i have restarted my system but throwing same error.
