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 "insert into test_a values(5555,4646,353454);" > t_fn.txt
echo "commit;" >> t_fn.txt
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:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
The table names in the database and the script "t_fn.txt" are not the same. ("fn_test" and "test_a")
"test_f" in the script "t_fn.txt" is only going to confuse sqlplus. What is it?
Regarding the main script:
Do you want to:
(a) execute "t_fn.txt" thereby inserting 1 record into table "fn_test" and then
(b) invoke function "test_fn" so that it fetches all 3 records (2 existing + 1 new)?
If you redirect the t_fn.txt script ("< t_fn.txt") to sqlplus, it will only run the script and disregard the commands inside the here-doc. You will have to execute the script within the here-doc.
Secondly, "l_refcursor" can be only be assigned the value of a compiled object. In this case, it is the function "test_fn". You cannot execute an external SQL script (anonymous PL/SQL block, "t_fn.txt" in this case) and assign it to "l_refcursor".
Considering all the points above, the following works in my system.
In Oracle:
SQL>
SQL> create table fn_test (a number, b number, c number);
Table created.
SQL> insert into fn_test values(1,2,3);
1 row created.
SQL> insert into fn_test values(3,4,5);
1 row created.
SQL> commit;
Commit complete.
SQL> -- create function
SQL> create or replace function test_fn
2 return sys_refcursor
3 as
4 p_ref_out sys_refcursor;
5 begin
6 open p_ref_out for select * from fn_test;
7 return p_ref_out;
8 end;
9 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> --
SQL> select * from fn_test;
A B C
---------- ---------- ----------
1 2 3
3 4 5
2 rows selected.
SQL>
SQL>
In Linux:
$
$ cat -n t_fn.txt
1 insert into fn_test values(5555,4646,353454);
2 commit;
$
$
$ cat -n main_script.ksh
1 #!/bin/ksh
2 sqlplus -s user/password@database << !
3 set head off
4 set echo off
5 set define off
6 set feedback off
7 set serveroutput on
8 set timing off
9 set pages 0
10 @t_fn.txt
11 var l_refcursor refcursor
12 exec :l_refcursor := test_fn
13 print l_refcursor
14 !
15
$
$ . main_script.ksh
5555 4646 353454
1 2 3
3 4 5
$
$
The sqlplus variable "timing" is set to off by default, but in my setup it is set to on in the sqlplus profile script. So I set it off in the testcase above.
I set the variable "pages" to 0 in order to remove the extra blank line at the top where the header would have been.
I actually do not encounter the "PLS-00103" error. Try the above and see if it works in your system. Otherwise, more investigation will be required.
Thanks for the advise. Yeahh..It seems I really cant run both the inserts and the function in the same file and expect my script to catch the ref cursor returned by the function. Actually I was trying to avoid the splitting of the single file and still get the results from the function.
In the following, the database part remains as it was earlier.
Sorry I don't have ksh at the moment. The shell script should not require any changes though.
$
$ cat -n insert_and_fetch.sql
1 set head off
2 set echo off
3 set define off
4 set feedback off
5 set serveroutput on
6 set timing off
7 set pages 0
8 insert into fn_test values(5555,4646,353454);
9 commit;
10 var l_refcursor refcursor
11 exec :l_refcursor := test_fn
12 print l_refcursor
13
$
$ cat -n main_script.sh
1 #!/usr/bin/bash
2 sqlplus -s user/password@database << !
3 @insert_and_fetch.sql
4 !
5
$
$ . main_script.sh
5555 4646 353454
1 2 3
3 4 5
$
$
then it opens a child shell, runs the "sqlplus" program in it, passing the SQL script "insert_and_fetch.sql" to it.
Thereafter, the control goes to the "SQL> " prompt of "sqlplus" until you manually type in an "exit" command.
The "exit <n>" builtin command causes the child shell to exit with status <n>. If <n> is not supplied, the exit status is that of the last command executed, which would be "sqlplus" in this case.
Yes, I missed that one. Adding an exit at the end of the SQL script does exit the subshell.
$
$ cat -n test_script.sql
1 set pages 0 feed off time off timing off
2 select 'This is passed from the SQL script to the database.' as x from dual;
3 exit
$
$ cat -n db_shell_script.sh
1 #!/usr/bin/bash
2 sqlplus -s user/password@db @test_script.sql
$
$ . db_shell_script.sh
This is passed from the SQL script to the database.
$
$
And I think it's a cleaner way of doing things as long as the SQL script is self-contained.
If additional data (variables, additional commands etc.) are to be passed from the parent shell however, then the pipe would be used. The following:
is just an idiom that's been floating around for a while. Here, "<cmd1>" etc. are proper SQL statements or sqlplus commands that would run in the database, which the shell won't understand, so they need to be wrapped with an "echo".
My understanding is that sqlplus runs the "sql_script" first and then accepts and runs everything passed from the command chain.
Testing again:
$
$ # No exit in the SQL script
$ cat -n test_script.sql
1 set pages 0 feed off time off timing off
2 select 'This is passed from the SQL script to the database.' as x from dual;
$
$ # The following should be run by sqlplus after the SQL script
$ ( echo "select 'After SQL script' from dual;" ; echo "exit" )
select 'After SQL script' from dual;
exit
$
$ # Test
$ ( echo "select 'After SQL script' from dual;" ; echo "exit" ) | sqlplus -s user/password@db @test_script.sql
This is passed from the SQL script to the database.
After SQL script
$
$
The "exit" above is a sqlplus command, not the shell builtin. sqlplus runs it, exits as a consequence and then the subshell exits as well.
The "exit" builtin closes the subshell itself and the sqlplus gets closed as well.
