Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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

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
Iver Erling Arva
Asked:
Iver Erling Arva
  • 2
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Iver Erling ArvaSenior consultantAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now