how can i execute pl/sql code using shell script

[sajumm]

Hello,

I am new to shell scripting. I am trying to write shell script code for executing pl/sql code. The purpose is whenever the shell script run the pl/sql code need to execute and generate report. I am looking for an early respone, thanks for your help.

Thanks,
SAM.

 

[Salem]

At the very basic level, a shell script is just a record of the commands you would type in, and as such just saves you a lot of typing.

So the first question is, do you know what to type in order to "the pl/sql code need to execute and generate report"

 

[ajayarora]

Hi Sajjum,

The command line to call a stored proc from unix shell is :


sqlplus -s <<!>${TMPFILE}
${UID_PASSWD}
set tab off head off echo off feed off pages 0 serverout on size 1000000 linesize ${MAX_LINES_SIZE} wrap off verify
off trimspool on
whenever SQLerror exit 10
whenever OSerror exit 20
${SQLSTMT}
!
SQLRC=$?

The command will return 10 in case of any oracle error and 20 in case of OS error.

Let me know if you need some more info.

Regards
Ajay.

 

[sybaseguru]

Sam

Look at this example that I recently gave in Oreacle 9i forum. The shell script creates public synonym. Look at the way it works to interact with SQL*Plus

oracle@linux:/home/oracle/dba/bin% more create_public_synonym.ksh
#!/bin/ksh
#--------------------------------------------------------------------------------
#
# Procedure:    create_public_synonym.ksh
#
# Description:  creates public synonym for a give schema
#
# Parameters:   database name, username, schema
#
#--------------------------------------------------------------------------------
# Vers|  Date  | Who | DA | Description
#-----+--------+-----+----+-----------------------------------------------------
# 1.0 |18/02/98|  MT |    | Initial Version
#--------------------------------------------------------------------------------
#
DATABASE_NAME=$1
USERNAME=$2
SCHEMA=$3

if [[ -z ${DATABASE_NAME} ]]
then
        echo "Abort: $0 failed. No database name specified on command line"
        exit 1
fi

if [[ -z ${USERNAME} ]]
then
        echo "Abort: $0 failed. No username specified on command line"
        exit 1
fi

if [[ -z ${SCHEMA} ]]
then
        echo "Abort: $0 failed. No schema specified on command line"
        exit 1
fi

ENVFILE=$HOME/${DATABASE_NAME}/dba/bin/environment.ksh

if [[ -f $ENVFILE ]]
then
        . $ENVFILE
else
        echo "Abort: $0 failed. No environment file ( $ENVFILE ) found"
export USERNAME
FILE_NAME=`basename $0 .ksh`
#
PASSWORD=$(get_password.ksh $DATABASE_NAME $USERNAME)
if [ $? != 0 ]
then
        echo "Abort: $0 failed. Query for Oracle password for user $USERNAME failed"
        exit 1
#
fi
IN_FILE="${LOGDIR}/${DATABASE_NAME}_${USERNAME}_${FILE_NAME}.sql"
[ -f ${IN_FILE} ] && rm -f ${IN_FILE}
sqlplus -S ${USERNAME}/${PASSWORD}@${DATABASE_NAME} << ! > ${IN_FILE}
set feedback off;
set heading off;
SELECT 'set serveroutput on' FROM dual;
SELECT 'set echo off' FROM dual -- do not display line commands;
SELECT 'PROMPT Creating public synonym for ${SCHEMA}.'||table_name||CHR(13)||CHR(10)||
'CREATE PUBLIC SYNONYM '||table_name||' FOR ${SCHEMA}.'||table_name||';' FROM ALL_TABLES
WHERE owner = UPPER('${SCHEMA}')
ORDER BY table_name;
SELECT 'exit' FROM dual;
exit
!
#
# get rid of 'rows selected' crap from ${IN_FILE}
#
cat ${IN_FILE} | egrep -v 'selected|TABLE_NAME|--'|sed -e '/^$/d'|sed -e &quot;s/#/'/g&quot; > temp.sql
mv temp.sql ${IN_FILE}
#
LOG_FILE=&quot;${LOGDIR}/${DATABASE_NAME}_${USERNAME}_${FILE_NAME}.log&quot;
[ -f ${OUT_FILE} ] && rm -f ${OUT_FILE}
sqlplus ${USERNAME}/${PASSWORD}@${DATABASE_NAME} @${IN_FILE} > ${LOG_FILE}

The output from the sql file will look like

oracle@linux:/home/oracle/mydb/dba/log% more mydb_system_create_public_synonym.sql
set serveroutput on
set echo off
PROMPT Creating public synonym for scott.BONUS
CREATE PUBLIC SYNONYM BONUS FOR scott.BONUS;
PROMPT Creating public synonym for scott.CHILD
CREATE PUBLIC SYNONYM CHILD FOR scott.CHILD;
PROMPT Creating public synonym for scott.CRAP
CREATE PUBLIC SYNONYM CRAP FOR scott.CRAP;
PROMPT Creating public synonym for scott.DEPT
CREATE PUBLIC SYNONYM DEPT FOR scott.DEPT;
PROMPT Creating public synonym for scott.EMP
CREATE PUBLIC SYNONYM EMP FOR scott.EMP;
PROMPT Creating public synonym for scott.EMP_NEW
CREATE PUBLIC SYNONYM EMP_NEW FOR scott.EMP_NEW;
PROMPT Creating public synonym for scott.EMP_TEST
CREATE PUBLIC SYNONYM EMP_TEST FOR scott.EMP_TEST;
PROMPT Creating public synonym for scott.KWORD
CREATE PUBLIC SYNONYM KWORD FOR scott.KWORD;
PROMPT Creating public synonym for scott.PARENT
CREATE PUBLIC SYNONYM PARENT FOR scott.PARENT;
PROMPT Creating public synonym for scott.PLAN_TABLE
CREATE PUBLIC SYNONYM PLAN_TABLE FOR scott.PLAN_TABLE;
PROMPT Creating public synonym for scott.RESULT
CREATE PUBLIC SYNONYM RESULT FOR scott.RESULT;
PROMPT Creating public synonym for scott.SALGRADE
CREATE PUBLIC SYNONYM SALGRADE FOR scott.SALGRADE;
PROMPT Creating public synonym for scott.TEST
CREATE PUBLIC SYNONYM TEST FOR scott.TEST;
PROMPT Creating public synonym for scott.TEST_DEFAULT
CREATE PUBLIC SYNONYM TEST_DEFAULT FOR scott.TEST_DEFAULT;
exit

Hope this helps