1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.