Run a PL SQL Package from a shell script
in [Shell]
|
Prev: Sed Find and Replace Multiple Files
Next: newbie qn: shell program to copy files from one directory on the server to a remote windows machine on the same network
From: nicetom786 on 1 Sep 2006 15:24 Hi i am new to unix shell scripting , I was successful in executing a shell script that displays records of a table. I want to execute a PL SQL package. This is how I tried. in scrip1.ksh But executing the below i get an error "unknown SET option" echo "set serveroutput on\nset timing on\execute Package.Procedure"| sqlplus username/password(a)database The procedure also takes 1 IN parameter.Let me know how shoudl I call this? Thanks in advance. Tom
From: Radoulov, Dimitre on 1 Sep 2006 15:36 > I want to execute a PL SQL package. > This is how I tried. > in scrip1.ksh > But executing the below i get an error "unknown SET option" > echo "set serveroutput on\nset timing on\execute Package.Procedure"| > sqlplus username/password(a)database > > The procedure also takes 1 IN parameter.Let me know how shoudl I call > this? printf "set serverout on timi on\n exec dbms_output.put_line('"'Hello'"');\n" | sqlplus user/pass(a)service_name Regards Dimitre
From: Chris F.A. Johnson on 1 Sep 2006 15:49 On 2006-09-01, nicetom786(a)yahoo.com wrote: > Hi i am new to unix shell scripting > , > I was successful in executing a shell script that displays records of a > table. > I want to execute a PL SQL package. > This is how I tried. > in scrip1.ksh > But executing the below i get an error "unknown SET option" > echo "set serveroutput on\nset timing on\execute Package.Procedure"| > sqlplus username/password(a)database Don't use echo: printf "%s\n" "set serveroutput on" "set timing on" \ "execute Package.Procedure" | sqlplus username/password(a)database -- Chris F.A. Johnson, author <http://cfaj.freeshell.org> Shell Scripting Recipes: A Problem-Solution Approach (2005, Apress) ===== My code in this post, if any, assumes the POSIX locale ===== and is released under the GNU General Public Licence
From: nicetom786 on 1 Sep 2006 16:12 Thanks for responding. 1)May I know is there any specific reason for "ECHO" should not be used and preferred than printf. 2)Moreover I am able to call the package procedure without parameters . But with parameters ,I passed using taking a varianle and storing in for ex col1= $1 col2 = $2... then execute mypackage.myproc($col1,$col2....) I get the following error... ORA-06550: line 1, column 30: PLS-00103: Encountered the symbol "," when expecting one of the following: ( ) - + case mod new not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> table avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month DAY_ hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date <a string literal with character set specification> Pls reply.Thanks in advance Tom Chris F.A. Johnson wrote: > On 2006-09-01, nicetom786(a)yahoo.com wrote: > > Hi i am new to unix shell scripting > > , > > I was successful in executing a shell script that displays records of a > > table. > > I want to execute a PL SQL package. > > This is how I tried. > > in scrip1.ksh > > But executing the below i get an error "unknown SET option" > > echo "set serveroutput on\nset timing on\execute Package.Procedure"| > > sqlplus username/password(a)database > > Don't use echo: > > printf "%s\n" "set serveroutput on" "set timing on" \ > "execute Package.Procedure" | sqlplus username/password(a)database > > -- > Chris F.A. Johnson, author <http://cfaj.freeshell.org> > Shell Scripting Recipes: A Problem-Solution Approach (2005, Apress) > ===== My code in this post, if any, assumes the POSIX locale > ===== and is released under the GNU General Public Licence
From: Chris F.A. Johnson on 1 Sep 2006 16:52
On 2006-09-01, nicetom786(a)yahoo.com wrote: > Chris F.A. Johnson wrote: >> On 2006-09-01, nicetom786(a)yahoo.com wrote: >> > Hi i am new to unix shell scripting >> > , >> > I was successful in executing a shell script that displays records of a >> > table. >> > I want to execute a PL SQL package. >> > This is how I tried. >> > in scrip1.ksh >> > But executing the below i get an error "unknown SET option" >> > echo "set serveroutput on\nset timing on\execute Package.Procedure"| >> > sqlplus username/password(a)database >> >> Don't use echo: >> >> printf "%s\n" "set serveroutput on" "set timing on" \ >> "execute Package.Procedure" | sqlplus username/password(a)database [please don't top post] > Thanks for responding. > 1)May I know is there any specific reason for "ECHO" should not be > used and preferred than printf. This has been explained here many times. You're using Google Groups; the archives will show you. Or read the FAQ: <http://home.comcast.net/~j.p.h/cus-faq.html#0b>. > 2)Moreover I am able to call the package procedure without parameters . > But with parameters ,I passed using taking a varianle and storing in > for ex > col1= $1 > col2 = $2... > then > execute mypackage.myproc($col1,$col2....) > > I get the following error... > ORA-06550: line 1, column 30: > PLS-00103: Encountered the symbol "," when expecting one of the > following: > ( ) - + case mod new not null others <an identifier> ><a double-quoted delimited-identifier> <a bind variable> > table avg count current exists max min prior sql stddev sum > variance execute multiset the both leading trailing forall > merge year month DAY_ hour minute second timezone_hour > timezone_minute timezone_region timezone_abbr time timestamp > interval date ><a string literal with character set specification> I don't know what syntax sqlplus expects. I presume you do, so play around with the printf statement, leaving out the pipe to sqlplus. Once that output is correct, put the pipe back. -- Chris F.A. Johnson, author <http://cfaj.freeshell.org> Shell Scripting Recipes: A Problem-Solution Approach (2005, Apress) ===== My code in this post, if any, assumes the POSIX locale ===== and is released under the GNU General Public Licence |