Oracle and Scripting gurus,
I need some help with this script...
I am trying to add the query
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...
#!/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}
For starters, you can retrieve all of the information from a single SQL statement and base logic from these values:
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}
DML_CMD=CREATE OR REPLACE PUBLIC SYNONYM METADATA FOR METADATA_A
SQLLDR_TBL=METADATA_A
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...
#!/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:
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
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.
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 
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.
Thank you Thomas....
The LoadMetadata.ksh script
USERID_logon
userid=user/pwd@db
#!/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...
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}
Probably this is along the lines of what you want:
WARNING: This is not tested so you assume the risks if you're going to simply paste it into you Load.ksh script.
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 'TRUNCATE TABLE ACXIOM_METADATA_B;'
ELSE 'TRUNCATE TABLE ACXIOM_METADATA_A;'
END text
,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 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B'
ELSE '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
TRUNCATE*|CREATE*)
sqlplus -s /nolog <<!
connect un/pw
${LINE}
!
;;
LoadMetadata*) exec ${LINE} ;;
esac
done
This will perform the command in this order:
- sqlplus TRUNCATE TABLE ACXIOM_METADATA_B;
- LoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}
- sqlplus CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_b;
Thank you again Thomas..
I have loaded a sample of 4 rows in to Table B. Switched the synonym to have Table B and then ran the script...
USERID=$1
DATA_FILE=$2
CONTROL_FILEA=prtnrAcxiomMetadataA.ctl
CONTROL_FILEB=prtnrAcxiomMetadataB.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 'TRUNCATE TABLE PARTNERDBA.ACXIOM_METADATA_B;'
ELSE 'TRUNCATE TABLE PARTNERDBA.ACXIOM_METADATA_A;'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B'
ELSE '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
TRUNCATE*|CREATE*)
sqlplus -s /nolog <<!
connect ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
${LINE}
!
;;
prtnrLoadMetadata*) exec ${LINE} ;;
esac
done
The script didn't truncate Table B, didn't load Table A and didn't replace the synonym to Table A.
The scripts is easy to understand but I am not sure why it is not executing anything...I wish I would atleast see any messages printing...
Looks like the quotes were getting clobbered. Try moving the SQL into a variable as follows:
USERID=$1
DATA_FILE=$2
CONTROL_FILEA=prtnrAcxiomMetadataA.ctl
CONTROL_FILEB=prtnrAcxiomMetadataB.ctl
IFS=$(echo '\012\001') # Newlines only
SQL="
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 'TRUNCATE TABLE PARTNERDBA.ACXIOM_METADATA_B;'
ELSE 'TRUNCATE TABLE PARTNERDBA.ACXIOM_METADATA_A;'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B'
ELSE 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_A'
END text
FROM all_synonyms
WHERE synonym_name = 'ACXIOM_METADATA';
"
for LINE in $(
sqlplus -s /nolog <<EOF
${SQL}
EOF)
do
case ${LINE} in
TRUNCATE*|CREATE*)
sqlplus -s /nolog <<!
connect ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
${LINE}
!
;;
prtnrLoadMetadata*) exec ${LINE} ;;
esac
done
No Luck Thomas...
Output
Connected.
0 rows deleted.
Connected.
I changed the TRUNCATE statement to just DELETE...It is not going further down the script to execute the shell script and also switch synonym..
USERID=$1
DATA_FILE=$2
CONTROL_FILEA=prtnrAcxiomMetadataA.ctl
CONTROL_FILEB=prtnrAcxiomMetadataB.ctl
IFS=$(echo '\012\001') # Newlines only
SQL="
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 'DELETE PARTNERDBA.ACXIOM_METADATA_B;'
ELSE 'DELETE PARTNERDBA.ACXIOM_METADATA_A;'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B'
ELSE 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_A'
END text
FROM all_synonyms
WHERE synonym_name = 'ACXIOM_METADATA';
"
for LINE in $(
sqlplus -s /nolog <<EOF
${SQL}
EOF)
do
case ${LINE} in
DELETE*|CREATE*)
sqlplus -s /nolog <<!
connect ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
${LINE}
!
;;
prtnrLoadMetadata*) exec ${LINE} ;;
esac
done
Check the syntax; it's fairly simple to find the bugs at this point :rolleyes:
1) You changed:
THEN 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE 'prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
to:
THEN '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
therefore, you must change the KSH expression that you are looking for:
case
...
$PARTNER_DB/prtnrLoadMetadata*) exec ${LINE} ;;
...
esac
or
case
...
*prtnrLoadMetadata*) exec ${LINE} ;;
...
esac
2) We both simply missed the missing semicolon here:
THEN 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B;' <===
ELSE 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_A;' <===
Conclusions:
- Korn shell can't guess where all your strings are changing. Make sure that your case statement can actually match the patter returning from sqlplus
- SQL must be valid SQL. Check those semicolons.
Thank you very much for the conclusions....I am still learning a whole lot of it...
I have also changed it as per your suggestions...But I think I am going wrong somewhere....It is not recognizing the shell script inside the SQL.
Connected.
0 rows deleted.
prtnrSynonym.ksh[57]: /partnerdb/bin/prtnrLoadMetadata.ksh dataload prtnrAcxiomMetadataA.: not found
I tried both ways....
*prtnrLoadMetadata* and $PARTNER_DB/prtnrLoadMetadata*
USERID=$1
DATA_FILE=$2
CONTROL_FILEA=prtnrAcxiomMetadataA.ctl
CONTROL_FILEB=prtnrAcxiomMetadataB.ctl
IFS=$(echo '\012\001') # Newlines only
SQL="
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 'DELETE PARTNERDBA.ACXIOM_METADATA_B;'
ELSE 'DELETE PARTNERDBA.ACXIOM_METADATA_A;'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEB} ${DATA_FILE}'
ELSE '$PARTNER_DB/prtnrLoadMetadata.ksh ${USERID} ${CONTROL_FILEA} ${DATA_FILE}'
END text
,CASE WHEN table_name = 'ACXIOM_METADATA_A'
THEN 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_B;'
ELSE 'CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_METADATA FOR ACXIOM_METADATA_A;'
END text
FROM all_synonyms
WHERE synonym_name = 'ACXIOM_METADATA';
"
for LINE in $(
sqlplus -s /nolog <<EOF
${SQL}
EOF)
do
case ${LINE} in
DELETE*|CREATE*)
sqlplus -s /nolog <<!
connect ${DB_USER}/${DB_PASSWORD}@${DB_NAME}
${LINE}
!
;;
$PARTNER_DB/prtnrLoadMetadata*) exec ${LINE} ;;
esac
done
Sorry, my bad again!
Your line is getting chopped from sqlplus so change:
SET FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 TRIMSPOOL ON
to:
SET FEEDBACK OFF VERIFY OFF ECHO OFF PAGES 0 TRIMSPOOL ON LINES 300
This gives you enough space for your path and filename. Add more if 300 character isn't enough.
I also changed this:
$PARTNER_DB/prtnrLoadMetadata*) exec ${LINE} ;;
to:
$PARTNER_DB/prtnrLoadMetadata*) eval ${LINE} ;;
Thank You Thomas....
It is working fine....I need to run this and test this on real tables and see how it goes..
I really appreciate your time!
Thomas....
All these days I was trying to do this and with your code I was able to execute successfully...
When the code review happened, they want to pass the table name as a parameter and told me to code it this way...I tried to put pieces together and couldn't go beyond this point..
I think this opens and closes connections. Is there any better way to do this?
#!/usr/bin/ksh
###################################################################################
SetupEnvironment () {
ConnectionParameter="dataload/dataload@dadb"
}
###################################################################################
GetWorkingTableName() {
sqlplus -silent /nolog @getAcxTableName.sql $ConnectionParameter 1.tmp >/dev/null
status=$?
if [[ $status -ne 0 ]]
then
print "Error from sql script getAcxTableName.sql"
exit 1
fi
grep ACX 1.tmp | read tempval
print "$tempval"
}
###################################################################################
Truncate_Table () {
sqlplus $ConnectionParameter <<!
whenever sqlerror exit failure
set serveroutput on size 1000000
exec p_truncate("$1","$2");
exit;
!
print "Error from sql"
}
###################################################################################
RunTheETLJob () {
print "dummy"
}
###################################################################################
SwitchSynonym () {
CREATE OR REPLACE PUBLIC SYNONYM ACXIOM_PROSPECT FOR $WorkTableName
print "dummy"
}
################ M A I N P R O G R A M ##########################################
SetupEnvironment
GetWorkingTableName|read WorkTableName
print "Tablename is $WorkTableName"
#exit 0
Truncate_Table $WorkTableName
export USER_VAR_0=$WorkTableName
print "Truncating table $WorkTableName"
RunTheETLJob
SwitchSynonym $WorkTableName
print "Switched synonym to $WorkTableName
exit 0;
I would really appreciate any help on this...
What do you mean that it opens and closes connections?
Thomas,
I think I am opening and closing connections in every function. I am not sure of this too...I am not sure if this is a good way to code. I am learning everyday...
I could put all these pieces together after all these days..I want to pass the table name as a parameter during runtime which was received from the getAcxTableName.sql
whenever sqlerror exit failure
set feed off
set echo off
set pages 0
set trimspool on
connect &1
spool &2
select decode(table_name,'ACXIOM_PROSPECT_B','ACXIOM_PROSPECT_A','ACXIOM_PROSPECT_A','ACXIOM_PROSPECT_B','')
from all_synonyms where SYNONYM_NAME='ACXIOM_PROSPECT'
/
spool off;
exit;
I am really going crazy here!!
Yes, you are opening and closing connections every time that you run sqlplus and provide it logon credentials. An alternative technique, which, since you are struggling so much, I do not recommend is to run sqlplus as a coprocessor, which keeps one session open no matter how many times that you need to interact with Oracle. This has advantages and disadvantages and, for me, the advantanges are substantial enough that I have developed libraries of functions to seemlessly and simply interact with up to four (which is the limit for ksh88) databases via coprocessors. However it is harder to do so you will be better served using the method that you been using. You are not doing that much work and opening and closing sessions isn't costing you that much for this load operation. A coprocessor implementation, while it would be a great learning experience, is not for the beginner.
As far as question goes, I'm not sure I understand what you are asking me. Is your script working or not? If not, what's wrong? If you want to combine all of your sqlplus calls then you certainly can but it's going to require more advanced sqlplus techniques or to do everything in PL/SQL. At any rate, it the script works, and, if performance isn't an issue (which, judging by the script simply can't possibly be the case), and, if you have plenty of other work to do, leave it alone and move on. When you get better and scripting, you can make changes then.
By the way, one comment regarding your script. You are logging on two different ways, once with the logon credentials as sqlplus parameters and once inside the here document. I would stick to the second method since it keeps your database password from being viewed by other Unix users.