Home » SQL & PL/SQL » SQL & PL/SQL » How to know the column name used in query? (oracle 9i)
How to know the column name used in query? [message #392045] Mon, 16 March 2009 03:22 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi,
I want to know the column names used in queries.

Sample
      Select emplcode,emplname from emplmast

emplcode
emplname


Is any function availble in oracle. Otherwise we need create our own procedure/functions.

kanish
Re: How to know the column name used in query? [message #392051 is a reply to message #392045] Mon, 16 March 2009 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know the columns of any query, you have to use dbms_sql (in PL/SQL) or some OCI functions in other languages.
To know the columns of a table or view you can query all_tab_columns.

Regards
Michel
Re: How to know the column name used in query? [message #392090 is a reply to message #392045] Mon, 16 March 2009 05:47 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Can u give a sample code?

kanish
Re: How to know the column name used in query? [message #392095 is a reply to message #392090] Mon, 16 March 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "print_table" function.

Regards
Michel
Re: How to know the column name used in query? [message #392273 is a reply to message #392095] Tue, 17 March 2009 04:43 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Select REPLACE(REPLACE(SQL_TEXT,'Select',' '),' from emplmast','') from sys.v_$sql s 
where s.PARSING_SCHEMA_NAME=USER AND 
s.SQL_TEXT ='Select emplcode,emplname from emplmast'
Re: How to know the column name used in query? [message #392277 is a reply to message #392273] Tue, 17 March 2009 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That solution does rather rely on knowing what the actual SQL that was used was.
And, if you know that, you'd be better off rewriting the query like this:
Select REPLACE(REPLACE(SQL_TEXT,'Select',' '),' from emplmast','') 
from (select 'Select emplcode,emplname from emplmast' sql_text
      from dual);
Re: How to know the column name used in query? [message #392278 is a reply to message #392277] Tue, 17 March 2009 05:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here is theSQL to return the text of the previous SQL that your session executed - You could parse the column names out of this if you like:
select sid
      ,ses.prev_sql_id
      ,sq.sql_text
from   v$session  ses
      ,v$sql      sq
WHERE  ses.audsid = SYS_CONTEXT('userenv','sessionid')
and    sq.sql_id = ses.prev_sql_id;
Re: How to know the column name used in query? [message #392349 is a reply to message #392045] Tue, 17 March 2009 10:45 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You can take a look here (Example 8 )

Bye Alessandro

[Updated on: Tue, 17 March 2009 10:45]

Report message to a moderator

Re: How to know the column name used in query? [message #392415 is a reply to message #392045] Tue, 17 March 2009 22:10 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Thank to all,

I done with dbms_sql. Again Thanks to everybody.

Kanish
Re: How to know the column name used in query? [message #392416 is a reply to message #392045] Tue, 17 March 2009 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
from http://www.orafaq.com/forum/t/88153/0/

If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Re: How to know the column name used in query? [message #392424 is a reply to message #392045] Tue, 17 March 2009 23:38 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Just i write a function.

Create or Replace Function Get_Sql_Col_cnt(Qry Varchar2) return number is
 cur       integer := dbms_sql.open_cursor;
 desctbl   dbms_sql.desc_tab;
 cnt       integer;  
Begin
     dbms_Sql.parse(cur,qry,dbms_sql.native);
     dbms_Sql.describe_columns(cur,cnt,desctbl);
     return(cnt);
End;



Create or Replace Function Get_Sql_col_Name(Qry varchar2, Row Number) return Varchar2 is
   Cur       Integer  := dbms_sql.Open_Cursor;
   desctbl   dbms_Sql.desc_tab;
   Cnt integer;
Begin
     dbms_sql.parse(cur,Qry,dbms_sql.native);
     dbms_Sql.describe_columns(cur,cnt,desctbl);
     return(desctbl(Row).col_name);
End;


kanish
Re: How to know the column name used in query? [message #392457 is a reply to message #392424] Wed, 18 March 2009 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what are intended to do these functions but I strongly recommend you to NOT use ROW reserved word as identifier.

Regards
Michel
Re: How to know the column name used in query? [message #392485 is a reply to message #392045] Wed, 18 March 2009 03:21 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

defently i will take your advice Michel

Thank you

kanish
Previous Topic: Query to alter mass number of tables
Next Topic: add a restriction depending on IN Variable
Goto Forum:
  


Current Time: Mon Dec 05 11:20:55 CST 2016

Total time taken to generate the page: 0.07586 seconds