CODEWITHSUNDEEP
oracle-databaseshellvariablesplsql
Landon Statis
Landon Statis

Reputation: 839

Return Value from PL/SQL

So, we have a shell script, which needs to call an anonymous block, and receive a value back. The anonymous block is about 100 lines, so we have it in a file, hence for SQL*Plus it is called:

@/directory_path/anonymous_block "'param1'" "'param2'"

Yes, we pass a couple of parameters. And at the end of this, we have a DBMS_OUTPUT.PUT_LINE.......

So, the question we have, is how to get that back to Shell? We've tried various types of code similar to this:

proc=TEMPLATE1_MV<BR>
code=513

x=`print "
  set heading off feedback off verify off timing off
  @/u01/sql/stock_rpt_stats '$code' '$proc';
  exit" | sqlplus -s $user/$pass`

y=`echo $x | awk '{print $1}'`<BR>
printf "HERE: $y\n"

But no luck, just errors.

If this contains just a straight SQL: 

SELECT TRUNC(SYSDATE) FROM dual;

That will work fine, but not referencing a file containing the SQL.

Anyone with some ideas would be great. Thanks in advance.

Upvotes: 0

Views: 645

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Did you try adding SET SERVEROUTPUT ON ?

I generally prefer to run sqlplus using the heredoc method and passing the result of the block to a variable like this and it works fine for me.

scripts-$cat ./sqlplus_exec.sh
#!/bin/bash
output=$(sqlplus -s  $user/$pass <<INP
set heading off feedback off verify off timing off
set serveroutput on
@print_output.sql
INP
)

echo "$output"

Here's the SQL file

scripts-$cat print_output.sql
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;
/

scripts-$./sqlplus_exec.sh
HELLO WORLD

Upvotes: 1

Related Questions