Home » Infrastructure » Unix » Function call in Unix to assign value to a refcursor variable
Function call in Unix to assign value to a refcursor variable [message #660454] Thu, 16 February 2017 14:21 Go to next message
abhi_orcl
Messages: 39
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
echo test_f > t_fn.txt

--- 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 #660455 is a reply to message #660454] Thu, 16 February 2017 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try something like:
#!/bin/ksh
echo 'set head off
set echo off
set define off
set feedback off
set serveroutput on
var l_refcursor refcursor
exec :l_refcursor := &1
print l_refcursor
exit' >script.sql
sqlplus -s user/pass@server @script `cat t_fn.txt`
Re: Function call in Unix to assign value to a refcursor variable [message #660456 is a reply to message #660455] Thu, 16 February 2017 14:49 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Thanks Michel, but got the below error:

bash-4.3$ ./sql_fn.ksh
SP2-0552: Bind variable "1" not declared.
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "l_refcursor"
Re: Function call in Unix to assign value to a refcursor variable [message #660457 is a reply to message #660456] Thu, 16 February 2017 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove "define off".
I copied it from your post and it is an error.
As well as "set serveroutput on" if you don't use dbms_output.

Re: Function call in Unix to assign value to a refcursor variable [message #660458 is a reply to message #660457] Thu, 16 February 2017 14:53 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Awesome..works great...thanks a lot Michel
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 Go to previous messageGo to next message
abhi_orcl
Messages: 39
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 #660464 is a reply to message #660459] Fri, 17 February 2017 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just put the "exec" inside the file and call the file as a script.

Re: Function call in Unix to assign value to a refcursor variable [message #660465 is a reply to message #660464] Fri, 17 February 2017 10:16 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
@Michel - Thanks. But still it didn't work. It seems because of the DMLs before the function call, the "exec :l_refcursor := " part in the script.sql doesn't work as it searches for the return value as refcursor and it doesn't get that. It seems I have to separate the files - one file contains the DMLS and do something like:

create a file t_fn.txt with text data as the DMLs.
and then in unix :

sqlplus -s user/password@database << !
set head off
set echo off
set define off
set feedback off
set serveroutput on
set timing off
set pages 0
@t_fn.txt
var l_refcursor refcursor
exec :l_refcursor := test_fn
print l_refcursor
!

This requires a lot of manual effort because I have to segregate the huge lot of file
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 Go to previous message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
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
with t_fn.txt containing:
insert into test_a values(5555,4646,353454);
commit;
exec :l_refcursor := test_fn
Or any variation on this.

Previous Topic: Getting errors when starting listener
Next Topic: How will you remove every 5th Row in a million row file in Unix
Goto Forum:
  


Current Time: Wed Dec 13 05:24:46 CST 2017

Total time taken to generate the page: 0.01455 seconds