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
# 8  
Old 05-25-2010
Can you try this?

Code:
isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b -h0 -i${MYDIR}/abc.sql << FINSQL

And remove the sed part now
# 9  
Old 05-25-2010
i used the command as u said but i am still unable to connect to the database.

i am not sure if this is the code error or configuration error.

btw i am executing a sql query like this --

Code:
select distinct A.account_no into #sun

am i using the temp. variable #sun properly or there is somthing else i need to use with it.
# 10  
Old 05-25-2010
what error you are getting now?
also, there must be some table name after "into" i guess.
#sun is a wrong interpretation.
# 11  
Old 05-25-2010
yippe. (thanks a ton) . i figured out hw to call a file finally. bt hw do i interpret this data in the fields required by me is the next step. SmilieSmilie..

Code:
select account_no into tempdb..sun from ABC
go

i am selecting account_no into tempdb creating sun table and copying data from ABC. (this is working fine, i mean i am able to create the table)Smilie.

and the isql command i am running the one which u told me:-
Code:
Retrieve_Date()
{
if [[ "${MODE}" = "FULL" ]]
then
(
isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b -h0 -i${MYDIR}/abc.sql << FINSQL
go
FINSQL
) > XYZ.txt
fi

I wanted to know how can i get the result of the sql query performed in abc.sql into a variable in my shell script. so that i can use those records. i am checking the database and i am seeing a temp. table being created but unable to get those records in my script. any ideas??
# 12  
Old 05-25-2010
if you just want to output to a variable, you don't actually need here doc ( the labels method).

try something like:



Code:
Retrieve_Date()
{
if [[ "${MODE}" = "FULL" ]];then
 myvar=$(isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b -h0 -i${MYDIR}/abc.sql)
fi
}


You must have "go" statement after each query in your ${MYDIR}/abc.sql file
# 13  
Old 05-25-2010
hey, thanks a ton again.
actually i am pulling some data from abc.sql into temp.db. i am storing this in a table called XYZ in tempdb.

and i am using bcp tool so that i can get that data into my file.

i m using the below code :-
Code:
Retrieve_Date()
{
if [[ "${MODE}" = "FULL" ]]
then
(
isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b -h0 -i${MYDIR}/abc.sql << FINSQL
go
FINSQL
) > sun.txt
fi
(
bcp tempdb..ASH in ${MYDIR}/takeit.txt -c -t"|" -U${S_USER} -S${S_SERV} -P${S_PWD} << FINBCP
FINBCP
) > ART.txt

I want the data fetched from abc.sql (queries) to be stored in takeit.txt (i actually want it in the file takeit.txt)
so that i can take ftp file and forward it some place else.

any ideas here??? SmilieSmilie
# 14  
Old 05-25-2010
You don't even need variable if that is the case.

bcp is an stand alone utility which has command line switched for database details.
you don't need to interact with database directly. ( << thing ).

Code:
Retrieve_Date()
{
if [[ "${MODE}" = "FULL" ]];then
 isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b -h0 -i${MYDIR}/abc.sql >/dev/null 2>&1
fi
}

Code:
## call the function
Retrieve_Date 
## process bcp
bcp tempdb..ASH out ${MYDIR}/takeit.txt -c -t"|" -b 100000 -S${S_SERV} -U${S_USER} -P${S_PWD}



in your "abc.sql", you must have something like this..

Code:
select blah blah into tempdb..ASH from blah
go

Last edited by clx; 05-25-2010 at 09:52 AM..
This User Gave Thanks to clx For This Post:
dazdseg
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