Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Passing Oracle function as file input to sqlplus

Shell Programming and Scripting


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 02-16-2017
amvip amvip is offline
Registered User
 
Join Date: Jan 2017
Last Activity: 22 May 2017, 4:30 PM EDT
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
Passing Oracle function as file input to sqlplus

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
Code:
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::

Code:
# 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


Code:
#!/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:

Code:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:



Moderator's Comments:
Passing Oracle function as file input to sqlplus Please use CODE tags as required by forum rules!

Last edited by amvip; 02-16-2017 at 04:28 PM.. Reason: Added CODE tags.
Sponsored Links
    #2  
Old Unix and Linux 02-17-2017
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 24 May 2017, 12:16 AM EDT
Posts: 2,013
Thanks: 19
Thanked 338 Times in 304 Posts
A couple of points first:

Quote:
Originally Posted by amvip View Post
...

Code:
create table fn_test
(a number, 
b number, 
c number
);

...
---- changes in Unix::

Code:
# 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

...
...
- 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:
Quote:
Originally Posted by amvip View Post
...
--- Create the main script


Code:
#!/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:

Code:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

...
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:

Code:
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:

Code:
$ 
$ 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.
The Following 2 Users Say Thank You to durden_tyler For This Useful Post:
amvip (02-17-2017), jim mcnamara (02-17-2017)
Sponsored Links
    #3  
Old Unix and Linux 02-17-2017
amvip amvip is offline
Registered User
 
Join Date: Jan 2017
Last Activity: 22 May 2017, 4:30 PM EDT
Posts: 11
Thanks: 4
Thanked 0 Times in 0 Posts
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.
    #4  
Old Unix and Linux 02-17-2017
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 24 May 2017, 12:16 AM EDT
Posts: 2,013
Thanks: 19
Thanked 338 Times in 304 Posts
Quote:
Originally Posted by amvip View Post
...
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.
...
...
Of course you can! Linux

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.


Code:
$
$ 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
$
$

You can also do it without that here-document.


Code:
$
$ cat -n main_script_1.sh
     1  #!/usr/bin/bash
     2  exit | sqlplus -s user/password@database @insert_and_fetch.sql
     3
$
$
$ . main_script_1.sh
      5555       4646     353454
      5555       4646     353454
         1          2          3
         3          4          5
$
$

"exit" is a Bash shell built-in. If you don't have it in your shell, then the following should work as well.


Code:
$
$ cat -n main_script_2.sh
     1  #!/usr/bin/bash
     2  echo exit | sqlplus -s user/password@database @insert_and_fetch.sql
     3
$
$
$ . main_script_2.sh
      5555       4646     353454
      5555       4646     353454
      5555       4646     353454
         1          2          3
         3          4          5
$
$

Sponsored Links
    #5  
Old Unix and Linux 02-17-2017
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 May 2017, 4:14 PM EDT
Location: Aachen, Germany
Posts: 10,816
Thanks: 272
Thanked 3,323 Times in 3,060 Posts
Help me out, please: What does and how works that construct in your main_script_1.sh :

Code:
  exit | sqlplus ...

?
Sponsored Links
    #6  
Old Unix and Linux 02-17-2017
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 24 May 2017, 12:16 AM EDT
Posts: 2,013
Thanks: 19
Thanked 338 Times in 304 Posts
In the shell script "main_script_1.sh", if you invoke this command:


Code:
 sqlplus -s user/password@database @insert_and_fetch.sql

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.
The Following User Says Thank You to durden_tyler For This Useful Post:
RudiC (02-17-2017)
Sponsored Links
    #7  
Old Unix and Linux 02-17-2017
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 24 May 2017, 4:14 PM EDT
Location: Aachen, Germany
Posts: 10,816
Thanks: 272
Thanked 3,323 Times in 3,060 Posts
Thanks.
But, why the pipe? And, can't you supply an exit in the sql-script?
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Passing variable from file to Oracle jhonnyrip Shell Programming and Scripting 3 11-21-2014 12:54 PM
Passing Input To Function Ariean UNIX for Dummies Questions & Answers 2 05-23-2013 11:48 AM
Passing variables to an input file danish0909 Shell Programming and Scripting 2 02-20-2013 06:43 AM
redirecting oracle sqlplus select query into file bongo Shell Programming and Scripting 6 05-18-2010 09:21 AM
passing unix variable to sqlplus without a file name sakthi.abdullah UNIX for Advanced & Expert Users 3 12-12-2006 05:35 AM



All times are GMT -4. The time now is 01:01 AM.