Shell Programming and Scripting

1.t1_rpt_1day_stg
2.t1_rpt_7day_stg
3.t1_rpt_30day_stg
4.t1_rpt_CTD_stg

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

##function call
if [[ $type = 'a' || $type = 'A' ]]
then
    load_data_to_oracle
    exit 0
fi

stage_create.sql
set serveroutput on size 1000000
declare
v_err_code   varchar2(500);
v_err_mesg   varchar2(1000);
v_table_name varchar2(100);
v_sql_string varchar2(4000);
v_table_exists number;
begin
# some code
     execute immediate 'create table t1_rpt_<parameterized>_stg as select * from t1_rpt_tmt';
#some error checking     
end;

for param in 1day 7day 30day CTD
do
   load_data_to_oracle
done

@${CREATE_STAGE_SQL} "$param"

execute immediate 'create table t1_rpt_&1._stg as select * from t1_rpt_tmt';

SQL*Loader-941: Error during describe of table T1_1DAY_STG
ORA-04043: object T1_1DAY_STG does not exist

begin

  select count(1) into v_table_exists
   from user_tables
  where table_name = 'TXPCY_&1._STG';
  
  dbms_output.put_line ('table name ' || 'TXPCY_&1._STG');

  if v_table_exists != 0 then
      execute immediate 'create table txpcy_&1._stg as select * from txpcy_tmt';
     dbms_output.put_line('stage table not exists and created...' );
  end if;

  execute immediate 'GRANT DELETE, INSERT, SELECT, UPDATE ON MT_RPT01.txpcy_&1._stg TO MT_RPT01_MO';

   commit;
exception
when others then
  v_err_code := sqlcode;
  v_err_mesg := substr(sqlerrm,1,1000);
  dbms_output.put_line('Others Error code <' || sqlcode || '> Error Message: '|| v_err_mesg);
  -
end;
/

load_data_to_oracle()
{
for i in 1DAY 7DAY 15DAY
do
${ORACLE_HOME}/bin/sqlplus ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} << EOF > ${TMP_LOG_FILE} 2>&1
set serveroutput on
@${CREATE_STAGE_SQL} "$i"
COMMIT;
QUIT;
EOF
}
########Main#######
CREATE_STAGE_SQL=stage_create_sp.sql
load_data_to_oracle

begin
execute immediate 'create table t1_&1._stg as select * from t1_rpt_tmt';
end;
/

SQL*Loader-941: Error during describe of table T1_1DAY_STG

begin
execute immediate 'create table t1_1day_stg as select * from t1_rpt_tmt';
end;
/

load_data_to_oracle()
{
for i in 1DAY 7DAY 15DAY
do
${ORACLE_HOME}/bin/sqlplus ${ORACLE_USER}/${ORACLE_PASSWD}@${ORACLE_SID} << EOF > ${TMP_LOG_FILE} 2>&1
set serveroutput on
@${CREATE_STAGE_SQL} $i
COMMIT;
QUIT;
EOF
}
########Main#######
CREATE_STAGE_SQL=stage_create_sp.sql
load_data_to_oracle