Home » SQL & PL/SQL » SQL & PL/SQL » Parse SQL: How to extract column names, table names from a SQL query (Oracle 10g)
Parse SQL: How to extract column names, table names from a SQL query [message #364840] Wed, 10 December 2008 04:04 Go to next message
sammydude
Messages: 34
Registered: November 2007
Member
Hi all,

I have a requirement wherein a SQL query will be given in a text file.
The text file has to be read (probably using text_io package), the SQL query needs to be parsed and the column names, table names and where clauses have to be extracted and inserted into separate tables.

Is there a way to do it using PL/SQL ?
Is there a PL/SQL script available to parse and extract column names, table names etc ?

Pls help.

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364846 is a reply to message #364840] Wed, 10 December 2008 04:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and search for Example 8.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6136

Regards

Raj
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364848 is a reply to message #364840] Wed, 10 December 2008 04:35 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
Thanks for you reply Raj.

But will the dbms_sql code work for the following requirement ?

SELECT Empno, Empname, Deptno, Nvl(Sal,0)
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno

Output
------
Columns
Empno
Empname
Deptno
Nvl(Sal,0)
Tables
Emp
Dept
Where
Emp.deptno = Dept.deptno

Pls let me know.

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364849 is a reply to message #364848] Wed, 10 December 2008 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it will.
Search for print_table and you will have an example of how to do it.

Regards
Michel
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364875 is a reply to message #364840] Wed, 10 December 2008 05:48 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Quote:
But will the dbms_sql code work for the following requirement ?


Dbms_sql hasn't been designed to implement these features.

DBMS_SQL is just an interface to submit instruction to the SQL engine and to fetch rows from a cursor. The feature regarding columns description is needed to fetch results of queries, but the rest of your request can't come from there

Anyway SQL is not that simple as you may suppose, and what kind of output would you except on SQL statements using subqueries, ansi join or a model clause for example?

If you're looking for a ready to go solution, you won't find anything for that scope. The only solution is to build your own parser that recognizes a limited SQL syntax that may be enough for your input statements and then use, but it's a hard job.

I advice you to change the project and to forget about the implementation of this feature.


Bye Alessandro
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364877 is a reply to message #364840] Wed, 10 December 2008 05:51 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
Print_Table uses dynamic sql.
The input file which I will be getting will have a query which can run directly from SQL Plus. So, it won't have 2 single quotes to denote 1 single quote.

Eg.
For dynamic sql the query string has to be
query_string := 'SELECT ''Hello'' FROM Dual'

Is there a way to overcome this issue ?
Is there any way we can do this without using dynamic sql ?

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364880 is a reply to message #364875] Wed, 10 December 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I completly misunderstood the question.
Indeed there is no Oracle package for this, you can investigate on lex and yacc as syntax analyzers.

Regards
Michel
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364889 is a reply to message #364840] Wed, 10 December 2008 06:20 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
What if I expected the SQL query to be in a strict pre-defined format.

Eg. 1 column per line in the text file.
1 table per line in the text file etc.

Of course the problem would arise when subqueries are present.

But, would a pre-defined format simplify things ?

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364901 is a reply to message #364889] Wed, 10 December 2008 07:28 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Quote:

But, would a pre-defined format simplify things ?



I was talking about a limited SQL syntax because a limited SQL syntax limits the context free grammar you need to define for the parser.

But the syntax for a limited sql that allows only the select, the from and the where clauses with the use functions is very complex too.

I advice you to go some step behind on your project, just before where you decided to parse SQL statements, ask help to solve your problem and then find an alternative solution.

Believe me, there is always an alternative solution that solves the problem in a more efficient way that this one.

Bye Alessandro

[Updated on: Wed, 10 December 2008 07:30]

Report message to a moderator

Re: Parse SQL: How to extract column names, table names from a SQL query [message #364903 is a reply to message #364840] Wed, 10 December 2008 07:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
But, would a pre-defined format simplify things ?

Not much, this is just a matter of whitespace definition.
Quote:
Of course the problem would arise when subqueries are present.

Not only subqueries, there are much more other options. The full SELECT statement syntax is stated in SQL Reference documentation book: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065706. Picking only a few of them can simplify the code needed for query parsing.

Of course, this coding requires quite a good knowledge of Compiler theory. There are special tools (more convenient than procedural PL/SQL) for both its steps: lex for for lexical analysis and yacc for parsing.

Just curious what are these values required for? Are you trying to replace Oracle built-compiler with your own?
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364911 is a reply to message #364848] Wed, 10 December 2008 07:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sammydude wrote on Wed, 10 December 2008 05:35

SELECT Empno, Empname, Deptno, Nvl(Sal,0)
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno

Output
------
Columns
Empno
Empname
Deptno
Nvl(Sal,0)
Tables
Emp
Dept
Where
Emp.deptno = Dept.deptno



This is a really bad design. You cannot guarantee the order of the data in a table, so how can you expect that rows listed under columns are not tables and vice versa?
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364916 is a reply to message #364911] Wed, 10 December 2008 08:09 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
No. I had indented the column names under the columns and the table names under the tables header.
Just that orafaq took away the formatting. Smile

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364921 is a reply to message #364916] Wed, 10 December 2008 08:24 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
No, what I am saying is that unless you use an ORDER BY clause to select your data, there is no way to guarantee that your column names will be listed under columns and tables names under tables.

This could easily be your output:
Output
------
Columns
Tables
Emp.deptno = Dept.deptno
Empno
Empname
Nvl(Sal,0)
Where
Emp
Dept
Deptno

Re: Parse SQL: How to extract column names, table names from a SQL query [message #364923 is a reply to message #364921] Wed, 10 December 2008 08:26 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
joy_division wrote on Wed, 10 December 2008 08:24
No, what I am saying is that unless you use an ORDER BY clause to select your data, there is no way to guarantee that your column names will be listed under columns and tables names under tables.



Actually I won't be storing the data in the same table. Separate tables to store columns, tables and where clauses.

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364924 is a reply to message #364903] Wed, 10 December 2008 08:30 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
flyboy wrote on Wed, 10 December 2008 07:33

Just curious what are these values required for? Are you trying to replace Oracle built-compiler with your own?


I'm looking to parse the input SQL and insert the list of columns, tables, etc. into separate tables for columns, tables and where clauses.
When required I should be able to construct the SQL query again to be displayed to the user.

Regards,
Sam
Re: Parse SQL: How to extract column names, table names from a SQL query [message #364927 is a reply to message #364924] Wed, 10 December 2008 08:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not just store the SQL query?
Why go to all the trouble of breaking it down and rebuilding it?
Re: Parse SQL: How to extract column names, table names from a SQL query [message #365085 is a reply to message #364840] Wed, 10 December 2008 22:15 Go to previous message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Use UTL_FILE to read the data from flat file and
use External Table to load the data.


Previous Topic: need help on creating a view excluding some columns from the table
Next Topic: Statistical Computations
Goto Forum:
  


Current Time: Fri Dec 02 14:13:57 CST 2016

Total time taken to generate the page: 0.09782 seconds