akp007
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
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
ASKER
thanks. But this is all can be achieved with in the shell program
regards
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?
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
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
WHERE id = '$list'
but I've not tested this, so you'll have to experiment.
ASKER
That's exaclty what I am looking for. But I tried to experiment it it's not working. Any other suggestions
regards
regards
How is it not working? An error code possibly? or not working as expected? Could you give an example...
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
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
ASKER
I am getting there.. I know why, your solution is correct. I did a mistake , I forgot -s option. working on it
thanks
thanks
ASKER
Perfect approach
Best solution is the one you are able to find on your own... good job!
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.