Link to home
Start Free TrialLog in
Avatar of akp007
akp007Flag for Afghanistan

asked on

Want to capture the Values from SQL inside a Shell and Run other SQLs

experts -
    I would like to have your help. I want to ge the values from a sql statement inside a shell program and assign that value in the where clause of an other sql and continue execute the shell

regards.. Kind of urgent
Avatar of joebednarz
joebednarz
Flag of United States of America image

Try this... it never leaves SQL...

your_first_script.sql
=================

SQL> COL dummy_col NEW_VALUE return_value
SQL> SELECT col1 AS dummy_col FROM your_table;
SQL> @ your_other_script return_value

your_other_script.sql
==================
SQL> SELECT something FROM another_table WHERE id = &1


Explanation:

1) "COL dummy_col" is checking for output in the column with this alias name
2) "NEW_VALUE return_value" assigns any value returned in the column "dummy_col" to a new variable called "return_value"... unlike "dummy_col", "return_value" is set in the environment and retains value
3) "SELECT..." returns "col1" into "dummy_col" which in turn sets variable "return_value"
4) "@ your_other_script return_value" executes your other script while passing in the obtained value.
Avatar of akp007

ASKER

thanks. But this is all can be achieved with in the shell program

regards
So, are you saying the results of the SQL query will be supplied to something other part of the shell script or just used in another SQL script?
Avatar of akp007

ASKER

What I have is I am getting a performance issue. I have the whole sql inside a shell script. Now I have divided that into 2 SQLs. So I get the output from the first sql. I want to use that in the second sql in the shell program.  
thanks
ASKER CERTIFIED SOLUTION
Avatar of joebednarz
joebednarz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
BTW, if the WHERE clause on the second SQL statement is a literal (i.e., character based) column, you may have to change it to something like this;

WHERE id = '$list'

but I've not tested this, so you'll have to experiment.
Avatar of akp007

ASKER

That's exaclty what I am looking for. But I tried to experiment it it's not working. Any other suggestions
regards
How is it not working?  An error code possibly? or not working as expected?  Could you give an example...
Avatar of akp007

ASKER

It is trying to take the output from the first sql. But the problem is when the first script executes, it gives more that we need .. Means we get the values we are looking for and also the entire sql in text . I am getting the following error generated. So I would like to if the sql out put can be written to file and read that file from the second sql. if that is the best way. Help is appreciated .. regards
SQL>   2    3    4    5  SQL> SP2-0734: unknown command beginning "Copyright ..." - rest of line ignored.
SQL> SQL> SQL> SP2-0734: unknown command beginning "Connected ..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "Oracle Dat..." - rest of line ignored.
SQL>   2  SQL> SP2-0734: unknown command beginning "SQL> SQL> ..." - rest of line ignored.
SQL> SP2-0226: Invalid line number
SQL> SP2-0734: unknown command beginning "SQL> Disco..." - rest of line ignored.
SQL>   2    3  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
Avatar of akp007

ASKER

I am getting there.. I know why, your solution is correct. I did a mistake , I forgot -s option. working on it

thanks
Avatar of akp007

ASKER

Perfect approach
Best solution is the one you are able to find on your own... good job!