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;
On Mon, Jul 28, 2014 at 5:27 AM, Animesh Kumar <[email protected]>
wrote:
> 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;
>
>
> On Friday, July 25, 2014 9:07:53 PM UTC+5:30, Michael Moore wrote:
>
>> 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 <[email protected]>
>> 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 [email protected]
>>>
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>>
>>> 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 [email protected].
>>>
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
