The UNIX and Linux Forums  

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
Google UNIX.COM


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
difference in calling shell scripts arpit_narula SUN Solaris 6 09-07-2008 12:46 PM
Calling shell functions from another shell script jisha Shell Programming and Scripting 6 04-05-2008 01:29 PM
Calling SQL LDR and SQL plus scripts in a shell script rajagavini Shell Programming and Scripting 5 11-05-2007 03:12 PM
Calling Shell Script onlyroshni Shell Programming and Scripting 1 10-22-2007 04:29 PM
script calling other scripts hangs rein Shell Programming and Scripting 1 09-07-2007 02:26 AM

Reply
 
Submit Tools LinkBack Thread Tools Search this Thread Display Modes
  #1  
Old 06-07-2006
Registered User
 

Join Date: Nov 2005
Posts: 91
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}
Reply With Quote
Forum Sponsor
  #2  
Old 06-07-2006
Registered User
 

Join Date: Jan 2005
Posts: 682
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
Reply With Quote
  #3  
Old 06-08-2006
Registered User
 

Join Date: Nov 2005
Posts: 91
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
Reply With Quote
  #4  
Old 06-08-2006
Registered User
 

Join Date: Jan 2005
Posts: 682
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.
Reply With Quote
  #5  
Old 06-08-2006
Registered User
 

Join Date: Nov 2005
Posts: 91
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
Reply With Quote
  #6  
Old 06-08-2006
Registered User
 

Join Date: Jan 2005
Posts: 682
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.
Reply With Quote
  #7  
Old 06-08-2006
Registered User
 

Join Date: Nov 2005
Posts: 91
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}
Reply With Quote
Google The UNIX and Linux Forums
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes




All times are GMT -7. The time now is 11:10 PM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited.
The UNIX and Linux Forums Content Copyright ©1993-2008. All Rights Reserved.Ad Management by RedTyger Visit The Complex Event Processing Blog

Content Relevant URLs by vBSEO 3.2.0