Referencing dynamically created table names in Create statement written in shell script

Question

I need to write a script to automatically generate a month end report.

The generation process involved creating tables in database and then accessing them later in the same script to create another temp tables.

I managed to create the base table dynamically, but facing problem while creating another temp table using former dynamic table as an reference.

Table 1: (Create statement is in an sql file)

Create table XYZ as
(
Select * from abc
);

NOTE: table abc is already present in the system. Thus not much issue in creating table XYZ.

Table2: (Create statement is in an sql file)

Create table JKL as
(
Select distinct list from XYZ
);

Now, I am not able to find a way to access table XYZ while creating JKL. I thought of assigning table name XYZ in a variable, but I have many such tables to be created with different tables as a reference, so I dont think creating seperate variable for each table name will be an efficient idea.

Following is my shell script to create table 1:

#!/bin/ksh

#Read the file file_table_map.sql to fetch the sql file name and corresponding table name

#file where the info related to create statement, prefix of table name and DB is stored.

file_table_lst_map=`echo ${abhi_SRC}/file_table_map.lst`

date_prev=`date --d 'last month'  +%b"_"%Y`

RunSQLCMD()
{
echo "Code came here"
echo $file_name
echo $table_name
echo $db_name
sqlplus -s <username>/<password>@${db_name} <<EOF
@$file_name $table_name
EOF
if [ $? -ne 0 ]
then
       echo "Failed to execute ${file_name}"
        return 1
   fi
    echo "Success in executing sqlcmd for ${file_name}"
    return 0
}


cat $file_table_lst_map|while read r
        do
                file_name=`echo $r|awk '{print $1}'`
                tab_name=`echo $r|awk '{print $2}'`
                #prepare the table name to be created
               table_name=$tab_name"_"$date_prev
                db_name=`echo $r| awk '{print$3}'`
                echo "========================================"
                #RunSQLCMD "${file_name}" "${table_name}" "${db_name}"
                #echo $date_prev
                echo " File to be executed - " $file_name "/" "table created - " $table_name "/" "db_name - "$db_name
        done

#echo $table_name

The file "file_table_map.lst" is in following format

<SQL file path and name> <table name prefix> <DB>
abc.sql abc DB_Z

SQL Command to create table 1 is as follows

CREATE TABLE &1 AS
(
SELECT to_char(add_months(SYSDATE,-1),'MON_yyyy') as dat FROM DUAL
);

Where &1 is table name abc_jan_2018

create table &1 AS
(
SELECT * FROM &2
)

where &1 is table name JKL_jan_2018 and &2 will be abc_jan_2018.

How to enhance the script to facilitate this functionality?

Referencing dynamically created table names in Create statement written in shell script

0 Answers0