Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to enumerate fields within a record??

Status
Not open for further replies.

sjh

Programmer
Joined
Oct 29, 2001
Messages
263
Location
US
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
 
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.
 
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?



[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
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
 
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..



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top