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

1 vote

0 answers

1264 views

                          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 /@${db_name}   
    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?

                        

Asked by A.Bhargava (11 rep)

Feb 27, 2018, 04:07 AM
Last activity: Mar 9, 2019, 12:08 PM

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

Related Questions