| Assign output value from package to bourne shell variable [message #35740] |
Thu, 11 October 2001 04:42  |
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  |
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 #35762 is a reply to message #35740] |
Fri, 12 October 2001 10:58 |
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`
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
