Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Calling SQL scripts through Shell Script
Top Forums Shell Programming and Scripting Calling SQL scripts through Shell Script Post 302075947 by madhunk on Wednesday 7th of June 2006 05:44:42 PM
Old 06-07-2006
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}

 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT DEBIAN

sql::reservedwords::oracle

SQL::ReservedWords::Oracle(3pm) 			User Contributed Perl Documentation			   SQL::ReservedWords::Oracle(3pm)

NAME

       SQL::ReservedWords::Oracle - Reserved SQL words by Oracle

SYNOPSIS

	  if ( SQL::ReservedWords::Oracle->is_reserved( $word ) ) {
	      print "$word is a reserved Oracle word!";
	  }

DESCRIPTION

       Determine if words are reserved by Oracle Database.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either Oracle7, Oracle8i, Oracle9i or Oracle10g.

       is_reserved_by_oracle7( $word )
	   Returns a boolean indicating if $word is reserved by Oracle7.

       is_reserved_by_oracle8( $word )
	   Returns a boolean indicating if $word is reserved by Oracle8i.

       is_reserved_by_oracle9( $word )
	   Returns a boolean indicating if $word is reserved by Oracle9i.

       is_reserved_by_oracle10( $word )
	   Returns a boolean indicating if $word is reserved by Oracle10g.

       reserved_by( $word )
	   Returns a list with Oracle versions that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_oracle7
       is_reserved_by_oracle8
       is_reserved_by_oracle9
       is_reserved_by_oracle10
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords

       <http://www.oracle.com/technology/documentation/>

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28					   SQL::ReservedWords::Oracle(3pm)
All times are GMT -4. The time now is 07:16 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy