Client Tools :: How To Exit Out Of SQLPLUS
Dec 4, 2011I have a scenario where I want to exit out of SQLPLUS.
SQL> declare
2 x number(10) :=1;
3 y number(10) :=2;
4 begin
5 if x=y then
[code]...
ADVERTISEMENT
Client Tools :: SQLPLUS Is Not Coming Out With Exit Code If Not Connected
Sep 6, 2011I 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
Output=`$ORACLE_HOME/bin/sqlplus -S /nolog << EOF
CONNECT $Usr/$Pwd@$Server
WHENEVER SQLERROR EXIT 1
spool ${ROOT_DIR}/.delta_date.tmp
[code]....
Client Tools :: How To Add Value From Sqlplus
May 14, 2013I want to add a value from sqlplus.
My scenario is like that i want to add the following value
select count(*) x from tab1
i need to connect several database, and each time i need to add with previous one. like that
connect a/a@db1;
select count(*) x from tab1;
then when i connect another db
connect b/b@db2;
select count(*) x from tab1;
x will add with previous one.
Client Tools :: SQLPLUS - Then / Else Run Script
Nov 7, 2012I 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.
Client Tools :: SqlPlus Version - How To Get Bit Information
Sep 26, 2013Oracle client version whether it is of 32-bit or 64-bit.
Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation. All rights reserved.
C:>sqlplus -v
SQL*Plus: Release 10.2.0.1.0 - Production
C:>
it shows me only version not bit information.is there any other command to get it.
Client Tools :: Delimited Output From Sqlplus
Mar 3, 2010I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
Client Tools :: Sysdba Access From Sqlplus?
Feb 8, 2009I 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.
-----------------------------------------------
what may be the reason for this error?
Client Tools :: SQLPLUS Windows In Oracle 11g
Feb 27, 2011the reason for not availability of ISQLPLUS & SQLPLUSW window from oracle 11g.
View 3 Replies View RelatedClient Tools :: How To Substitute Value From A Query In Sqlplus
Apr 2, 2012I need generate a script like this.
spool loadvalues.sql
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM TABLE_NAME;
spool off;
I would like to substitute TABLE_NAME with each value from the following query;
select table_name from user_tables
where table_name like 'SALES%'
so there are about 45 SALES tables ex: SALES_IDAHO,SALES_MICHIGAN etc.so my spool file(loadvalues.sql) should look like this ex:
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_IDAHO;
sales_michingan should have script like
INSERT INTO SALES(column1, column2, column2) SELECT( COLUMN1, COLUMN2, COLUMN3) FROM SALES_MICHIGAN;
Client Tools :: Display Option In Sqlplus
Apr 12, 2010I 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.
Client Tools :: How To Define ED Command In Sqlplus
Jul 25, 2012In my sql*plus when i am writing the command ed .It was showing the error as \
SQL> ed
SP2-0110: Cannot create save file "afiedt.buf"
i tried to define the editor by using the command define_editor=notepad;
But still my problem was not solved.
Client Tools :: How To Find Username In Sqlplus
Aug 16, 2011How to find user name in sqlplus?
View 5 Replies View RelatedClient Tools :: SQLPlus Command Parser
Jul 31, 2013I 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.
Client Tools :: SQLPlus Not Starting From Command Prompt
Jan 21, 2011I 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 ?
Client Tools :: How To Handle Errors In Sql Files By SQLPlus
Jul 31, 2013I 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.)
how to handle these cases by a SQLPlus?
Client Tools :: Windows 7 / Oracle 10g - Not Getting Editor From Sqlplus
Jan 29, 2012I 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 ?
Client Tools :: How To Capture Error Code In Sqlplus
Sep 10, 2013I 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:
WRAPPER.SQL
START D:ScriptsA.sql "'04-01-2012 00:00:00'"
START D:ScriptsB.sql "'04-01-2012 00:00:00'"
START D:ScriptsC.sql "'04-01-2012 00:00:00'"
START D:ScriptsD.sql "'04-01-2012 00:00:00'"
START D:ScriptsE.sql "'04-01-2012 00:00:00'"
EXIT;
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
Client Tools :: SQLPLUS For Oracle 11 Not Rolling Back
Sep 3, 2013I 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.
Client Tools :: Can Login As SYS As SYSDBA Via TOAD But Not Via SQLPlus
Jun 21, 2013I 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
Client Tools :: Conditionals In A SQLPlus Batch File?
Oct 23, 2012From within a DOS batch file I make the following call:
sqlplus [User]/[password]@[server] @batch_script.sql [Server] [User]
Within the file batch_script.sql I have the following lines:
spool output_batch.lst
connect &&2/[password]@&&1;
@SomeOtherFile.sql
I want to change the last line to something like this:
if &&1 = 'Something' then
@SomeOtherFile1.sql
else
@SomeOtherFile2.sql
end if;
I get the following result
SQL> if &&1 = "something" then
SP2-0734: unknown command beginning "if &&1 = "..." - rest of line ignored.
I took a shot at using the "If" statement and missed.
Client Tools :: Run A Query In Background In Toad Or Sqlplus
Feb 3, 2011i 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?
Client Tools :: Changing DB Password Using SQLPlus Command Line
Jul 21, 2010I'm trying to change a password. I can connect to the db then at the SQL prompt I'm using:
alter user <name> identified by <new password>
The dos window comes back with '2' - then just sits there
Client Tools :: Handling DATETIME Format Using SQLPLUS COPY Command
Jul 29, 2012I 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.
Client Tools :: Sqlplus - Multiple Scripts In Argument (batch Execution)
Aug 13, 2008I have two scripts (test1.sql and test2.sql) that need to be executed one after the other. I would like to call them both using a single command line:
sqlplus testuser/testpwd@testdb @c: est1.sql @c: est2.sql
This does not work
creating a test3.sql script that calls test1.sql and test2.sql is not an option.
Client Tools :: Display Clob Column In Text File In Sqlplus
Mar 30, 2012I 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
[Code]....
Client Tools :: Sqlplus Error ORA-12154 - TNS - Resolve Connect Identifier Specified?
Feb 24, 2010it 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
sqlplus username/password@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oradev10)(PORT = 10520))(CONNECT_DATA = (SERVICE_NAME = D10)))
i set the ORACLE_HOME environment variable correctly... what more i have to do? following is my tnsnames.ora
TOTEMPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ttf19.cern.ch)(PORT = 1521))
[code]...
Client Tools :: Create Shortcut On Desktop To Have SQLPLUS (command Line)
Sep 6, 2010The 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)
Client Tools :: SQLPLUS - How To Execute Store Proc When Passing Only 2 Out Of 3 Parameters
Feb 22, 2011I 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
After searching a bit, i try the following:
SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>
This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
AUTHID CURRENT_USER
AS
--
TYPE CurTyp_Supp IS REF CURSOR;
--
TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);
--
TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);
--
PROCEDURE PROC(pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp);
This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
PROCEDURE PROC (pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp)
QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS
SQLPLUS Script Needs To Exit On Completion
Dec 9, 2009I have a series of sql files that I need to execute via sqlplus. What I do need is to immediately exit the sqlplus instance upon completion of the script. Now, I am not using a batch file, nor a file containing script files where I can add the EXIT command.
I have to effectively run each script from the command line as such:
sqlplus user/pass@server @c: empsomedirsomeScript.sql
Now this works, but once it runs the SQLPlus window is still open. Now I have an event handler (in a c# wrapper) that is supposed to fire once the process terminates...but of course, this even never fires since the sqlplus process doesn't terminate after running the script.
Is there any any any any way to get sqlplus to run this script and then self-exit??
I have no control of the sql script and cannot edit them to include an EXIT command. I need to run them as is.
Client Tools :: Connect With Sqlplus Or Toad - Unable To Initialize Oracle Call Interface
Sep 6, 2012when 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.
i am using oracle 11.2.0.1 on windows 7