How to enumerate fields within a record??

[sjh]

Hi,

I am a beginner to Oracle programming.

I am writing a stored procedure that takes in a random SELECT SQL and outputs the result to email. Since I won't know the structure of the SELECT statement, I am not sure how to access a field within a record. Normally, it would be record_name.field_name, but I don't know what the field_name will be. Is there a different way to reference the fields in a record?

Please help!

Thank you!
Susie

 

[lewisp]

Welcome to Oracle. Its a baptism of fire!

You will need to use dynamic SQL method 4 to achieve your goal. This means using the DBMS_SQL package and PREPAREing your SQL statement. Each column in each row can then be referenced by position number in a loop.

 

[SantaMufasa]

Susie,

If you do not know a specific field name within a record, then will "SELECT * FROM table_name;" (all columns) not work for you?



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sjh]

The user may or may not use the "SELECT * " statement, so I still won't know the field names.

So there is no easy way to list values in the individual fields of a record? I will look into using the DBMS_SQL package.

Thanks!

Susie

 

[Turkbear]

Hi,
Actually, there are several easy ways to list the fields ( and/or their values), but not many easy ways to dynamically select some or all of them for use in a subsequent Sql Select statement..





To Paraphrase:"The Help you get is proportional to the Help you give.."