Hello,
I have to call the stored procedure as argument from the unix shell program. Looks like unix doesnt like, can someone comment pls
Code:
USERID=scott
PASSWD=xxxxxx
PLSQLCALL=$2
STDT=`sqlplus /nolog <<END >> $LOGFILE
conn ${USERID}/${PASSWD}@${ORACLE_SID}
whenever sqlerror exit failure
set serveroutput on timing on lines 150 feedback on
declare
xx varchar2(100);
begin
$PLSQLCALL;
dbms_output.put_line('proc status: '||xx);
if xx <> 'Success' then
raise_application_error (-20001,'Failed');
end if;
end;
/
exit;
END`
Below execution is error
Code:
./test.sh secload "scott.Pk_Load_proc.Pr_sec_load('SecurityMaster',xx)"
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
...
Looks like unix doesnt like, can someone comment pls
Code:
USERID=scott
PASSWD=xxxxxx
PLSQLCALL=$2
STDT=`sqlplus /nolog <<END >> $LOGFILE
conn ${USERID}/${PASSWD}@${ORACLE_SID}
whenever sqlerror exit failure
set serveroutput on timing on lines 150 feedback on
declare
xx varchar2(100);
begin
$PLSQLCALL;
dbms_output.put_line('proc status: '||xx);
if xx <> 'Success' then
raise_application_error (-20001,'Failed');
end if;
end;
/
exit;
END`
Below execution is error
Code:
./test.sh secload "scott.Pk_Load_proc.Pr_sec_load('SecurityMaster',xx)"
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
...
Works fine for me. I've cooked up a simple testcase based on your example.
Code:
SQL>
SQL>
SQL> @f7.sql
SQL> --
SQL> drop table t;
Table dropped.
SQL> drop package pkg_load;
Package dropped.
SQL> --
SQL> create table t (a number(2), b varchar2(20));
Table created.
SQL> --
SQL> create or replace package pkg_load as
2 procedure pr_t_load (t varchar2, x out varchar2);
3 end;
4 /
Package created.
SQL> show errors
No errors.
SQL> --
SQL> create or replace package body pkg_load as
2 procedure pr_t_load (t varchar2, x out varchar2) is
3 e_too_big_number exception;
4 pragma exception_init(e_too_big_number, -1438);
5 begin
6 insert into t (a, b)
7 select rownum, t
8 from all_objects
9 where rownum <= 10;
10 x := 'Success';
11 exception
12 when e_too_big_number then
13 x := 'Failure';
14 end;
15 end;
16 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL>
Test for success -
Code:
$
$
$ cat -n f7.sh
1 #!/usr/bin/bash
2 PLSQLCALL=$2
3
4 sqlplus -s test/test <<END
5 whenever sqlerror exit failure
6 set serveroutput on timing on lines 150 feedback on
7 declare
8 xx varchar2(100);
9 begin
10 $PLSQLCALL;
11 dbms_output.put_line('proc status: '||xx);
12 if xx <> 'Success' then
13 raise_application_error (-20001,'Failed');
14 end if;
15 end;
16 /
17 exit;
18 END
19
$
$
$ ./f7.sh secload "test.pkg_load.pr_t_load('SOME_TEXT_HERE', xx)"
proc status: Success
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
$
$
And test for failure -
Code:
SQL>
SQL>
SQL> truncate table t;
Table truncated.
SQL>
SQL> alter table t modify (a number(1));
Table altered.
SQL>
SQL>
Code:
$
$ ./f7.sh secload "test.pkg_load.pr_t_load('SOME_TEXT_HERE', xx)"
proc status: Failure
declare
*
ERROR at line 1:
ORA-20001: Failed
ORA-06512: at line 7
$
$
Thanks for the reply, my proc executes always in sqlplus without any issues, not sure why unix code gives error...
Code:
var xx varchar2(100);
SQL> scott.Pk_Load_proc.Pr_sec_load('SecurityMaster',xx)
Business Date :--> 05-FEB-2010
Total number of records :--> 380
Total number of records inserted :--> 0
Total number of records Updated :--> 380
Total number of records rejected :--> 0
Process Starting time :--> 18-AUG-10 11.16.56.549698 AM
Process Ending time :--> 18-AUG-10 11.16.56.936462 AM
Total Time Taken :--> .39 seconds
sh execution
-----------
./test.sh test "scott.Pk_Load_proc.Pr_sec_load('SecurityMaster',xx)"
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
---------- Post updated at 12:01 PM ---------- Previous update was at 11:24 AM ----------
my be this is what creating the issue i guess..
Here is the complete content in the shell
Code:
TODAY=`date +%Y%m%d`
LOGFILE=${LOGDIR}/${0##*/}.${TODAY}.$$.log
exec > ${LOGFILE} 2>&1
printf "start as $0 `date`" >>$LOGFILE
USERID=scott
PASSWD=xxxxxx
PLSQLCALL=$2
STDT=`sqlplus /nolog <<END >> $LOGFILE
conn ${USERID}/${PASSWD}@${ORACLE_SID}
whenever sqlerror exit failure
set serveroutput on timing on lines 150 feedback on
declare
xx varchar2(100);
begin
$PLSQLCALL;
dbms_output.put_line('proc status: '||xx);
if xx <> 'Success' then
raise_application_error (-20001,'Failed');
end if;
end;
/
exit;
END`
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
I may be missing something, but the above error messages did not come from the script posted in post #1. They also did not come from the Shell itself or SQL Plus - they are custom error messages.
They actually look like the result of some basic vaildation that a parameter was provided to the script. i.e. a piece of shell script which is testing parameter "$#" (the number of parameters supplied). The "Usage" message suggests that the script is expecting one parameter whereas your sample script is taking two parameters.
Your reply is appreciated, i guess i haven't explain the problem clearly. let me brief it again. Below is my complete list of shell program, all it does is execute the sql procedure and creates a log file as the shell program name with date and time + .log in the log file directory. The shell program takes an argument as well.
Now, i need to pass the stored procedure dynamically so that i could reuse my shell program.
Once the shell program executed eg ./test.sh tttt
it creates a log file called test.sh.20100818.19650.log in the log directory.
Please note that, upone passing the second parameter as stored procedure,
there is no log files created with above shell.log and nothing executes...
so giving the second argument 2 does not create the log with same shell program name
and it creates below
idlbatch@vdusappx0201> more security_master_load_stat.0818
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
Error: Command Line argument is required
Usage: ./test.sh <table name without prefix>
Code:
#!/bin/ksh
. /apps/idlbatch/ideaalenv
. /apps/idlbatch/errorenv
TODAY=`date +%Y%m%d`
LOGFILE=${LOGDIR}/${0##*/}.${TODAY}.$$.log
exec > ${LOGFILE} 2>&1
printf "start as $0 `date`" >>$LOGFILE
#---------------------------------------------------------------------
# Error Routine
#---------------------------------------------------------------------
error_routine()
{
msg="$1"
# Add error message and Abnormal Termination message to log
echo "***ERROR*** ${msg} " >> $LOGFILE
echo "********* ABNORMAL TERMINATION OF Load_ia.sh at `date`*********" | >> $LOGFILE
# E-mail support contacts
mailx -s "Load_security_master.sh : ${msg} " test.test@xxxxxx.com < $LOGFILE
# Exit Unix Script Abnormally
exit 1
}
#---------------------------------------------------------------------
# Start of Main Script
#---------------------------------------------------------------------
FILE=$1
if [ $# != 1 ]
then
LOGFILE="$LOGDIR/security_master_load_stat.`date '+%m%d'`"
echo "\nError: Command Line argument is required">>$LOGFILE
echo "Usage: $0 <table name without prefix> ">>$LOGFILE
exit 1
fi
#LOGFILE="$LOGDIR/${FILE}_load_stat.`date '+%m%d'`"
#echo "START $FILE load: `date`">$LOGFILE
USERID=scott
PASSWD=xxxxxx
PLSQLCALL=$2
#---------------------------------------------------------------------
# Check Database Connection
#---------------------------------------------------------------------
dbconnect=`sqlplus /nolog <<end >> $LOGFILE
whenever sqlerror exit failure
conn ${USERID}/${PASSWD}@${ORACLE_SID}
exit;
end`
if [ $? != 0 ]
then
echo "database connection error"
exit $DATABASE_CONNECT_ERROR
fi
#---------------------------------------------------------------------
# Invoke SQL PLUS
#---------------------------------------------------------------------
#TMPLOG=./tmp.log
STDT=`sqlplus /nolog <<END >> $LOGFILE
conn ${USERID}/${PASSWD}@${ORACLE_SID}
whenever sqlerror exit failure
set serveroutput on timing on lines 150 feedback on
declare
xx varchar2(100);
begin
pkg.security_exchange('SecurityMasterAutosys',xx);
#$PLSQLCALL;
dbms_output.put_line('proc status: '||xx);
if xx <> 'Success' then
raise_application_error (-20001,'Failed');
end if;
end;
/
exit;
END`
STDT=$?
echo " Security Master Log Status = ${STDT}" >> $LOGFILE
#echo "END $FILE load: `date`">>$LOGFILE
printf "End of process $0 `date`" >> $LOGFILE
exit $STDT
As we guessed the script contains basic validation and checks the number of parameters supplied to the script.
Code:
#---------------------------------------------------------------------
# Start of Main Script
#---------------------------------------------------------------------
FILE=$1
if [ $# != 1 ]
then
LOGFILE="$LOGDIR/security_master_load_stat.`date '+%m%d'`"
echo "\nError: Command Line argument is required">>$LOGFILE
echo "Usage: $0 <table name without prefix> ">>$LOGFILE
exit 1
fi
It appears that the script has been modified to introduce a second parameter $2 and the validation has not been changed. It is questionable whether the first parameter $1 has any purpose in the script as posted.
Hello all,
I am trying to run stored procrdure from shell script which takes one argument. And also I want to verify in the script whether the script executed successfully. However the Stored procedure is not running from shell script. Manually if I run it update the data in the table.
Can... (29 Replies)
I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
hi all
please tell me how to pass parameters 4m shell script to a DataBase stored procedure.
To be specific i have sybase DB.
i mean i want the syntax of the command..
how to connect to DB, pass user id and password, pass the required parameter to SP..
..
need ur help frnds..
hema (0 Replies)
HI,
I have a similar problem to thread 18264, only I couldn't get it to work.
https://www.unix.com/showthread.php?t=18264
I have a stored procedure which is called by a shell script program. When I run the stored procedure alone or through the shell script, it works fine with output text... (3 Replies)
I have a db2 stored procedure on my database which require 3 parameters, I have the following db2 command in a sql script
CONNECT TO SAMPLE;
CALL BACKUP(INPUT_1, INPUT_2, INPUT3);
Usually, I would just invoke this sql script from my shell script like below
db2 -tf... (1 Reply)
Hi All,
This is a very starnge problem I am having. I have a shell script that calls a stored procedure.
Here's my code in shell script:
sqlplus "userid/pwd" @file.sql
and file.sql has the following statement:
exec my_storedProc;
Now, when I execute my shell script, nothing... (2 Replies)
08-18-2010
