How to get OUT parameter of a stored procedure in shell script?
I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible?
I have got the below error when i followed the above method
ERROR at line 2: ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "." to continue.
---------- Post updated at 10:45 AM ---------- Previous update was at 10:37 AM ----------
I am able to execute the procedure after removing
dbms_output.enable(1000000);
but i didn't get the OUT parameter in the shell variable output. Please help.
---------- Post updated at 11:05 AM ---------- Previous update was at 10:45 AM ----------
I am almost there with the below shell & sql script. Its returns a NUMBER value, but i can't able to use this a variable in shell. Please see the output and suggest a solution
Shell
SQL Script
O/P
20005
temp.sh: line 8: syntax error: unexpected end of file
Try this at the sql prompt:
This has been a standard package in Oracle since v8. If you not see it, either the DBA set it up incorrectly, or you do not have it for some reason.
If desc sys.dbms_output works, add the owner, sys., to the front of dbms_output everywhere in the code I gave you. It does work.
Let us know. The next step is either to get DBA cooperation or create a ridiculous hack.
And the hack assumes your oracle userid has create table or owns a table that is meant as temp storage.
Hello all,
I am trying to run stored procrdure from shell script which takes one argument. And also I want to verify in the script whether the script executed successfully. However the Stored procedure is not running from shell script. Manually if I run it update the data in the table.
Can... (29 Replies)
I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Hi Dudes :)
I want a unix shell script to pass value to SQL stored procedure.
Below is the procedure
declare
res varchar2(10);
begin
odm_load_check('PRE_SANITY',res);
dbms_output.put_line(res);
end;
select * from error_log;
truncate table error_log;
select * from test; (1 Reply)
hi
i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh).
the called stored procedure returns some values through OUT variables
i want to assign the return values of stored procedure in to unix shell script variable.
can you provide me the code.
... (1 Reply)
Here's a shell script snippet.....
cd $ORACLE_HOME/bin
Retval=`sqlplus -s <<eof
$TPDB_USER/april@$TPD_DBCONN
whenever SQLERROR exit 2 rollback
whenever OSERROR exit 3 rollback
set serveroutput on
set pages 999
var status_desc char(200)
var status_code... (1 Reply)
hi all
please tell me how to pass parameters 4m shell script to a DataBase stored procedure.
To be specific i have sybase DB.
i mean i want the syntax of the command..
how to connect to DB, pass user id and password, pass the required parameter to SP..
..
need ur help frnds..
hema (0 Replies)
hai,
can anybody say how to call or to execute an oracle stored procedure in oracle from unix...
thanks in advance.... for ur reply....
by,
leo (2 Replies)
HI,
I have a similar problem to thread 18264, only I couldn't get it to work.
https://www.unix.com/showthread.php?t=18264
I have a stored procedure which is called by a shell script program. When I run the stored procedure alone or through the shell script, it works fine with output text... (3 Replies)
Hi All,
This is a very starnge problem I am having. I have a shell script that calls a stored procedure.
Here's my code in shell script:
sqlplus "userid/pwd" @file.sql
and file.sql has the following statement:
exec my_storedProc;
Now, when I execute my shell script, nothing... (2 Replies)