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
banani_chat
Messages: 1
Registered: November 2006
Location: Hyderabad
Junior Member

Hi
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.

TIA
Re: Dynamically naming and declaring variables [message #202245 is a reply to message #202135] Wed, 08 November 2006 19:55 Go to previous message
rleishman
Messages: 3724
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: need help with script
Next Topic: Stored Procedure: Prompt
Goto Forum:
  


Current Time: Sun Dec 04 10:49:27 CST 2016

Total time taken to generate the page: 0.08503 seconds