Client Tools :: How To Exit Out Of SQLPLUS

Dec 4, 2011

I 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]...

View 3 Replies


ADVERTISEMENT

Client Tools :: SQLPLUS Is Not Coming Out With Exit Code If Not Connected

Sep 6, 2011

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

Output=`$ORACLE_HOME/bin/sqlplus -S /nolog << EOF
CONNECT $Usr/$Pwd@$Server
WHENEVER SQLERROR EXIT 1
spool ${ROOT_DIR}/.delta_date.tmp

[code]....

View 5 Replies View Related

Client Tools :: How To Add Value From Sqlplus

May 14, 2013

I 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.

View 6 Replies View Related

Client Tools :: SQLPLUS - Then / Else Run Script

Nov 7, 2012

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.

View 1 Replies View Related

Client Tools :: SqlPlus Version - How To Get Bit Information

Sep 26, 2013

Oracle 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.

View 26 Replies View Related

Client Tools :: Delimited Output From Sqlplus

Mar 3, 2010

I 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.

View 10 Replies View Related

Client Tools :: Sysdba Access From Sqlplus?

Feb 8, 2009

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.
-----------------------------------------------

what may be the reason for this error?

View 8 Replies View Related

Client Tools :: SQLPLUS Windows In Oracle 11g

Feb 27, 2011

the reason for not availability of ISQLPLUS & SQLPLUSW window from oracle 11g.

View 3 Replies View Related

Client Tools :: How To Substitute Value From A Query In Sqlplus

Apr 2, 2012

I 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;

View 2 Replies View Related

Client Tools :: Display Option In Sqlplus

Apr 12, 2010

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.

View 3 Replies View Related

Client Tools :: How To Define ED Command In Sqlplus

Jul 25, 2012

In 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.

View 2 Replies View Related

Client Tools :: How To Find Username In Sqlplus

Aug 16, 2011

How to find user name in sqlplus?

View 5 Replies View Related

Client Tools :: SQLPlus Command Parser

Jul 31, 2013

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.

View 5 Replies View Related

Client Tools :: SQLPlus Not Starting From Command Prompt

Jan 21, 2011

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 ?

View 11 Replies View Related

Client Tools :: How To Handle Errors In Sql Files By SQLPlus

Jul 31, 2013

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.)

how to handle these cases by a SQLPlus?

View 8 Replies View Related

Client Tools :: Windows 7 / Oracle 10g - Not Getting Editor From Sqlplus

Jan 29, 2012

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 ?

View 1 Replies View Related

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:

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

View 15 Replies View Related

Client Tools :: SQLPLUS For Oracle 11 Not Rolling Back

Sep 3, 2013

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.

View 7 Replies View Related

Client Tools :: Can Login As SYS As SYSDBA Via TOAD But Not Via SQLPlus

Jun 21, 2013

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

View 2 Replies View Related

Client Tools :: Conditionals In A SQLPlus Batch File?

Oct 23, 2012

From 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.

View 12 Replies View Related

Client Tools :: Run A Query In Background In Toad Or Sqlplus

Feb 3, 2011

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?

View 11 Replies View Related

Client Tools :: Changing DB Password Using SQLPlus Command Line

Jul 21, 2010

I'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

View 4 Replies View Related

Client Tools :: Handling DATETIME Format Using SQLPLUS COPY Command

Jul 29, 2012

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.

View 12 Replies View Related

Client Tools :: Sqlplus - Multiple Scripts In Argument (batch Execution)

Aug 13, 2008

I 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.

View 22 Replies View Related

Client Tools :: Display Clob Column In Text File In Sqlplus

Mar 30, 2012

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

[Code]....

View 2 Replies View Related

Client Tools :: Sqlplus Error ORA-12154 - TNS - Resolve Connect Identifier Specified?

Feb 24, 2010

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

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]...

View 6 Replies View Related

Client Tools :: Create Shortcut On Desktop To Have SQLPLUS (command Line)

Sep 6, 2010

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)

View 7 Replies View Related

Client Tools :: SQLPLUS - How To Execute Store Proc When Passing Only 2 Out Of 3 Parameters

Feb 22, 2011

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

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

View 6 Replies View Related

SQLPLUS Script Needs To Exit On Completion

Dec 9, 2009

I 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.

View 5 Replies View Related

Client Tools :: Connect With Sqlplus Or Toad - Unable To Initialize Oracle Call Interface

Sep 6, 2012

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.

i am using oracle 11.2.0.1 on windows 7

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved