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
    #8  
Old Unix and Linux 4 Days Ago
durden_tyler's Unix or Linux Image
durden_tyler durden_tyler is offline Forum Advisor  
Registered User
 
Join Date: Apr 2009
Last Activity: 18 February 2017, 5:57 PM EST
Posts: 1,961
Thanks: 13
Thanked 310 Times in 278 Posts
Quote:
Originally Posted by RudiC View Post
...
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.
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 01:54 PM
Passing Input To Function Ariean UNIX for Dummies Questions & Answers 2 05-23-2013 12:48 PM
Passing variables to an input file danish0909 Shell Programming and Scripting 2 02-20-2013 07:43 AM
redirecting oracle sqlplus select query into file bongo Shell Programming and Scripting 6 05-18-2010 10:21 AM
passing unix variable to sqlplus without a file name sakthi.abdullah UNIX for Advanced & Expert Users 3 12-12-2006 06:35 AM



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