SQLPLUS calling Via Script

UNIX for Beginners Q & A
1

Hello All,

Could you please help me if i am doing anything wrong in below script, especially the sqlplus part performance wise or anything else i could improvise in the script. Thank you.

#!/bin/ksh
## Batch Obj Id
MP_BCH_OBJ_ID=$1
PASS=$2
partition=$3

## script dir
RUN_DIR=$(dirname $0)

## Set/Source Environment #
cd ${RUN_DIR}


${ORACLE_HOME}/bin/sqlplus -s /nolog <<-EOF > /dev/null
conn el1acpt2/$PASS@AOWH01
spool ${SRC_DIR}/SRVC_CONV/RDW_SRVC_CONV_${MP_BCH_OBJ_ID}_$partition.dat;
set pagesize 0
set trimspool on
set heading off
set feedback off
select ACTVY_RPTNG_PER_DT||'|'||LN_ID||'|'||ARM_IDX_CD||'|'||RCRS_TYP_CD||'|'||LNDR_LN_ID from LN_ACTVY partition($3);
spool off;
EXIT;
EOF
2

Anything wrong with that script? Error messages? Undesired results?

3

I don't see anything wrong with your script.

By the way you can establish a direct database connection instead of having a connection-less session with /NOLOG and then connecting.

${ORACLE_HOME}/bin/sqlplus -s  el1acpt2/$PASS@AOWH01 <<-EOF > /dev/null

NOLOG is useful for performing some database administration tasks, writing transportable scripts, or to use SQL*Plus editing commands to write or edit scripts.

4

I am remember doing that in the past because the database username & passwords are visible, when if anyone else issues ps -ef command.

1 Like
5

Oh yes, /nolog will definitely help with that. By the way in our Linux environments it appears we used hide utility that will hide sqlplus arguments.

7

Hello All,
i am trying to extract the data file via sqlplus and i wanted to capture the output of sqlplus into a log file and check for any syntax,Connection, DB errors etc.
But for some reason my logfile is filled up with the data of sqlplus output, and also process is running a long time because of this.
Appreciate your further inputs.

#${ORACLE_HOME}/bin/sqlplus -s /nolog <<-EOF > ${LOG_DIR}/edw_extract_RDW_Partition.sh.${MP_BCH_OBJ_ID}_${partition}.sql.log
${ORACLE_HOME}/bin/sqlplus -s /nolog <<-EOF > /dev/null
conn ${APPLICATION_ID}/${DB_PASS}@${RDW_DB}
spool ${SRC_DIR}/RDW_SRVC_CONV_${MP_BCH_OBJ_ID}_${partition}.dat;
set pagesize 0
set trimspool on
set heading off
set feedback off
set linesize 500
select to_char(ACTVY_RPTNG_PER_DT,'mm/dd/yyyy hh24:mi:ss')||'|'||LN_ID||'|'||'|'||'|'||'|'||'|'||ARM_IDX_CD||'|'||'|'||'|'||'|'||'|'||'|'||'|'||RCRS_TYP_CD||'|'||'|'||'|'||LNDR_LN_ID||'|'||'|'||'|'||'|'||'${MP_BCH_OBJ_ID}'||'|'||'|'||'${MP_EDW_USR_ID}'||'|'||'${MP_BCH_STRT_DTTM}'||'|'||'|' from $RDW_Schema.LL_LN_ACTVY_3 partition(${partition});
spool off;
EXIT;
EOF
8

There is an Oracle system variable ERRORLOGGING.

This is useful for capturing errors generated from long running queries and avoids capturing all output using the SPOOL command, or having to remain present during the run.

Oracle writes errors to default table: SPERRORLOG You can run a select to view all the errors later:-

SELECT timestamp, 
       username, 
       script, 
       statement, 
       message 
FROM   sperrorlog;