Passing Oracle function as file input to sqlplus


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing Oracle function as file input to sqlplus
# 8  
Old 02-18-2017
Quote:
Originally Posted by RudiC
...
But, why the pipe? And, can't you supply an exit in the sql-script?
Yes, I missed that one. Adding an exit at the end of the SQL script does exit the subshell.

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

Code:
( <cmd1> ; <cmd2> ; <cmd3> ; ... ) | sqlplus user/pass@db @sql_script

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:
Code:
$ 
$ # 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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

10. 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
Login or Register to Ask a Question