Home » Infrastructure » Unix » SQL Script Exit Condition (Oracle 10g on Unix)
SQL Script Exit Condition [message #378415] Tue, 30 December 2008 05:47 Go to next message
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?


Re: SQL Script Exit Condition [message #378418 is a reply to message #378415] Tue, 30 December 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search in Unix forum there are many examples of this.

Regards
Michel
Re: SQL Script Exit Condition [message #378420 is a reply to message #378418] Tue, 30 December 2008 06:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 19651
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:\>
Re: SQL Script Exit Condition [message #378425 is a reply to message #378421] Tue, 30 December 2008 06:51 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
My requirement was to send characters. Sad

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

Report message to a moderator

Re: SQL Script Exit Condition [message #378441 is a reply to message #378425] Tue, 30 December 2008 07:57 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 30 December 2008 13:20
Search in Unix forum there are many examples of this.

Regards
Michel


Previous Topic: o/s ver: HP-UX .ver:11.23
Next Topic: asm
Goto Forum:
  


Current Time: Thu Oct 02 02:25:12 CDT 2014

Total time taken to generate the page: 0.15211 seconds