Home » SQL & PL/SQL » SQL & PL/SQL » How do I obtain cursor field names from cursor into variable
How do I obtain cursor field names from cursor into variable [message #323434] Wed, 28 May 2008 08:23 Go to next message
phuksi
Messages: 4
Registered: May 2008
Junior Member

How do I get the cursor field names from a cursor?

If I have defined:

cursor c1 is select * from dual;
v_col_name varchar2(20);
v_col_value varchar2(20);

then my plsql has:

for r1 in c1 loop
v_col_name := r1.[COLUMN_NAME???] -- so this would = DUMMY
v_col_value := r1.dummy -- so this would = X
end loop;

- p
Re: How do I obtain cursor field names from cursor into variable [message #323442 is a reply to message #323434] Wed, 28 May 2008 08:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

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

Regards

Raj

[Updated on: Wed, 28 May 2008 08:40]

Report message to a moderator

Re: How do I obtain cursor field names from cursor into variable [message #323446 is a reply to message #323434] Wed, 28 May 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
T.Kyte's print_table function.

Regards
Michel
Re: How do I obtain cursor field names from cursor into variable [message #323450 is a reply to message #323446] Wed, 28 May 2008 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would argue that the data in the cursor doesn't have column names in any meaningful sense.
The variables/client that it is fetched into had column names, but the data returned by a cursor is simple a set of data-types and values.
Re: How do I obtain cursor field names from cursor into variable [message #323508 is a reply to message #323434] Wed, 28 May 2008 11:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
For some perspective let me make these two statements:

1) it is not possible to get column names from a cursor. Oracle has not exposed this interface to-date as far as I know anyway. This is the reason for what was said by JROWBOTTOM.

2) it is possible to parse a query and get the column names from the query. This is what S.Rajaram and Michel are talking about.

There is a distinct difference between these two requirements. So, when you look at examples, understand that you must have the original SQL used to open a cursor with if you want to get the column names, datatype info, etc. using dbms_sql.describe_columns.

Building a truly generic routine that would take for example, a refcursor and give you back the column and type info for the refcursor is not possible.

Building the same routine to take in the text of a valid select statment and return the column and type info of the select statement is possible and these examples have been provided to you via the links in other posts.

Good luck, Kevin
Re: How do I obtain cursor field names from cursor into variable [message #323509 is a reply to message #323508] Wed, 28 May 2008 11:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
it is not possible to get column names from a cursor. Oracle has not exposed this interface to-date as far as I know anyway

I think they have made it possible both ways in Oracle 11g.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm

Regards

Raj
Re: How do I obtain cursor field names from cursor into variable [message #323511 is a reply to message #323508] Wed, 28 May 2008 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Oracle allows it in 11g as DBMS_SQL can now translate a ref cursor to a dmbs_sql cursor number (TO_CURSOR_NUMBER function) then the way is the same as for Tom Kyte's print_table function.

Regards
Michel
Re: How do I obtain cursor field names from cursor into variable [message #323532 is a reply to message #323434] Wed, 28 May 2008 12:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
way cool. thanks for cluing me to this Michel. After reading your post I found this link

Describe RefCursors

I am going to give it a go. Looks like a mighty useful piece of material.

Kevin
Re: How do I obtain cursor field names from cursor into variable [message #323537 is a reply to message #323532] Wed, 28 May 2008 12:47 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This remembers me the following article from T. Kyte in which he gives an improvement of his dump_csv package using this feature:
http://www.oracle.com/technology/oramag/oracle/07-nov/o67asktom.html

Regards
Michel
Previous Topic: ORA-03134
Next Topic: creating an array out of several rows of data.
Goto Forum:
  


Current Time: Fri Dec 09 15:52:05 CST 2016

Total time taken to generate the page: 0.15775 seconds