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}
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
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...
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?
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.
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.
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}
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)