defining a variable type using another dynamic variable. oracle. pl sql

Question

Here is the deal I'm trying to define MY_VARIABLE, which would be of a type MY_TABLE%ROWTYPE.

The problem is that MY_TABLE is dynamic and i receive it as a varchar2 variable, so something like

 TYPE my_variable 
   IS TABLE OF my_table%ROWTYPE;

wouldnt work because Compilation errors for PACKAGE BODY DENNIS.XXPORTER

'YOUR_TABLE_NAME' must name a table, cursor or cursor-variable

Now, how do i deal with that??

P S I need my_variable to fetch records from the ref cursor. And i used ref cursor not a cursor as i executed a query where table name (in from clause) was a variable

then you cant create a rowtype using only the name of the table (unless you write dynamic plsql code - which i'm guessing would be an overkill here). if you have a ref cursor you can just iterate it. — haki
– haki, Commented Jun 25, 2013 at 14:45
As with each loop - i'll have a number of values to be stored. What variable/variables can i use for that? AND i do not know the table name or columns types in advance — Denys
– Denys, Commented Jun 25, 2013 at 14:48

Justin Cave · Accepted Answer · 2013-06-25 14:48:14Z

2

You can't declare a variable whose data type is not known until runtime.

If you are trying to use dynamic SQL where the structure of the result set is also dynamic (if the number of columns and their data types are fixed for any table that is passed in, you could statically declare a record variable of an appropriate type), then you would realistically need to use the DBMS_SQL package to execute the statement, to gather data about the columns that are returned, and to bind appropriate variables into which you could fetch the data.

You can see an example of using the DBMS_SQL package in Tom Kyte's dump_csv function that dumps the results of an arbitrary SQL statement to a file.

answered Jun 25, 2013 at 14:48

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Denys Over a year ago

thanks, Your answer is quite useful. Currently im eximining the dbms_sql package.

Denys Over a year ago

that was it. THe example of a solution is somewhere on askTom website. I'll add a link sometime

Justin Cave Over a year ago

@Dennis - Other than the link I had originally? Or are you saying you prefer an example other than the dump_csv procedure?

Denys Over a year ago

Oh, my bad :) Justin, the link you initially posted ( the dump_csv one) is the one i mentioned. For some reason i didnt notice it the first time I read your answer, so I wasted some time looking for the same info myself :)

Collectives™ on Stack Overflow

defining a variable type using another dynamic variable. oracle. pl sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related