I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this?
Is there a way to have a persistent connection to oracle database using shell script?
What you have tried so far and what is your certain condition to fetch it again ..
I have connected to database and created a temp table with some data. Now i need to run a select query from the temp table to get first 100 records, then i will run it to get the next 100 and so on until i get the entire records write them in a file.
Try with this ..
printf "Enter start range : " ; read a
printf "Enter end range : " ; read b
sqlplus -s $dbconnect <<-!
whenever sqlerror exit 1
select * from temp_table where rownum >= $a and rownum <= $b;
exit 0
!
You can have a persistent connection if your shell implements coprocesses (ksh93, zsh and bash 4 support coprocesses).
If you need something like this though, most probably the shell is not the right choice (use PL/SQL, Perl or some other high level programming language).
Why do you want to fetch n records at a time? I'm just curious ...
Thanks for the clarifications. I want to have those records in hand to generate a XML. Please let me know if i can have those records and write into file using shell script. Help me with a sample code.
Depending on your Oracle version and installed components,
you could generate XML directly in the database.
Check this.
Thanks a lot for your suggestion. I think i can generate XML directly now.. Great
---------- Post updated at 06:34 AM ---------- Previous update was at 05:59 AM ----------
Do you know how to write the XML created in oracle database in UNIX environment using shell script. is it possible to do it?
To write the generated document to a file the SQL*Plus-command spool is the easiest way. This link takes you to the Oracle-documentation describing that command.
Thanks for the link and suggestion.. You guys are awesome.