asked on

RECORD TYPE rows from multiple tables

Hi,

I have to query 50 columns from different tables in PL-SQL block. (only one row will be selected)
So in INTO clause i have to declare 50 variables or otherwise I have to create RECORD TYPE of that specified query so is there any way we can define the dynamic RECORD TYPE depending on multiple table columns.

THANKS AND REGARDS

SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sudees

ASKER

Hi Wasim,

First of all I would like to say thanks for giving time to understand my requirement, your code is looking good but what is the purpose of this line of code
l_rec rec_model%ROWTYPE;
This line is declaring REC_MODEL named table or view ROWTYPE and it shows its not dynamic.
And in case, if V_SQL may have more than one table to query like DEPTNO and EMP then this procedure may not work..

Thanks and Regards,

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wasim Akram Shaik

Yes.. I agree.. You cannot make everything dynamic.. You need to have a definition somewhere.. As plsql is a compiler based language it expects declaration, definition and then only execution part will work successfully.. If you have simple data types then go for a varchar2 declaration of all variables and declare a record set based on that.. If at all any number data types are there they will get converted implicitly.