How to use special character in procedure call?

Shell Programming and Scripting
1

Hi,
I have following script which calls sql to create staging table like t1_rpt_1day_stg,t1_rpt_7day_stg

The following line has '' and causing all data in directory to show up in email notification but if I give all column names then it works,
Please let me know how to use '' instead of giving all column names.

'create table t1_rpt_<parameterized>_stg as select * from t1_rpt_tmt'

--shell script

load_data_to_oracle()
{
# truncate or create stage table first
${ORACLE_HOME}/bin/sqlplus ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} << EOF > ${TMP_LOG_FILE} 2>&1
set serveroutput on
@${CREATE_STAGE_SQL}
COMMIT;
QUIT;
EOF

## Function Main

# variables
CREATE_STAGE_SQL=${SQL_DIR}/stage_create.sql

load_data_to_oracle
    exit 0

---sql to create staging table

stage_create.sql
declare
v_sql_string varchar2(4000);
begin
# some code
     execute immediate 'create table t1_rpt_<parameterized>_stg as select * from t1_rpt_tmt';
#some error checking     
end;

Thanks
Sandy

2

First of all it seems really strange to me that you noticed path-name expansion (globbing) inside an SQL block. Maybe I'm missing something.

But you can disable path-name expansion (globbing) by simply calling:

set -f
3

Not sure what you mean without seeing actual example because the '*' just tells oracle to return all columns, but try it like this:

param=

load_data_to_oracle ()
{
crt_stage_tbl_results=`${ORACLE_HOME}/bin/sqlplus -s ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} <<EOF
set linesize 500 heading off feedback off
whenever sqlerror exit 1
create table t1_rpt_$param_stg as select * from t1_rpt_tmt;
exit 0
EOF`
if [[ $? = 1 ]] then
  ts=`date "+%Y-%m-%d-%H.%M.%S"`
  echo "`echo "$crt_stage_tbl_results" | sed 's/^[ \t\n]*/'$ts' /'`" >> ${TMP_LOG_FILE}
  echo `date "+%Y-%m-%d-%H.%M.%S"` "**The above error occurred creating staging table(t1_rpt_$param_stg)." >> ${TMP_LOG_FILE}
  return 1
fi
return 0
}
4

thanks, actually requirement is to call different environment in same
shell script that is why I am giving path names here and using config file to keep all these parameter values.

---------- Post updated at 06:32 AM ---------- Previous update was at 06:28 AM ----------

thanks much, problem is after creation of stage table is successfully completed; a email generates (mail -s) and contents of that email has all the sqls from above query (which is fine) but the '*' causing list of directorties/files in folder to show up in the email content too.

5

Bumping up posts or double posting is not permitted in these forums.

Please read the rules, which you agreed to when you registered, if you have not already done so.

You may receive an infraction for this. If so, don't worry, just try to follow the rules more carefully. The infraction will expire in the near future

Thank You.

The UNIX and Linux Forums.