In shell, I don't know a way to keep an Oracle connection opened (and I don't think there is a way, sqlplus does not support it!).
By the way, if you want this, why the first piece of code I wrote is not valid to you? It executes all queries in the FileName array sequentially, in the same connection!
If you really need to open the connection once, execute the queries and then close the connection, I suggest you to use another programming language.
My suggestion is: Python and its lib to connect to Oracle: cx_Oracle
Sorry but it is not clear to me at which point in the first code you are opening the connection.
Kindly repeat it marking the line for opening the connection.
...
I have a system that is throwing number of id's in files where each file has more than one line and each line has one id
right now i have a code to cat the files each 5 minute and pass each file content to the given array FileName -the array in my code is just sample for the dynamic output and it is not static like shown- and i do shell loop to do the following
select the next value from shell array using the loop number as index
open sql connection
run query using sqlplus bind variable and query about the given value from shell array
close the connection
all what i'm looking for is to keep the whole logic but loop after opening the sqlplus because this change will offload the oracle resources somehow as opening connection is considered a high cost action for resources
...
You could loop through the array, generate all SQL statements, assign that to a shell variable and then use that variable in the sqlplus connection.
Thus, you move the task of generating your statements to Unix and open only one connection to Oracle when you're ready with your batch of queries.
Code:
$$$ cat -n oraconnect.sh 1 #!/usr/bin/bash 2 3 # Let's say the array has been populated at this point 4 # Over here, I've populated the empno values from the EMP 5 # demonstration table of the SCOTT sample schema of Oracle 6 id_array[1]=7369 7 id_array[2]=7839 8 id_array[3]=7900 9 id_array[4]=7654 10 11 # Generate all SQL statements by looping through the array 12 # and assign that to a single shell variable 13 SQLSTR=$(for i in "${id_array[@]}"; do 14 echo "SELECT ename from emp where empno = $i;"'\n' 15 done) 16 17 # now connect to Oracle and execute the SQL statements in 18 # the string variable 19 sqlplus -s /nolog <<EOF 20 connect user/password@db 21 set pages 0 heading off time off timing off feed off 22 $(echo -e $SQLSTR) 23 exit 24 EOF 25$$$ ./oraconnect.shSMITHKINGJAMESMARTIN$$$
Now for the part that says that the array is dynamic and is actually being created by reading text files. In that case, you do not need an array.
You loop through the files, read the ids, generate the queries all the while appending them, assign the value to a Unix variable and then use it while interacting with Oracle.
Code:
$$$ # The Ids are in the files - file1 and file2$$ cat file173697839$$ cat file279007654$$$ cat file1 file27369783979007654$$$ cat -n oraconnect1.sh 1 #!/usr/bin/bash 2 3 # If the Ids are being fetched from files that have one Id 4 # per line, then an array is not really needed. 5 SQLSTR=$(cat file? | while read i; do 6 echo "SELECT ename from emp where empno = $i;"'\n' 7 done) 8 9 # now connect to Oracle and execute the SQL statements in 10 # the string variable 11 sqlplus -s /nolog <<EOF 12 connect user/password@db 13 set pages 0 heading off time off timing off feed off 14 $(echo -e $SQLSTR) 15 exit 16 EOF 17$$$ ./oraconnect1.shSMITHKINGJAMESMARTIN$$$
tyler_durden
---------- Post updated at 11:51 AM ---------- Previous update was at 11:28 AM ----------
In fact, depending on what your queries are and how you want to use their output, you might be able to use the IN operator, like so -
Code:
$$ # For the text files - "file1" and "file2" posted earlier...$$ cat -n oraconnect2.sh 1 #!/usr/bin/bash 2 3 # If the Ids are being fetched from files that have one Id 4 # per line, then an array is not really needed. 5 SQLSTR=$(cat file? | 6 xargs printf "%s, " | 7 sed 's/^\(.*\), $/SELECT ename FROM emp WHERE empno IN (\1);/') 8 9 echo "My query is: $SQLSTR" 10 11 # now connect to Oracle and execute the SQL statements in 12 # the string variable 13 sqlplus -s /nolog <<EOF 14 connect user/password@db 15 set pages 0 heading off time off timing off feed off 16 $(echo -e $SQLSTR) 17 exit 18 EOF 19$$$ ./oraconnect2.shMy query is: SELECT ename FROM emp WHERE empno IN (7369, 7839, 7900, 7654);SMITHMARTINKINGJAMES$$
tyler_durden
Last edited by durden_tyler; 12-08-2011 at 06:13 PM..
Hello,
I have file (PARFILE) with string on first line:
INCLUDE=SCHEMA:"IN\( 'SCHEMA1','SCHEMA2','SCHEMA3' \)"In .sh script I use:
....
IMPORT_SCHEMA=`awk 'NR==1{print $2}' ${PARFILE}`
...print $2 is because 'SCHEMA1','SCHEMA2','SCHEMA3' is 2nd column in file
echo "$IMPORT_SCHEMA"... (5 Replies)
Apologies if this is the incorrect forum.There is an issue in the function call I am facing while calling the same from a unix shell scripts.
Basically, I want the ref cursor to return values to a variable in sqlpus. The function call is currently saved in a ".txt" file
in a unix location. I want... (7 Replies)
Hi ,
I am using below code :
for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
ORACLE_SID=$i
export ORACLE_SID;
dest=`sqlplus "/ as sysdba" <<EOF
set heading off feedback on verify off
select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Hi All,
I'm new to Linux and scripting, apologies in advance for 'stupid' questions.
Please help... Im writing a script that calls a sqlplus script but the sqlplus requires inputs and i cant seem to get this to work.
here is my code.
#!/bin/sh
TERM=vt100
export TERM... (4 Replies)
Hello All,
I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible?
Thanks (4 Replies)
Hi there,
I am trying to call a shell script from a Perl script. here is the code:
@args = ("sh", "someshellprg.sh", "a file handler", "an array");
system(@args) == 0
or die "system @args failed: $?";
in the shell program, I examine if the arguments exits using:
if
then echo... (5 Replies)
Hi,
I am writing a script which creates an external table using a shell script. My requirement is like this.
Usage: . ./r.ksh <table_name> - this should create an external table.
e.g . ./r.ksh abc - this should create an external table as abc_external.
How do i achieve this? Please... (5 Replies)
hi,
I am using a shell script from where i will be conecting to sqlplus..
i am having a problem in passing a variable to sqlplus query..
i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Hi,
I want to input unix variable to sqlplus.The following is working fine
sqlplus username/password @dummy.sql param1 param2 << EOF
create user $1 identified by $2;
EOF
But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that??
Thanks... (3 Replies)
Hi ,
I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script
Right now no file is generated on the unix box...it is a... (2 Replies)