Solved

How to get column names and/or column data from %ROWTYPE via column number in Oracle PL/SQL?

Posted on 2014-09-17
3
303 Views
Last Modified: 2014-11-14
Is something like this possible?
declare
  cursor c_pr is
    select * from a_table;
   r_pr c_pr%ROWTYPE;
begin
  open c_pr
  loop
    fetch c_pr into r_pr;
    exit when c_pr%NOTFOUND;
    for i in 1..r_pr.number_of_columns
    loop
       DBMS_OUPUT.put(r_pr.column(i)||chr(9));
    end loop;
       DBMS_OUTPUT.put_line('');
   end loop;
   close c_pr;
end

Open in new window

There may be other errors in the code - haven't really checked, but you should get my point. Can I extract column data and number of columns and eventually column names in a way something like above?

Thx!
IVer
0
Comment
Question by:IverErling
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
ID: 40327685
Short answer: the way you want to code it: NO
You'll have to use dynamic SQL with DBMS_SQL package, there should be numerous examples around the internet ;-)
If you need further help on that subject and/or own examples, just ask and I'll dig into my code snippets ;-)
0
 

Author Comment

by:IverErling
ID: 40327766
Ok. It's not that important to me at the moment. I just thought it would be nice to know if the info was held in the declared variable, and it just was a matter of knowing the syntax.
I have seen an example of something similar using dynamic SQL and it seemed unneccessarily complex. Strange really that not even the number of columns and the column numbers are known to the ROWTYPE variable!

THanks!

Brgds IVer
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40327827
That's definetely a feature to PL/SQL that should be implemented in future versions ;-)
Accessing fields/columns within ROWTYPE variables through their corresponding names and/or indexes... That would be great...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now