05-26-2016
You can do this in a few ways.
- You can call sqlplus and pass it a deck/file of multiple SQL commands.
- You can call sqlplus in what is called a here document and pass in the commands from your script
- Probably others......
How much SQL code are you planning to pass in, and will it be static or based on variables in the main script?
Robin
This User Gave Thanks to rbatte1 For This Post:
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible.
List of queries are stored in a file. For example, I have to run a query like this:
Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies
2. Shell Programming and Scripting
Hi,
When i execute the below statement , the value is not getting stored in the variable.
AnneeExercice=`sqlplus $LOGSQL/$PASSWORDSQL << FIN >> $GEMOLOG/gemo_reprev_reel_data_ventil_$filiale.trc
SELECT bi09exercice
FROM bi09_scenario
WHERE bi09idfiliale=UPPER('de')
AND ... (1 Reply)
Discussion started by: krishna_gnv
1 Replies
3. Shell Programming and Scripting
Hi,
I need your help in sedning sql queries output to different excel sheets.
My requirement is like this:
Query1: Select name from table1 where status = 'Complete'
Query2: Select name from table1 where status = 'Failed'
Query3: Select name from table1 where status = 'Ignored'
... (4 Replies)
Discussion started by: parvathi_rd
4 Replies
4. Shell Programming and Scripting
i have to do a check in my UNIX script to see whats saved in the database. depending on whats there will fork data to certain functions. However i do not know how to capture SQL output into a UNIX variable.
Below is what i have tried, but i get an error:
Error 3706 Failure 3706 Syntax error:... (3 Replies)
Discussion started by: purplebirky
3 Replies
5. Shell Programming and Scripting
Hi,
I want to write the shell script to change multple file name (the file name is get from DB)
e.g. cp db1.txt file1_new.txt
cp db2.txt file2_new.txt
cp db3.txt file3_new.txt
I have write the script like this:
VAR=`sqlplus -s $LOGON @<<ENDOFTEXT
set termout off
... (0 Replies)
Discussion started by: jackyntk
0 Replies
6. Shell Programming and Scripting
In c:/zaheer/123-456/sql/ folder i have below 7 sql files.
cv_abc_a.sql
cv_abc_b.sql
cv_abc_c.sql
vtemp_d.sql
vtemp_e.sql
cv_abc_f.sql
Exviews.sql
In Exviews.sql file i have mentioned all cv and vtemp files to run like
start ${SQLDIR}/cv_abc_a.sql
start ${SQLDIR}/cv_abc_b.sql... (1 Reply)
Discussion started by: Zaheer.mic
1 Replies
7. Shell Programming and Scripting
Hi!
I would like to do a single connection to sqlplus and execute some querys.
Actually I do for every query one connection to database
i.e
echo 'select STATUS from v$instance;
exit' > $SQL_FILE
sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT
echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies
8. Shell Programming and Scripting
Hi,
I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else.
Thanks in advance..
Cheers (6 Replies)
Discussion started by: gonchusirsa
6 Replies
9. Shell Programming and Scripting
Dear All,
I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File.
The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files.
Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies
10. Shell Programming and Scripting
Hi All,
I have a file queries.txt as follows :
SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2;
SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2;
SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2;
SELECT... (2 Replies)
Discussion started by: SriRamKrish
2 Replies
LEARN ABOUT PHP
oci_set_client_info
OCI_SET_CLIENT_INFO(3) OCI_SET_CLIENT_INFO(3)
oci_set_client_info - Sets the client information
SYNOPSIS
bool oci_set_client_info (resource $connection, string $client_info)
DESCRIPTION
Sets the client information for Oracle tracing.
The client information is registered with the database when the next 'roundtrip' from PHP to the database occurs, typically when an SQL
statement is executed.
The client information can subsequently be queried from database administration views such as V$SESSION.
The value may be retained across persistent connections.
PARAMETERS
o $connection
-An Oracle connection identifier, returned by oci_connect(3), oci_pconnect(3), or oci_new_connect(3).
o $client_info
- User chosen string up to 64 bytes long.
RETURN VALUES
Returns TRUE on success or FALSE on failure.
EXAMPLES
Example #1
Setting the client information
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
// Record the client information
oci_set_client_info($c, 'My Application Version 2');
// Code that causes a roundtrip, for example a query:
$s = oci_parse($c, 'select * from dual');
oci_execute($s);
oci_fetch_all($s, $res);
sleep(30);
?>
// While the script is running, the administrator can see the client
// information:
sqlplus system/welcome
SQL> select client_info from v$session;
NOTES
Note
Oracle version requirement
This function is available when PHP is linked with Oracle Database libraries from version 10 g onwards.
Tip
Performance
With older versions of OCI8 or the Oracle Database, the client information can be set using the Oracle DBMS_APPLICATION_INFO pack-
age. This is less efficient than using oci_set_client_info(3).
Caution
Roundtrip Gotcha
Some but not all OCI8 functions cause roundtrips. Roundtrips to the database may not occur with queries when result caching is
enabled.
SEE ALSO
oci_set_module_name(3), oci_set_action(3), oci_set_client_identifier(3).
PHP Documentation Group OCI_SET_CLIENT_INFO(3)