Re: Run a PL SQL Package from a shell script

On 2006-09-01, nicetom786@xxxxxxxxx wrote:
Chris F.A. Johnson wrote:
On 2006-09-01, nicetom786@xxxxxxxxx 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@database

Don't use echo:

printf "%s\n" "set serveroutput on" "set timing on" \
"execute Package.Procedure" | sqlplus username/password@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
.

Relevant Pages

  • Inheriting proper env for crontab
    ... I can successfully execute a shell-script ... as cron job because sqlplus does not execute properly. ... and added the following line to crontab: ... the command sqlplus because sqlplus is not in its PATH. ...
    (comp.unix.shell)
  • Inheriting proper env for crontab
    ... I can successfully execute a shell-script ... as cron job because sqlplus does not execute properly. ... and added the following line to crontab: ... This has better success because sqlplus is now in its PATH. ...
    (comp.unix.misc)
  • Re: Inheriting proper env for crontab
    ... as cron job because sqlplus does not execute properly. ... and added the following line to crontab: ... the command sqlplus because sqlplus is not in its PATH. ...
    (comp.unix.shell)
  • Re: Inheriting proper env for crontab
    ... as cron job because sqlplus does not execute properly. ... and added the following line to crontab: ... the command sqlplus because sqlplus is not in its PATH. ...
    (comp.unix.shell)
  • Re: [kde-linux] Mounting with exec flag
    ... And I can copy a shell script to both devices and then execute ... UDEV config problem [if KDE uses UDEV info to mount devices) (I use ...
    (KDE)