Returning Value to UNIX Command Line

[karmst]

Hi,

I am calling SQLPLUS from the UNIX command line with the following command:

SQLPLUS odsload/odsload@odsd.com @value.sql


The value.sql file is the following:

DECLARE
RetVal NUMBER;
BEGIN
RetVal := ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 );
END;


Now, my question is how do I return the value RetVal to the UNIX command line?

Thanks in advance, Keith

[gsprince]

Try this:

DECLARE
RetVal NUMBER;
BEGIN
RetVal := ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 );
END;
/ -- Added
exit :RetVal; -- Added

And, test the value of "$?"

[gtm]

there are two ways to do this

1.use utl_file and write it to a file and then extract the value
from that file to a unix variable.

2. Write a pro*c program.


regards

gtm

[marist89]

The magic of ksh. You can just embed you sqlplus command inside some forward quotes and bingo, your variable is set.

#!/bin/ksh
THE_VALUE=`sqlplus -s system/manager <<EOF
set feedback off
set echo off
set pages 0
DECLARE
RetVal NUMBER;
BEGIN
dbms_output.put_line(ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 ));
END;
EOF
`
echo $THE_VALUE


# end of ksh

This way, you can check the value of $? to see of the sqlplus command was successful and also get your value back.

[karmst]

Jeff,

THis might be a stupid question, but I can get the value for $?, which tells me whether the SQLPLUS command was OK.

Where do i find the value that was sent to DBMS_OUPUT by using $...i.e. the result of ODSLOAD.SP blah blah blah?

Thanks, Keith

[marist89]

Its in $THE_VALUE