| Function call in Unix to assign value to a refcursor variable [message #660454] |
Thu, 16 February 2017 14:21  |
 |
abhi_orcl
Messages: 40
Registered: December 2016
|
Member |
|
|
Apologies if this is the incorrect forum.There is an issue in the function call I am facing while calling the same from a unix shell scripts.
Basically, I want the ref cursor to return values to a variable in sqlpus. The function call is currently saved in a ".txt" file
in a unix location. I want to read the function name from the file and then execute it.But when I am running the below I get error.
Can someone please advise how to achieve the desired results?
--- create tables
create table fn_test
(a number,
b number,
c number
);
insert into fn_test values(1,2,3)
insert into fn_test values(3,4,5);
commit;
-- create function
create or replace function test_fn
return sys_refcursor
as
p_ref_out sys_refcursor;
begin
open p_ref_out
for select *
from fn_test;
return p_ref_out;
end;
---- changes in Unix::
# create the input file
--- Create the main script
#!/bin/ksh
sqlplus -s user/pass@server << ! < t_fn.txt
set head off
set echo off
set define off
set feedback off
set serveroutput on
var l_refcursor refcursor
exec :l_refcursor := @t_fn.txt
print l_refcursor
!
I get Error:
[b]SP2-0734: unknown command beginning "test_f..."[/b] [Updated on: Thu, 16 February 2017 14:23] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Function call in Unix to assign value to a refcursor variable [message #660459 is a reply to message #660458] |
Thu, 16 February 2017 15:13  |
|
abhi_orcl
Messages: 40
Registered: December 2016
|
Member |
|
|
It seems my celebration was a bit early. There is one more scenario that came stating the file t_fn.txt can have more commands a part from the just the function call. For eg, it can have multiple DMLs and then the call to the function and cat t_fn.txt might show:
insert into test_a values(5555,4646,353454);
commit;
test_fn
In this scenario, the above script fails because it encounters insert instead of a cursor. Any advise on this?
[Updated on: Thu, 16 February 2017 15:13] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Function call in Unix to assign value to a refcursor variable [message #660467 is a reply to message #660465] |
Fri, 17 February 2017 10:45 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Just:
#!/bin/ksh
echo 'set head off
set echo off
set define off
set feedback off
set serveroutput on
var l_refcursor refcursor
@t_fn.txt
print l_refcursor
exit' >script.sql
sqlplus -s user/pass@server @script
insert into test_a values(5555,4646,353454);
commit;
exec :l_refcursor := test_fn
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
