mjhjmtu

Recently, I have been allotted a task 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.

I’ll illustrate for better understanding:

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.

Please advise on how to enhance the script to facilitate this functionality.