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 302076070 by madhunk on Thursday 8th of June 2006 01:54:31 PM
Old 06-08-2006
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}

 

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

xinetd.log

XINETD.LOG(5)							File Formats Manual						     XINETD.LOG(5)

NAME

       xinetd.log - xinetd service log format

DESCRIPTION

       A  service configuration may specify various degrees of logging when attempts are made to access the service. When logging for a service is
       enabled, xinetd will generate one-line log entries which have the following format (all entries have a timestamp as a prefix):

	      entry: service-id data

       The data depends on the entry.  Possible entry types include:

	      START	  generated when a server is started

	      EXIT	  generated when a server exits

	      FAIL	  generated when it is not possible to start a server

	      USERID	  generated if the USERID log option is used.

	      NOID	  generated if the USERID log option is used, and the IDONLY service flag is used, and the remote end  does  not  identify
			  who is trying to access the service.

       In the following, the information enclosed in brackets appears if the appropriate log option is used.

       A START entry has the format:

	      START: service-id [pid=%d] [from=%d.%d.%d.%d]

       An EXIT entry has the format:

	      EXIT: service-id [type=%d] [pid=%d] [duration=%d(sec)]

       type can be either status or signal.  The number is either the exit status or the signal that caused process termination.

       A FAIL entry has the format:

	      FAIL: service-id reason [from=%d.%d.%d.%d]

       Possible reasons are:

	      fork	     a certain number of consecutive fork attempts failed (this number is a configurable parameter)

	      time	     the time check failed

	      address	     the address check failed

	      service_limit  the allowed number of server instances for this service would be exceeded

	      process_limit  a limit on the number of forked processes was specified and it would be exceeded

       A DATA entry has the format:

	      DATA: service-id data

       The data logged depends on the service.

	      login	  remote_user=%s local_user=%s tty=%s

	      exec	  remote_user=%s verify=status command=%s
			  Possible status values:

			  ok	    the password was correct

			  failed    the password was incorrect

			  baduser   no such user

	      shell	  remote_user=%s local_user=%s command=%s

	      finger	  received string or EMPTY-LINE

       A USERID entry has the format:

	      USERID: service-id text

       The text is the response of the identification daemon at the remote end excluding the port numbers (which are included in the response).

       A NOID entry has the format:

	      NOID: service-id IP-address reason

SEE ALSO

       xinetd(1L), xinetd.conf(5)

								   28 April 1993						     XINETD.LOG(5)
All times are GMT -4. The time now is 12:12 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy