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:


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

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

cat $file_table_lst_map|while read r
                file_name=`echo $r|awk '{print $1}'`
                tab_name=`echo $r|awk '{print $2}'`
                #prepare the table name to be created
                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

#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

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

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.