Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

calling a sql file in my shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting calling a sql file in my shell script
# 1  
Old 05-25-2010
Error calling a sql file in my shell script
Hi,

I want to call a sql file in my shell script. see the below code:-

Code:
 if [[ "${MODE}"="FULL" ]]
 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 XYZ.sql 
       go
       ENDSQL
 )

if the mode =full, then connect via using isql (command) with given paramters as (server_user, server_id, password).

xyz.sql file contains like 20sql commands in it. i am not sure how will i call that file in here. {i want to call the file because i am using that same file in different calls also, thats why not pasting sql commands here}

please help.. ??
# 2  
Old 05-25-2010
Check for -i switch of isql

Also,

Code:
if [[ "${MODE}"="FULL" ]]

should be

Code:
if [[ "${MODE}" = "FULL" ]]

# 3  
Old 05-25-2010
as u said the mode is proper only. i checked it again.

Code:
if [[ "${MODE}" = "FULL" ]]

btw can you tell me what do you mean by isql -i ?

when i am compiling the code i am getting error
Code:
isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://"

error - "<< unexpected" why its not taking the endsql ??? or throwing a error here
# 4  
Old 05-25-2010
ending ENDSQL MUST be in the beginning of the line.

I understood, you wish to take queries from an input file instead writing.
that's why I suggested -i option.

from the sybase man page ( I assume you using sybase databse ?)

Code:
-i inputfile

    specifies the name of the operating system file to use for input to isql. The file must contain command terminators (“go” is the default).

        *

          Specifying the parameter as follows is equivalent to < inputfile:

          -i inputfile
        *

          If you use -i and do not specify your password on the command line, isql prompts you for it.
        *

          If you use < inputfile and do not specify your password on the command line, you must specify your password as the first line of the input file.

Last edited by clx; 05-25-2010 at 04:37 AM.. Reason: some typo
This User Gave Thanks to clx For This Post:
dazdseg
# 5  
Old 05-25-2010
thanks a ton for the answer and sorry for late reply.

Yes i am using sybase db. i understood the -i option and started using it also. about the endsql i am trying it out right now

Code:
 << FINSQL isql -U${S_USER} -S${S_SERV} -D${S_DB} -b -h0 -w1000 -i${MYDIR}/abc.sql | sed -e "s/Password://"

is this what ru suggesting. ?

---------- Post updated at 04:15 AM ---------- Previous update was at 04:11 AM ----------

i tried the upper code.

Code:
<< FINSQL isql -U${S_USER} -S${S_SERV} -D${S_DB} -b -h0 -w1000 -i${MYDIR}/abc.sql | sed -e "s/Password://"


but its still throwing me the same error.
ERROR - - " ./mine.ksh[6]: syntax error at line 13 : `<<' unmatched"

see the exact code below :-

Code:
MYDIR=/home/cadessad/bin

Retrieve_Date()
{
  << FINSQL isql -U${S_USER} -S${S_SERV} -D${S_DB} -b -h0 -w1000 -i${MYDIR}/abc.sql | sed -e "s/Password://"

i m just trying to configure this thing properly but unable to do it properly
# 6  
Old 05-25-2010
Quote:
Originally Posted by dazdseg
thanks a ton for the answer and sorry for late reply.

Yes i am using sybase db. i understood the -i option and started using it also. about the endsql i am trying it out right now

Code:
 << FINSQL isql -U${S_USER} -S${S_SERV} -D${S_DB} -b -h0 -w1000 -i${MYDIR}/abc.sql | sed -e "s/Password://"

is this what ru suggesting. ?
No, I was talking about the ending label. ( the second one)


Code:
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 XYZ.sql 
       go



ENDSQL ## this MUST be in the beginning of the line. no space or tabs before it

EDIT: Label name doesn't matters. It can be anything. SQL, sql,hi or even your name
This User Gave Thanks to clx For This Post:
dazdseg
# 7  
Old 05-25-2010
hey anchal thanks a lot. i tried putting finsql at the beginning of the line and its working properly.

i tried running again and i am getting anther error. (errors love me.. Smilie ).

got any ideas to solve this error. see the below piece of code.

Code:
        isql -U${S_USER} -S${S_SERV} -D${S_DB} -b -h0 -i${MYDIR}/abc.sql <<FINSQL | sed -e "s/Password://"
     ${S_PWD}

when this code is executed .. below is the command for the execution of the script.
Code:
]./mine.ksh -S 3 -M FULL >>log.txt

(mine.ksh is my shell script), it has 2 modes S and M. in the log.txt file the error i m facing is ..
Code:
 CT-LIBRARY error:   ^?}^]: user api layer: external error: A data length of 85 exceeds the maximum length allowed for password data.

i tried using the isql command with -w1000 option also bt i was getting the same error. any heads up on this.?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

9. Shell Programming and Scripting

Problem with Calling sql file from shell script

I have created abc.sh file which will set the environment variables (UNIX env variables as well as ORACLE required variables like ORACLE_SID,ORACLE_HOME etc) and then calls a function file which checks for starts some logs and then it will try to execute the .sql file. The .sh, function file are as... (1 Reply)
Discussion started by: sskc
1 Replies

10. Shell Programming and Scripting

Calling SQL scripts through Shell Script

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.... (18 Replies)
Discussion started by: madhunk
18 Replies
Login or Register to Ask a Question