Read SQL statement in Script

Shell Programming and Scripting
1

Hi Guys..

need some urgent help... I am stuck in something badly

I need to write a script which would read a sql statement (which might be a join/inner join/select/sub select etc. )

I need to read that sql statement ... and in the output I want all the table names and columns (doesn't matter if the columns are used in select or where or join) used in that script....

ex.

SQL:
select a.empno, b.deptname from emp a inner join dept b on a.deptno=b.deptno where a.salary in (select salary from sal where salary>1000)

(I know its a bad query, but just an example)
Please also remember that there can be several sub selects in a single select

Output:

TABLENAME 	COLUMNNAME
emp		empno
emp		deptno
emp		salary
dept		deptname
dept		deptno
sal		salary

Any help would be great.. :wink:
Thanks a lot...

2

Are you looking for the sqlplus command to run the sql statement in a shell script..?

 
sqlplus -S username/password@DBSID <<EOF > output.txt
select a.empno, b.deptname from ... ;
exit;
EOF
3

This is how it works out.
you have to open sql session by issuing proper credentials to the login db and simply execute any command , procedure vice versa.

sqlplus -s userid/password@database_name<<EOF
----call oracle statements in here
EOF

eg :

sqlplus -s scott/tiger@mydb<<EOF > error.log
select * from table_name;  --you may use any type of query with n number of joins  in here
EOF
4

Thanks for your time guys,

But thats not my requirement, my requirement was to write a shell script, which would read the sql statement as an input, and could identify TABLENAMES and itscorresponding COLUMNS in it.

I've managed to achieve 70% of functionality, but right now I am stuck in reading subqueries.

5

Can you post what ever you have tried, the best way would be function which will be called for main query and each sub query.

try to look for from, into key words which will be followed by table name.