How to capture the result of stored procedure through shell script?

I'm trying to execute stored procedure through shell script and try to get return from stored procedure but I didn't get any thing from the stored procedure on other hand same thing I do with sqlplus prompt and I'm able to get the result

sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare

DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);

end;

EOF

Through sqlplus prompt

SQL> set serveroutput on
declare

DE_REC_COUNT number;
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);

end;  

0

PL/SQL procedure successfully completed.

Answers


The version of the anonymous block in the shell script will not be executed as shown, because you don't have a slash after the block to run it. If you run that you get no output at all. If you change it to have a slash:

sqlplus -silent xxx@xxx <<EOF
set serveroutput on
declare
  DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/
EOF

then you'll see:

0

PL/SQL procedure successfully completed.

You've shown the interactive version in SQL*Plus without the slash too, but you must have had that to see the output you showed.

If you want the zero - which seems to be coming from a dbms_output call in your procedure, rather than directly from your anonymous block - n a shell variable you can refer to later, you can assign the output of the heredoc to a variable:

MY_VAR=`sqlplus -silent xxx@xxx <<EOF
set serveroutput on
set feedback off
declare

DE_REC_COUNT number(10);
begin
    DE_DUP_PROC ('T_MCL_30404_20150317_020','MCL','30404','FT',DE_REC_COUNT);
end;
/

EOF`

printf "Got back MY_VAR as %s\n" ${MY_VAR}

Note that I've added set feedback off so you don't see the PL/SQL procedure successfully completed line. Now when you run that you'll see:

Got back MY_VAR as 0

and you can do whatever you need to with ${MY_VAR}. It depends what you mean by 'capture' though.


Need Your Help

Ember Data cannot read property 'async' of undefined

javascript dictionary ember.js ember-data

Using Ember v1.8 beta 3+ with Ember Data 1.0 beta 10- you receive this error:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.