Hello,
I have to call the stored procedure as argument from the unix shell program. Looks like unix doesnt like, can someone comment pls
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
./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.
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 -
$
$
$ 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 -
SQL>
SQL>
SQL> truncate table t;
Table truncated.
SQL>
SQL> alter table t modify (a number(1));
Table altered.
SQL>
SQL>
$
$ ./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...
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
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>
#!/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.
#---------------------------------------------------------------------
# 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.
i have removed the validation and giving only the procedure call as input. but giving an error, can some one please check?
SQL> Connected.
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 ;
*
ERROR at line 4:
ORA-06550: line 4, column 2:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
The symbol "exit" was substituted for ";" to continue.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option
Security Master Log Status = 1
End of process ./test.sh Wed Aug 18 13:40:30 EDT 2010