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
289 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
Comment Utility
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
Comment Utility
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]
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

744 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

18 Experts available now in Live!

Get 1:1 Help Now