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
] 
Blog
Search
Help
Members
Register
Login
Home
