Call procedure multiple time

Hi,

I have following script which calls sql to create staging table.How do I call load_data_to_oracle() multiple times so that it creates 4 staging as follows.

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

--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

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

---sql to create staging table

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;

Thanks
Sandy

Use a for loop to call the function:

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

Inside the function pass the $param as argument:

@${CREATE_STAGE_SQL} "$param"

Use first argument &1 for creating table:

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

Note: Don't forget the period . after &1 which defines the boundary.

I am getting following error when trying to create staging table.

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

This is PL/SQL block(stage_create_sp.sql) being called inside shell script

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;
/

Shell Script Call

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

The ORA error: ORA-04043 suggest that the table was not created successfully. So I guess the PL/SQL procedure didn't run successfully.

In PL/SQL, use semi-colon to define the end of line of code and in the end use forward slash / to execute it:

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

Thanks I have made changes but still got the same error.
I have updated the second post as well.

I didn't pay attention to this error before!

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

Please check and verify if you are running SQL Loader using a DB User who has sufficient privileges on object: T1_1DAY_STG

Or else check and verify if this object: T1_1DAY_STG exist in the DB User's schema.

Thanks..
But table does not exist and I am trying to create table, if I hard code the pl/sql block "t1_1day_stg" ( instead of passing parameter &1.) it works

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

Do you think I should try passing parameter without double quotes like this : -- @${CREATE_STAGE_SQL} $i

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

Thanks
Sandy

Double quotes is just fine. It should not be a problem.

I suggest you to put the PL/SQL procedure inside the SQL block in your function rather than calling using an .sql file just to understand what is happening:

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
   -- put the procedure here

I found fix..table names need to be upper case and also instead of "${i} -- use ${i}

I am glad that you found the fix.

But it is really strange that usually table name case should not really matter in Oracle! Oracle identifiers are case insensitive.