Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Read SQL statement in Script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read SQL statement in Script
# 1  
Old 06-02-2011
Read SQL statement in Script
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.

Code:
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


Code:
Output:

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

Any help would be great.. Smilie
Thanks a lot...
# 2  
Old 06-03-2011
Are you looking for the sqlplus command to run the sql statement in a shell script..?
Code:
 
sqlplus -S username/password@DBSID <<EOF > output.txt
select a.empno, b.deptname from ... ;
exit;
EOF

Last edited by michaelrozar17; 06-03-2011 at 05:09 AM.. Reason: added exit statement
# 3  
Old 06-03-2011
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.


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

eg :

Code:
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  
Old 06-07-2011
PHP
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  
Old 06-07-2011
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oop to copy and rename files through SQL Statement in shell Script

#!/bin/sh sqlplus -s "/ as sysdba" << EOF SET HEADING OFF SET FEEDBACK OFF Select pt.user_concurrent_program_name , OUTFILE_NAME FROm apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where pt.concurrent_program_id = f.concurrent_program_id and pt.application_id =... (1 Reply)
Discussion started by: usman_oracle
1 Replies

2. Shell Programming and Scripting

Linux Script to copy and rename files through SQL statement

Hi, I require help to complete below requirement through Linux Script. I have a SQL query which shows two columns as output. One is Report Name and other is report path. Query return multiple rows. below is the output. Report Name Cotton Stock Report (Net Weight)- Customized Output... (3 Replies)
Discussion started by: usman_oracle
3 Replies

3. Shell Programming and Scripting

How to create SQL statement out of data using shell script?

Table TAB1 contains following example data (its a tree sitting in table data format & its driven based CHILD & PARENT column pick the RULE condition to generate the below SQL: CHILD PARENT SS MID MNM VNM RULE FLG 1 ? S1 ? ? V1 rule004 I 2 1 S1 ? ? V1 0 Z 3 1 S1 ? ? V1 1 Z ... (6 Replies)
Discussion started by: gksenthilkumar
6 Replies

4. Shell Programming and Scripting

Korn shell script - SQL statement challenges

Hi scripting experts. I have some coding challenges that I'm hoping you can help me out. I have one file#1 that contains the following sql statement that spans over multiple lines: sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id... (1 Reply)
Discussion started by: pchang
1 Replies

5. UNIX for Dummies Questions & Answers

SQL statement is not work on unix script

Hi, I have the following basic script. However, the statement (line 5) is not work. The output data is not able to set my request format a30. Any advise? :mad: echo " Column filename format a30"|sqlplus4 echo Input file list to check: read filelist for file in `cat $filelist.txt` do... (1 Reply)
Discussion started by: happyv
1 Replies

6. UNIX for Dummies Questions & Answers

unix script with SQL statement problem

Hello, I have a script to get the information from database, however, it's look like the loop is not work, can someone help? :confused: echo Input file list to check: read filelist for file in 'cat $filelist.txt' do echo "select FILENAME from FILE_TABLE where filename like '${file}'%;" >>... (9 Replies)
Discussion started by: happyv
9 Replies

7. Shell Programming and Scripting

How to call an sql script inside a while statement in KSH

Hi all, I'm trying to run an sql inside a loop which looks like this #!bin/ksh while IFS=, read var1 var2 do sqlplus -s ${USERNAME}/${PASSWORD}@${ORACLE_SID} << EOF insert into ${TABLE} ( appt_date ) values ( '${var1 }' ); ... (6 Replies)
Discussion started by: ryukishin_17
6 Replies

8. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

9. Shell Programming and Scripting

Executing a Oracle SQL statement in a UNIX script

Hi All, I need to select one column from a table based upon the passed in parameter. I tried this: sqlplus -silent $MISP_USER << EOF set feedback off; set verify off; set sqlprompt "" SELECT mail_flag FROM dailyjobs WHERE job_name = '$1'; exit 0 EOF exit... (1 Reply)
Discussion started by: ganga.dharan
1 Replies

10. UNIX for Dummies Questions & Answers

Pipe SQL select statement results to script

Hello I would like to perform a select from a oracle table and return those values to my shell script For example: site=head -1 $infile | cut -c1-15 | awk '{printf "s%", $0} sqlplus -s /nolog |& #Open pipe to sql select col1, col2, col3, col4 from oracle_table where col5 =... (6 Replies)
Discussion started by: houtakker
6 Replies
Login or Register to Ask a Question