Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically naming and declaring variables
icon5.gif  Dynamically naming and declaring variables [message #202135] Wed, 08 November 2006 05:49 Go to next message
Messages: 1
Registered: November 2006
Location: Hyderabad
Junior Member

here is the scenario...
i have a varray that lists the data types, populated and sent from a diff procedure.
using the varray i need to dynamically name variables and assign data types.
say. Data_type_Varr has 3 elements NUMBER,VARCHAR2,NUMBER

since i will not know exactly how may elements there may be in the varray at compile time... i have to read the varray, then depending on the data type .... name and assign data types which will be used as cursor variables and also in queries there after.

Pls suggest ways to do the above.

Re: Dynamically naming and declaring variables [message #202245 is a reply to message #202135] Wed, 08 November 2006 19:55 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You cannot dynamically declare variables that can be used in later PL/SQL code.

You could move them from the VARRAY into an associative array (TABLE OF varchar2(30) INDEXED BY varchar2(30))

Then you could reference them in later code as:
thisval := dynamic_hash('FRED');
thatval := dynamic_hash('BARNEY');

However you would have to handle type conversions to numbers, or have a separate array for numbers.

Another alternative is SYS_CONTEXT - look it up in the doco.

Ross Leishman
Previous Topic: produce 1 row
Next Topic: Formatting Data and inserting into a table
Goto Forum:

Current Time: Thu Aug 24 00:02:26 CDT 2017

Total time taken to generate the page: 0.04887 seconds