Visit The New, Modern Unix Linux Community


Calling SQL scripts through Shell Script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calling SQL scripts through Shell Script
# 1  
Calling SQL scripts through Shell Script

Oracle and Scripting gurus,

I need some help with this script...

I am trying to add the query
Code:
SELECT * FROM 
ALL_SYNONYMS 
WHERE SYNONYM_NAME = 'METADATA'

in the current script....

Read the result set and look for the TABLE_NAME field. If the field is pointing to one table eg. METADATA_A, I need to truncate the opposite table METADATA_B.

Then, load the opposite table using SQLLOADER.

Then, switch Synonym by CREATE OR REPLACE PUBLIC SYNONYM to the opposite table.

I tried to add these commands in the script..Somehow, it is not working...

Code:
#!/usr/bin/ksh

FILE_NAME=`basename $0 .ksh`

SPOOL_FILE=${PARTNER_DB}/$FILE_NAME.spool
LOG_FILE=${PARTNER_DB}/$FILE_NAME.log

DT_START=`date`

echo "-----------------------------------------" >> $LOG_FILE
echo "Execution Started at :"$DT_START>>$LOG_FILE
echo "Connecting to Oracle...">>$LOG_FILE

tab=`$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF >>$LOG_FILE
      CONNECT ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
      set lines 250
      set trims off
      set wrap off
      set head off
      set pages 0
      spool $SPOOL_FILE

      WHENEVER SQLERROR EXIT SQL.SQLCODE;

      TRUNCATE TABLE METADATA_A;

      spool off;
      exit
EOF`

SQL_STATUS=$?

if [ ${SQL_STATUS} -ne 0 ]
then
   echo "Error in the SQL...."  >>${LOG_FILE}
else
   echo "Successfully Completed...."  >>${LOG_FILE}
fi

DT_END=`date`

echo "Execution Ended at :"$DT_END>>$LOG_FILE
echo "--------------------------------------------">>$LOG_FILE
exit ${SQL_STATUS}

# 2  
For starters, you can retrieve all of the information from a single SQL statement and base logic from these values:
Code:
IFS=$(echo '\012\001') # Newlines only
for LINE in $(
    sqlplus -s /nolog <<!
        connect un/pw@db
        SET FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 TRIMSPOOL ON
        COL text FORMAT A150 FOLD_AFTER
        SELECT CASE
                   WHEN table_name = 'METADATA_A'
                       THEN 'sqlldr_table=METADATA_B'
                       ELSE 'sqlldr_table=METADATA_A'
               END text
              ,CASE
                   WHEN table_name = 'METADATA_A'
                       THEN 'dml_cmd=CREATE OR REPLACE PUBLIC SYNONYM METADATA FOR METADATA_B'
                       ELSE 'dml_cmd=CREATE OR REPLACE PUBLIC SYNONYM METADATA FOR METADATA_A'
               END text
        FROM   all_synonyms
        WHERE  synonym_name = 'METADATA';
!)
do
    case ${LINE} in
        dml*)    DML_CMD=${LINE##*=} ;;
        sqlldr*) SQLLDR_TBL=${LINE##*=} ;;
    esac
done
print ${DML_CMD}
print ${SQLLDR_TBL}

Code:
DML_CMD=CREATE OR REPLACE PUBLIC SYNONYM METADATA FOR METADATA_A
SQLLDR_TBL=METADATA_A

# 3  
Thank you for the advice Thomas..

I did get an idea of what you gave me....

I have to truncate the opposite table and then use this sqlldr script to load the table. I have already written a generic sqlldr script to load the table...

Code:
#!/usr/bin/ksh

USERID=$1
CONTROL_FILE=$2
DATA_FILE=$3

$ORACLE_HOME/bin/sqlldr UN/PW@DB control=$SCRIPT_DIR/${CONTROL_FILE} log=$SCRIPT_DIR/${CONTROL_FILE}.log data=$FILES/temp/${DATA_FILE}

I will have to use this generic script to load the table. But the table name is hard coded in the Control File....
Do I require two different control files?

One for table A and another for table B.

Control File:
Code:
LOAD DATA
append
INTO TABLE METADATA_A
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(
 PRODUCT_CD "SUBSTRB(:PRODUCT_CD , 1, 30)"
,NAME "SUBSTRB(:NAME , 1, 30)"
,NO_CUSTOM_VALUE "SUBSTRB(:NO_CUSTOM_VALUE , 1, 1)"
,CUSTOM_VALUE1_NAME "SUBSTRB(:CUSTOM_VALUE1_NAME , 1, 30)"
,CUSTOM_VALUE1_TYPE "SUBSTRB(:CUSTOM_VALUE1_TYPE , 1, 1)"

.................

Please advise...

Thank You,
Madhu
# 4  
Certainly, use two control files. You can't bind variables in a control file. Otherwise, you'll need to modify a copy of the control file at run-time as I have had to at times or you'll have to load an intermediate table and use some addition code to move the data from the intermediate table to the METADATA table.
# 5  
Thomas,

I tried to use your script with two control files MetadataA.ctl and MetadataB.ctl

I am unable to execute the Load.ksh script through your script. The Load.ksh script requires three parameters. 1) USERID 2) CONTROL FILE and 3) DATA FILE.

I am not sure how to pass the control file parameter in your SELECT CASE statement.

Please advice...This is becoming more and more complicated for me and I am scratching my head Smilie
# 6  
I need to see what your script looks like; please post the section that fails when calling your Load.ksh script and please post the part of Load.ksh that handles the parameters.
# 7  
Thank you Thomas....

The LoadMetadata.ksh script

USERID_logon
Code:
userid=user/pwd@db

Code:
#!/usr/bin/ksh

USERID=$1
CONTROL_FILE=$2
DATA_FILE=$3

LOGON=`cat ${PARTNER_DB}/${USERID}_logon`
$ORACLE_HOME/bin/sqlldr $LOGON control=$PARTNER_DB/${CONTROL_FILE} log=$PARTNER_DB/${CONTROL_FILE}.log data=$FILES/temp/${DATA_FILE}

I am calling this Load.ksh through your script....I also need to truncate the opposite table after checking which table is currently in use...

Code:
USERID=$1
DATA_FILE=$2
CONTROL_FILEA=MetadataA.ctl
CONTROL_FILEB=MetadataB.ctl

IFS=$(echo '\012\001') # Newlines only
for LINE in $(
    sqlplus -s /nolog <<!
        CONNECT ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
        SET FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 TRIMSPOOL ON
        COL text FORMAT A150 FOLD_AFTER
        SELECT CASE
                   WHEN table_name = 'ACXIOM_METADATA_A'
                       THEN 'LoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
                       ELSE 'LoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
               END text
              ,CASE
                   WHEN table_name = 'ACXIOM_METADATA_A'
                       THEN 'dml_cmd=CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B'
                       ELSE 'dml_cmd=CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_A'
               END text
        FROM   all_synonyms
        WHERE  synonym_name = 'ACXIOM_METADATA';
!)
do
    case ${LINE} in
        dml*)    DML_CMD=${LINE##*=} ;;
        sqlldr*) SQLLDR_TBL=${LINE##*=} ;;
    esac
done
print ${DML_CMD}
print ${SQLLDR_TBL}


Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #916
Difficulty: Easy
In Javascript, the console is an object which provides access to the browser debugging console.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[Solved] Calling PL/SQL Block into Shell Script

Hi, i have one simple PL/SQL Block and i have saved it as .sql file, which i am trying to call from UNIX script. PL/SQL block structure CONNECT DB_NAME/PWD@Database whenever SQLERROR EXIT 1; Declare ..Variables... BEGIN --Code-- exception END; exit; I have save this block as... (3 Replies)
Discussion started by: abhii
3 Replies

2. Shell Programming and Scripting

shell script hangs while calling sql file

I have a master shell script which calls some 40 shell scripts. All the shell scripts calls a sql file which executes some sql statements. I run these scripts in parallel such that it saves me time. When i executed them i saw some strange behavior. Firstly, I found that some scripts among the 40... (1 Reply)
Discussion started by: sushi
1 Replies

3. Shell Programming and Scripting

Calling sql file from shell script with parameters.

Hi, I am calling a sql file script.sql from shell script and passing few parameters also as shown below: sqlplus -S id/password @script.sql $param1 $param2 Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as... (7 Replies)
Discussion started by: anil029
7 Replies

4. Shell Programming and Scripting

calling a sql file in my shell script

Hi, I want to call a sql file in my shell script. see the below code:- if ] then ( isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://" ${S_PWD} set nocount on go use ${S_DB} go // need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies

5. Shell Programming and Scripting

calling 'n' number of shell scripts based on dependency in one shell script.

Hello gurus, I have three korn shell script 3.1, 3.2, 3.3. I would like to call three shell script in one shell script. i m looking for something like this call 3.1; If 3.1 = "complete" then call 3.2; if 3.2 = ''COMPlete" then call 3.3; else exit The... (1 Reply)
Discussion started by: shashi369
1 Replies

6. UNIX for Advanced & Expert Users

Calling sql file from shell script

Hi I have a shell script that call a sql file. The sql file will create a spool file. My requirement is, when ever i get an OS error like file not found. I have to log it in a log file. Could some who worked in a like scenario help me by giving the code sample. Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies

7. Shell Programming and Scripting

Calling sql in shell script with parameters

Dear All, I want to call an sql script within a unix shell script. I want to pass a parameter into the shell script which should be used as a parameter in teh sql script. e.g $ ./shell1.sh 5000129 here 5000129 is a prameter inside shell script i am calling one sql script e.g. ... (2 Replies)
Discussion started by: Radhe
2 Replies

8. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

9. Shell Programming and Scripting

calling sql file from shell script

Hello everybody I need help calling sql file from shell script. Can anyone help me creating a small shell script which calls an sql file . The .sql file should contain some select statements like select emp_no from emp_table; select emp_id from emp_table; And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies

10. UNIX for Advanced & Expert Users

Calling PL/SQL Script in Shell Programming

Hi all, In a shell script I need to pass two parameters to a pl/sql script and get the ouput of the pl/sql script and use it in shell script. For example Shell script : test.sh PL/SQL script : get_id.sql parameter1 parameter2 Actually get_id.sql has a select statement something... (1 Reply)
Discussion started by: lijju.mathew
1 Replies

Featured Tech Videos