Home » SQL & PL/SQL » SQL & PL/SQL » Assign output value from package to bourne shell variable
Assign output value from package to bourne shell variable [message #35740] Thu, 11 October 2001 04:42 Go to next message
Kitty
Messages: 2
Registered: October 2001
Junior Member
Hi,

After I call a stored procedure in ORACLE, how do I assign the output value from the stored procedure into a bourne shell variable? Syntax pls???

Regards,
Kitty

----------------------------------------------------------------------
Re: Assign output value from package to bourne shell variable [message #35742 is a reply to message #35740] Thu, 11 October 2001 07:23 Go to previous messageGo to next message
jim
Messages: 74
Registered: July 2000
Member
If the shell script is actually calling the procedure and not a second process calling this procedure, then let the shell script call sqlplus. Here is an example:(this should work even from a unix prompt)

RC=`sqlplus -s logon/password << EOF
set heading off
set pause off
select count(*) from atable;
exit
EOF`
echo $RC

For this to work the stored procedure must return the results back to the calling program, either through dbms_output or a function, etc.

If the stored procedure is completely independent to the shell script, one way is to have the procedure write to a file and read the file in the shell script. If this does not answer your question, please provide some additional info.

----------------------------------------------------------------------
Re: Assign output value from package to bourne shell variable [message #35750 is a reply to message #35740] Thu, 11 October 2001 18:40 Go to previous messageGo to next message
Kitty
Messages: 2
Registered: October 2001
Junior Member
Thanks for your reply. Do you have an example of how to create a stored procedure that can write to a file. What is the syntax of reading a file in the bourne shell script?

----------------------------------------------------------------------
Re: Assign output value from package to bourne shell variable [message #35762 is a reply to message #35740] Fri, 12 October 2001 10:58 Go to previous message
jim
Messages: 74
Registered: July 2000
Member
Not knowing how you are calling the procedure; a form, unix shell script, sqlplus, etc?, I would say use the UTL_FILE package. Here is some pseudo_code for UTI_FILE:

create or replace procedure_name
u_output_text varchar2(1023) := 'text';
u_output_filename varchar2(50) := 'output_file';
u_output_dir varchar2(1024) := '/usr/tmp';
u_output_file utl_file.file_type;
begin
u_output_file := utl_file.fopen(u_output_dir, u_output_filename, 'w');
utl_file.put_line(u_output_file,u_output_text);
utl_file.fflush(u_output_file);
utl_file.fclose_all;
end;

For the shell script a 1 line read:
RC=`cat output_file`

----------------------------------------------------------------------
Previous Topic: I can't display data form a variable
Next Topic: ASCII Chars
Goto Forum:
  


Current Time: Sat May 25 16:30:13 CDT 2024