Home » SQL & PL/SQL » SQL & PL/SQL » Parse SQL: Extract column expression and labels from SQL query (SQL / PL/SQL)
Parse SQL: Extract column expression and labels from SQL query [message #399270] Tue, 21 April 2009 06:40 Go to next message
sammydude
Messages: 34
Registered: November 2007
Member
Hi all,

I have a requirement and posted earlier reg. this.
A SQL query needs to be parsed and the column names have to be extracted separately.
I cannot use dbms_sql. I need to extract the column names through string manipulation in PL/SQL only.
I don't have to validate the SQL.

eg.
SELECT EMPNO, Upper(ENAME) Emp_Name, DEPTNO, DECODE(Deptno,10,'ACCOUNTING', 20,'HR','OTHERS') Dept_Name FROM Dual

Output should be,

Column Names
------------
1) EMPNO
2) Upper(ENAME) Emp_Name
3) DEPTNO
4) DECODE(Deptno,10,'ACCOUNTING', 20,'HR','OTHERS') Dept_Name

How do I extract column names using PL/SQL for the format given above?
Pls help.

Regards,
Sam
Re: Parse SQL: Extract column expression and labels from SQL query [message #399272 is a reply to message #399270] Tue, 21 April 2009 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I cannot use dbms_sql.

Why? This is the way to do it.

Regards
Michel
Re: Parse SQL: Extract column expression and labels from SQL query [message #399274 is a reply to message #399270] Tue, 21 April 2009 06:51 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
I won't be having access to schemas, whose tables are part of the SQL query. That's why I can't use dbms_sql.

Regards,
Sam
Re: Parse SQL: Extract column expression and labels from SQL query [message #399280 is a reply to message #399274] Tue, 21 April 2009 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sammydude wrote on Tue, 21 April 2009 13:51
I won't be having access to schemas, whose tables are part of the SQL query. That's why I can't use dbms_sql.

Regards,
Sam

In this case you cannot execute the statement.
So why do you care for its returned fields?

Regards
Michel

Re: Parse SQL: Extract column expression and labels from SQL query [message #399284 is a reply to message #399270] Tue, 21 April 2009 07:26 Go to previous messageGo to next message
sammydude
Messages: 34
Registered: November 2007
Member
We have a system where we can store these values in a table. The user can check, modify and validate this data at a later point of time. This data will afterwards be used to re-create the SQL query again.

Regards,
Sam
Re: Parse SQL: Extract column expression and labels from SQL query [message #399288 is a reply to message #399270] Tue, 21 April 2009 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Smae question than in http://www.orafaq.com/forum/m/364840/102589/#msg_364840

Regards
Michel
Re: Parse SQL: Extract column expression and labels from SQL query [message #399289 is a reply to message #399284] Tue, 21 April 2009 07:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So why not just store the query?

If you could execute the query, you could wrap it in a 'SELECT * FROM (<your query>) where 1=2', and do a CREATE TABLE AS, to get a description.

If you can't execute the query, and can't use DBMS_SQL, then you're going to have to write a really complex string parser to split the query down.
Previous Topic: Parallelize alter table statement
Next Topic: stored procedure with cursors
Goto Forum:
  


Current Time: Sat Dec 10 12:54:43 CST 2016

Total time taken to generate the page: 0.10848 seconds