Retrieving value of variable based on variable name stored in table.

[Animesh Kumar]

I have few variable names stored in a table. I want to retrieve their values in a procedure where all the variables are already defined. Can someone suggest the way of doing it?

[Michael Moore]

read through this. 
http://www.pythian.com/blog/how-to-dynamically-call-plsql-procedure-in-oracle/
It's not exactly what you want, but if you understand the principles involved, you will be able to do what you want.

Mike

On Fri, Jul 25, 2014 at 2:27 AM, Animesh Kumar <animes...@gmail.com> wrote:

I have few variable names stored in a table. I want to retrieve their values in a procedure where all the variables are already defined. Can someone suggest the way of doing it?

--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

---
You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Animesh Kumar]

I tried using dynamic query but no success. Can you explain me with the example I have mentioned below?

CREATE TABLE VALUE_SET

(

  VALUE_SET_ID         NUMBER    NOT NULL,

  VALUE_TYPE           VARCHAR2(100 BYTE),

  VAL                          VARCHAR2(250 BYTE)

);

INSERT INTO VALUE_SET VALUES

(1,'CONSTANT', 'ABC');

INSERT INTO VALUE_SET VALUES

(2,'VARIABLE', 'g_name');

INSERT INTO VALUE_SET VALUES

(3,'VARIABLE', 'g_age');

DECLARE

g_name VARCHAR2(100) := 'JOHN'

g_age  VARCHAR2(100) := '30'

BEGIN

FOR REC IN (SELECT * FROM VALUE_SET)

LOOP

IF REC.VALUE_TYPE = 'CONSTANT'

THEN

DBMS_OUTPUT.PUT_LINE(REC.VAL);

ELSIF REC.VALUE_TYPE = 'VARIABLE'

THEN

DBMS_OUTPUT.PUT_LINE(REC.VAL); //Here I want to display the value of variable(JOHN) instead of g_name

END IF;

END;

[Michael Moore]

DECLARE

   lconst   VARCHAR2(4000);

BEGIN

   FOR rec IN (SELECT * FROM value_set)

   LOOP

      IF rec.value_type = 'CONSTANT' THEN

         lconst := 'begin

         DBMS_OUTPUT.put_line('''||rec.val||''');

         end;';

         EXECUTE IMMEDIATE lconst;

      ELSE

         lconst :=

               'declare

                  g_name   VARCHAR2(100) := ''JOHN'';

                  g_age    VARCHAR2(100) := ''30'';

               begin

                  DBMS_OUTPUT.put_line('

                 || rec.val

                 || ');

                end;';

         EXECUTE IMMEDIATE lconst;

      END IF;

   END LOOP;

END;