SQLPLUS output to shell script not returning value

Question

I'm trying to return a sqlplus output to a shell script. This may sound simple enough but I've searched online for some time and cannot get my script to work.

Here is my pl/sql script:

SET SERVEROUTPUT ON

DECLARE 
X_RETURN_MSG VARCHAR2(32767);
X_RETURN_CODE NUMBER;

BEGIN 
X_RETURN_MSG := NULL;
X_RETURN_CODE := 5;

COMMIT;
END;

EXIT X_RETURN_CODE;

Here is my shell script:

sqlplus -s user/pwd <<EOF
@../sql/tester.sql
EOF
RETVAL=$?
echo $RETVAL

$RETVAL always returns 0 even when I have X_RETURN_CODE := 5

Dave Costa · Accepted Answer · 2011-10-10 18:38:35Z

6

X_RETURN_CODE has no meaning outside of the scope of the PL/SQL block where it is declared. You need to use a SQLPlus bind variable.

SQL> VARIABLE return_code NUMBER
SQL> BEGIN
  2    :return_code := 5;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> EXIT :return_code
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
> echo $?
5

answered Oct 10, 2011 at 18:38

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user965422 Over a year ago

Thank you everyone for your comments and suggestions. As always, this forum is one of the best.

Rachel McMahan · Accepted Answer · 2011-10-10 17:20:43Z

0

My guess is END marks the end of the block and X_RETURN_CODE goes out of scope so it defaults to 0. Or maybe you should look into using the RETURN statement instead of EXIT.

edited Oct 10, 2011 at 17:20

answered Oct 10, 2011 at 17:12

Rachel McMahan

3921 silver badge8 bronze badges

2 Comments

user965422 Over a year ago

I think you're correct. How would I get my return code in scope to exit with it's value?

Rachel McMahan Over a year ago

Make it a function or procedure instead of an anonymous block.

Dimitre Radoulov · Accepted Answer · 2011-10-10 18:39:55Z

0

$ sqlplus -s <<!
> / as sysdba
> @tester
> !

PL/SQL procedure successfully completed.

$ echo $?
5

$ cat tester.sql
SET SERVEROUTPUT ON

var X_RETURN_CODE number

DECLARE
X_RETURN_MSG VARCHAR2(32767);

BEGIN
X_RETURN_MSG := NULL;
:X_RETURN_CODE := 5;

COMMIT;
END;
/

EXIT :X_RETURN_CODE;

answered Oct 10, 2011 at 18:39

Dimitre Radoulov

28.1k4 gold badges42 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQLPLUS output to shell script not returning value

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related