Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Passing Oracle function as file input to sqlplus
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

LEARN ABOUT OSF1

uudemon.cleanu

uudemon(4)						     Kernel Interfaces Manual							uudemon(4)

NAME

       uudemon.admin,  uudemon.cleanu,	uudemon.hour,  uudemon.poll  -	Administrative shell scripts for polling remote systems, cleaning up spool
       directories, reporting status to the system administrator, and routine invocations of the uuxqt and uusched daemons

SYNOPSIS

       These shell scripts reside in the following directory:
	  /usr/lib/uucp

DESCRIPTION

       All the scripts can be run from the command line or can be run automatically by the cron daemon. To automatically run the  scripts,  remove
       the comment character (#) from the beginning of the relevant line in the /var/spool/cron/crontabs/uucp file.

       This  script  reports  status  to  the system administrator. It issues the uustat command to find out the status of uucp jobs. It mails the
       results to the uucp login ID. The script may be modified to send mail to any login ID such as the uucp administrative login ID  (uucpa)	or
       root.  This script cleans up the /var/spool/uucp and /var/spool/uucppublic directories by running the uucleanup command. The uucleanup com-
       mand is run with the following parameters: -C7, -D7, X2, -o2, -W1.  This script runs the uusched and uuxqt daemons in the background.  This
       script  polls  the  systems  listed  in	the  /usr/lib/uucp/Poll file.  The uudemon.poll script should be scheduled before the uudemon.hour
       script. This allows uudemon.poll to create any command files before cron runs the uudemon.hour script.

FILES

       Contains the uudemon.admin, uudemon.cleanu, uudemon.hour and uudemon.poll files.  Contains the uucp file.

RELATED INFORMATION

       Commands: cron(8), uucleanup(8), uusched(8), uuxqt(1)

       Files: /usr/lib/uucp delim off

																	uudemon(4)
All times are GMT -4. The time now is 11:10 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy