OraFAQ Forum: Unix » SQL Script Exit Condition

Home » Infrastructure » Unix » SQL Script Exit Condition (Oracle 10g on Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Script Exit Condition [message #378415]

Tue, 30 December 2008 05:47

newtooracle123
Messages: 24
Registered: May 2007

Junior Member

Hello,

In the below mention code snippet, I intend to return email id for a user back to the calling KSH file.

SET LINESIZE 500
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
SET TRIMSPOOL on
SET HEAD ON
SET PAGESIZE 0
SET TERMOUT ON
SET VERIFY OFF

VARIABLE return_val VARCHAR2(100);

BEGIN
SELECT email_id
INTO :return_val
FROM table_name
WHERE name= '&1' ;

EXCEPTION
WHEN OTHERS
THEN
:return_val := 'ERR';
END;
/

EXIT :return_val;

I am getting below mentioned error in doing so.

Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]

Can anyone please guide me on whats wrong with the above implementation? Is it not possible to send a VARCHAR2 back to calling KSH?

Report message to a moderator

Re: SQL Script Exit Condition [message #378418 is a reply to message #378415]

Tue, 30 December 2008 06:20

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search in Unix forum there are many examples of this.

Regards
Michel

Report message to a moderator

Re: SQL Script Exit Condition [message #378420 is a reply to message #378418]

Tue, 30 December 2008 06:31

newtooracle123
Messages: 24
Registered: May 2007

Junior Member

Correct me if I am wrong, but I am getting the error in SQL prompt and not UNIX.

I get the below mentioned error
"SP2-0670: Internal number conversion failed"

So my question is, Can we return a VARCHAR back to unix with the EXIT <:bindvariable> command

[Updated on: Tue, 30 December 2008 06:35]

Report message to a moderator

Re: SQL Script Exit Condition [message #378421 is a reply to message #378420]

Tue, 30 December 2008 06:40

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

EXIT.

Variable should be NUMBER, not CHARACTER:

SQL> var return_val number;
SQL> begin
  2    select empno into :return_val
  3    from emp
  4    where ename = 'KING';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print return_val;

RETURN_VAL
----------
      7839

SQL> exit :return_val
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

M:\>

Report message to a moderator