Sponsored Content
Top Forums Shell Programming and Scripting Passing Oracle function as file input to sqlplus Post 302991884 by durden_tyler on Friday 17th of February 2017 12:19:04 PM
Old 02-17-2017
Quote:
Originally Posted by amvip
...
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! Smilie

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

 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

2. Shell Programming and Scripting

Passing global variable to a function which is called by another function

Hi , I have three funcions f1, f2 and f3 . f1 calls f2 and f2 calls f3 . I have a global variable "period" which i want to pass to f3 . Can i pass the variable directly in the definition of f3 ? Pls help . sars (4 Replies)
Discussion started by: sars
4 Replies

3. UNIX for Dummies Questions & Answers

Reading from a file(passing the file as input parameter)

hi I have a shell script say primary.sh . There is a file called params my scenario is primary.sh should read all the values and echo it for example i should pass like $primary.sh params output would be Abc ... (2 Replies)
Discussion started by: ssuresh1999
2 Replies

4. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./script.sh sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

5. Shell Programming and Scripting

Passing variable as an input file to AWK comand

Hi, I would like to compare 2 files using awk, which I can do by using: awk 'NR==FNR{a;next} (NR > 32 && $2 in a) {print $0}' File1 and File2. If the name of the File1 is in another file (for example, column 4 in File 3) then how can I pass this column 4 to the awk command. Thanks in... (1 Reply)
Discussion started by: ezhil01
1 Replies

6. Shell Programming and Scripting

Passing variables to an input file

Hi All, I have to insert 2 values to a text file in specific places. I have been able to extract each variable value via a script but am not able to send these variable values to the text file. Pasted is the script for extracting the variable values: for i in `ls -1` ... (2 Replies)
Discussion started by: danish0909
2 Replies

7. UNIX for Dummies Questions & Answers

Passing Input To Function

May i please know why is it printing the script name for $0 when i pass those parameters to function. #!/bin/bash -x usage() { echo "In Usage Function" echo $0 echo $1 echo $2 } echo "printing first time" echo $0 echo $1 echo $2 usage $0 $1 $2 Output: (2 Replies)
Discussion started by: Ariean
2 Replies

8. Shell Programming and Scripting

Passing variable from file to Oracle

cat a1 scott robert tom test script : #!/usr/bin/ksh for NAME in `cat a1` do VALUE=`sqlplus -silent "nobody/bobody01@testq" <<END set pagesize 0 feedback off verify off heading off echo off select username from dba_users where username=upper('$NAME'); END` if ; then echo... (3 Replies)
Discussion started by: jhonnyrip
3 Replies

9. Shell Programming and Scripting

Coredump when passing file name to ksh function

Hi, I'm playing with ksh. I'm trying to do a simple task: read file name from cli and call a function which calculated number of lines in file. I'm getting coredump every time when I try to read that file. Korn shell version $ print ${.sh.version} Version AJM 93u+ 2012-08-01 Main... (5 Replies)
Discussion started by: solaris_user
5 Replies

10. Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Hi, I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop. The first SQL file is basically a connection test and it is supposed to sort... (2 Replies)
Discussion started by: newbie_01
2 Replies
xlator_call(8)						      System Manager's Manual						    xlator_call(8)

Name
       xlator_call - shell script to invoke PostScript translators

Syntax
       xlator_call datatype orientation pagesize width length indent

Description
       This  Bourne  shell script can be called by the line printer daemon to invoke the appropriate translator to convert each data type to Post-
       Script.	The data type passed by may be specified by using the command with the -Ddatatype option, or by using the  Da=datatype	capability
       in the file.

       For a file in a data syntax to be translated to PostScript, the script must contain a case branch which recognizes the data type string and
       calls a suitable filter.  The supplied recognizes the ANSI, ASCII, ReGIS and Tektronix 4014 data types.

Arguments
       datatype
       The valid data types are: or any other for which a translator has been installed.

       orientation
       The valid orientations are: portrait or landscape.

       pagesize
       The valid page sizes are: or

       width
       The width (in characters) of the page.

       length
       The length (in lines) of the page.

       indent
       The amount (in spaces) the output is to be indented.

       All the valid arguments are described in detail by the reference page.

Examples
       An example shell script is shown below:
       case $datatype in
       ansi)
	    exec ansi_ps -F $pagesize -O $orientation -e "$@";;
       ascii)
	    echo "(04) cvn {} def"
	    exec ln03rof -w$width -l$length -i$indent;;
       postscript)
	    exec cat;;
       tek4014)
	    exec tek4014_ps -F $pagesize -O $orientation;;
       regis)
	    exec regis_ps -F $pagesize -O $orientation;;
       *)
	    echo "$0: Translator for data type $datatype not installed" >&2
       esac

       When the shell script is called, the path searched is:
       /usr/local/lib/lpdfilters:/usr/ucb:/bin:/usr/bin:
				   /usr/lib:/usr/lib/lpdfilters

Files
       The				       script

See Also
       lno3rof(8), printcap(5), ansi_ps(8), lpd(8)

																    xlator_call(8)
All times are GMT -4. The time now is 07:53 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy