Oracle Query results to be stored in variables Post: 302177689

Sponsored Content

Top Forums Shell Programming and Scripting Oracle Query results to be stored in variables Post 302177689 by ashokjaysiv on Saturday 22nd of March 2008 06:46:23 PM

03-22-2008

Registered User

Oracle Query results to be stored in variables

Hi

I would like to know if there is a way to just have one ORACLE connection established, using which we can execute different queries and store the results under different variables.

For e.g the following uses to two silent ORACLE connection to store the result under two different variables. Instead can we just be able to modify the code to use the same connection against multiple queries?

#!/bin/bash
Query1=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select name from success; --Just for e.g.
EXIT;
eof`
....Operation on $Query1

Query2=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select name from failure; --Just for e.g.
EXIT;
eof`
....Operation on $Query2

The reason for asking is that the same code is required to be repeated multiple times.

Also, can you pls explain how to read the results stored in the variable line by line.

For e.g.

if Query1 results are:

int1
int4
int5

I would like to be able to get int1 value first, get the second one int4 and get the last one int5. Just would like to know how to perform a loop and read each line.

Thanks for your help.
Ashok

ashokjaysiv

View Public Profile for ashokjaysiv

Find all posts by ashokjaysiv

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Oracle stored procedure.

I am using sqlplus. I have the stored procedure name. How can i print the stored procedure content?

2. UNIX for Advanced & Expert Users

Environmental Variables - where stored ?

Hi all ! Yesterday I defined an environmental variable PATH, but today when I restarted machine, I could not see that it was stored any place. Is there any file where I could save the settings ? I have quite a few env.variables defined, so I need a smarter way to define. regards D

3. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off...

4. Shell Programming and Scripting

Oracle Query results to be stored in variables using unix

I want to store the sql query output into a variable #!/bin/ksh ORACLE_SID=DB01; export ORACLE_SID; export FILE_PATH=/home/asg/Tmp # Order Checking echo " removing old files " rm $FILE_PATH/Malformed_Order.txt echo " Enter the Malformed Order ....!" read orders echo "Regrade...

5. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the...

6. Shell Programming and Scripting

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at...

7. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ...

8. Shell Programming and Scripting

Multiple Query Results to Variables

Hello, I am very new to shell scripting and I am not sure of how best to handle the following scenario. I need to query a list of values from a table. I need to store those results and use them to selectively delete values in yet another table in a separate database. I do know how to store the...

9. Shell Programming and Scripting

How to Assign SQL Query Results to Variables in Linux?

Hi, I am new to linux... How to Assign SQL Query Results to Variables in Linux,i want ti generate it in param files, Can anyone please explain me. Ex: SQL> Select * from EMP; O/P: Emp_No Emp_Name 1 AAA 2 BBB 3 CCC and I want expected...

10. Shell Programming and Scripting

Assigning multiple column's value from Oracle query to multiple variables in UNIX

Hi All, I need to read values of 10 columns from oracle query and assign the same to 10 unix variables. The query will return only one record(row). I tried to append all these columns using a delimiter(;) in the select query and assign the same to a single variable(V) in unix. I thought I...

LEARN ABOUT DEBIAN

myloader

MYLOADER(1)							     mydumper							       MYLOADER(1)

NAME

       myloader - multi-threaded MySQL loader

SYNOPSIS

       myloader --directory = /path/to/mydumper/backup [OPTIONS]

DESCRIPTION

       myloader is a tool used for multi-threaded restoration of mydumper backups.

OPTIONS

       The myloader tool has several available options:

       --help Show help text

       --host, -h
	      Hostname of MySQL server to connect to (default localhost)

       --user, -u
	      MySQL username with the correct privileges to execute the restoration

       --password, -p
	      The corresponding password for the MySQL user

       --port, -P
	      The port for the MySQL connection.

       Note   For localhost TCP connections use 127.0.0.1 for --host.

       --socket, -S
	      The UNIX domain socket file to use for the connection

       --threads, -t
	      The number of threads to use for restoring data, default is 4

       --version, -V
	      Show the program version and exit

       --compress-protocol, -C
	      Use client protocol compression for connections to the MySQL server

       --directory, -d
	      The directory of the mydumper backup to restore

       --database, -B
	      An alternative database to load the dump into

       Note   For  use with single database dumps.  When using with multi-database dumps that have duplicate table names in more than one database
	      it may cause errors.  Alternatively this scenario may give unpredictable results with --overwrite-tables.

       --queries-per-transaction, -q
	      Number of INSERT queries to execute per transaction during restore, default is 1000.

       --overwrite-tables, -o
	      Drop any existing tables when restoring schemas

       --enable-binlog, -e
	      Log the data loading in the MySQL binary log if enabled (off by default)

       --verbose, -v
	      The verbosity of messages.  0 = silent, 1 = errors, 2 = warnings, 3 = info.  Default is 2.

AUTHOR

       Andrew Hutchings

COPYRIGHT

       2011, Andrew Hutchings

0.5.1								   June 09, 2012						       MYLOADER(1)

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Oracle stored procedure.

Discussion started by: kamil

2. UNIX for Advanced & Expert Users

Environmental Variables - where stored ?

Discussion started by: DGoubine

3. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Discussion started by: pranavagarwal

4. Shell Programming and Scripting

Oracle Query results to be stored in variables using unix

Discussion started by: Nareshp