need some help in executing sql


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting need some help in executing sql
# 1  
Old 05-26-2008
need some help in executing sql

i am stuck with a problem ...
i have a shell script that gets the file name as input and performs the following operation...
it runs through a for loop inside from which i connect to sqlplus and run a procedure that creates a number of tables ..
there is no space in my server so we have made them as temporary tables ..
problem is ..... since they are temporary tables when the session expires the data in them vanishes .... but i do not want the data to vanish ...
problem in that is that without exiting from the sql server i cannot get the value of the next filename ... Smilie....

sample code :
for filename in `ls *.in`
do
log "$filename: loading"
echo "Begin" >in.sql
echo "execute immediate 'truncate table input_data1';" >>in.sql
echo "end;" >>in.sql
echo "/" >>in.sql
echo `sqlplus -s ${ORACLE_USERNAME}/${ORACLE_PASSWORD} <in.sql`
echo `exit`

echo "Begin" >in.sql
echo "loading_gtt;" >>in.sql
echo "end;" >>in.sql
echo "/" >>in.sql
echo `sqlplus -s ${ORACLE_USERNAME}/${ORACLE_PASSWORD} <in.sql`
echo `exit`
the procdure in bold is what is going to create all the temporay tables ... the value of which expire when i close the session....
pls help ...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Executing SQL's in parallel

Hi Folks, I have requirement to pull a bunch of SQL's from a table in DB and execute them in parallel and update the status of each query as and when they complete. Can you please help me with ideas on how this can be achieved? create table list_of_sql ( id number, full_sql... (3 Replies)
Discussion started by: member2014
3 Replies

2. UNIX for Beginners Questions & Answers

Help with Executing sql in Shell Script

Hello~ I have a requirement to write a shell script which will connect to the oracle database and run a select count(*) query on a table. The script should succeed only when the count returns a number greater than zero. If the count returns zero, the script should fail. Can someone please... (3 Replies)
Discussion started by: Naren.N
3 Replies

3. UNIX for Dummies Questions & Answers

Regarding executing sql query in shell script

Hi, I have one SQL file prepared in UNIX and one script that is executing that. In SQL i have Update and create queries. I want to introduce conditions in SQL file (in UNIX) that if either of the create or update query failes whole transaction should be rollback. I just have 1 create... (2 Replies)
Discussion started by: abhii
2 Replies

4. Shell Programming and Scripting

Executing sql statement from .sh file

Hi, How to execute sql statements from the .sh file ?? Means, when we run .sh file then the sql statements within it should be get executed one by one from the sqlplus With Regards (3 Replies)
Discussion started by: milink
3 Replies

5. UNIX for Advanced & Expert Users

Executing SQL file in UNIX

I have few .sql file at some location say /x/y/z. I want to execute those .sql files in UNIX server so that all packages,procedures can be applied on the database of the UNIX server. (1 Reply)
Discussion started by: Dip
1 Replies

6. Shell Programming and Scripting

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies

7. UNIX for Advanced & Expert Users

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies

8. Programming

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies

9. Shell Programming and Scripting

Script executing sql query

Hello, I have a sh script excuting a sql query through sqlplus. I am having trouble making my date equal to the date of the server time in the sql script. How can i call the server date from my query? Thanks (2 Replies)
Discussion started by: kingluke
2 Replies

10. Shell Programming and Scripting

Problems executing SQL Plus sessions

If I execute this script: $ORACLE_HOME/bin/sqlplus -s <<EOF > oracle.log $DB_id/$DB_pswd@$DB_server start test.sql EOF the sql script executes with no errors. IF I execute the following script:... (12 Replies)
Discussion started by: mh53j_fe
12 Replies
Login or Register to Ask a Question