| Parse SQL: How to extract column names, table names from a SQL query [message #364840] |
Wed, 10 December 2008 04:04  |
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 #364877 is a reply to message #364840] |
Wed, 10 December 2008 05:51  |
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 #364901 is a reply to message #364889] |
Wed, 10 December 2008 07:28 |
 |
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 #364911 is a reply to message #364848] |
Wed, 10 December 2008 07:52 |
joy_division
Messages: 4963
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 #364921 is a reply to message #364916] |
Wed, 10 December 2008 08:24 |
joy_division
Messages: 4963
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 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
