ok let me ask a few questions here,
you are getting the name of "variable" from a "view", so number(how
many) of variables is not constant??
it must be constant as that variable must exist inside the package
otherwise it doesn't make sense, or is it?
so, if we have a fix number of possible selection, is it really
necessary to use anything like execute immediate?
execute immediate will run a sql query so it will never return the
value of some value, can it?
my suggestion assuming the fact the number of variables will be
constant will be something like below piece of code:
DECLARE
myvar VARCHAR2 (200) := NULL;
urvar VARCHAR2 (200) := NULL;
nameof VARCHAR2 (200) := NULL;
x VARCHAR2 (200) := NULL;
BEGIN
myvar := 5;
urvar := 6;
nameof := 'myvar';
select decode(nameof,'myvar',myvar,'urvar',urvar,'None')
into x
from dual;
DBMS_OUTPUT.put_line (x);
nameof := 'urvar';
select decode(nameof,'myvar',myvar,'urvar',urvar,'None')
into x
from dual;
DBMS_OUTPUT.put_line (x);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
when you run this code the output will be:
5
6
Any possible suggestions, feedback, concerns and better solutions are
always welcome.
Regards,
Sonty
On Oct 27, 11:41 pm, Andrej Hopko <ado.ho...@gmail.com> wrote:
> just think about it:
>
> you are trying to use a bind variable in (exec. immed.) scope EI, a
> variable that should be bound to another variable from (parents) scope P
> where you will have to name it
>
> so by inserting nameof you insert a bind to variable you need specify
> (in full name) in USING clause after exec. immed.
>
> yes, this will definitely work, but dynamically? no! how if you have to
> specify one name for one bind variable in scope P to replace your :myvar
> bind in scope EI
>
> execute immediate was made quite useful, but not almighty :D (seeing
> scope P from scope EI might be hell of a leak - as "eval" is in ruby,
> javascript and many others)
>
> so if you want to do this magic, you need to use it from the start - and
> on the beginning there is always a table and rows ;-) - there it should work
>
> regards
> hoppo
>
> p.s.: CALM DOWN! It's Only 1's and 0's
>
> On 27. 10. 2010 18:43, Gopakumar Pandarikkal wrote:
>
> > One last attempt for me
> > try putting a colon before myvar (to make it a bind variable)
>
> > nameof := ':myvar' ;
>
> > Please let me know the result
>
> > Gopakumar Pandarikkal
>
> > On Wed, Oct 27, 2010 at 10:01 PM, Andrej Hopko <ado.ho...@gmail.com
> > <mailto:ado.ho...@gmail.com>> wrote:
>
> > it barfs the same error as every my try (kinda pissing me off):
> > Error report:
> > ORA-00904: "MYVAR": invalid identifier
> > ORA-06512: on line 6
> > *Cause:
> > *Action:
>
> > execute immediate just don't see the PL/SQL scope of its caller
>
> > regards
> > hoppo
>
> > On 27. 10. 2010 18:22, Gopakumar Pandarikkal wrote:
> >> myvar := 5;
> >> nameof := 'myvar' ;
> >> execute immediate 'select ' || nameof || ' from dual ' into x ;
>
> >> Please try this please let me know if it works.
>
> >> the trick is to concatenate the content of the variable.
> >> It should work.
>
> >> Gopakumar Pandarikkal
>
> >> myvar := 5;
> >> nameof := 'myvar' ;
> >> execute immediate 'select ' || nameof || ' from dual ' into x ;
>
> >> Please try this please let me know if it works.
> >> It should work.
>
> >> Gopakumar Pandarikkal
>
> >> On Wed, Oct 27, 2010 at 5:55 PM, oldyork90 <oldyor...@yahoo.com
> >> <mailto:oldyor...@yahoo.com>> wrote:
>
> >> I have a name, collected from a view. This name is a string,
> >> (varchar2), and is a name of a var. I want the value of
> >> the var
> >> named by this name.
>
> >> If you know something like perl, it would look like this:
>
> >> $myvar = 5;
> >> $nameof = 'myvar';
> >> $x = eval $$nameof
>
> >> I think execute immedate is in play here, but all my attempts
> >> fail. I
> >> can't get it to evaluate the name and return the value of that
> >> variable.
>
> >> Thank you.
>
> >> --
> >> 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-PLSQL@googlegroups.com
> >> <mailto:Oracle-PLSQL@googlegroups.com>
> >> To unsubscribe from this group, send email to
> >> oracle-plsql-unsubscr...@googlegroups.com
> >> <mailto:oracle-plsql-unsubscr...@googlegroups.com>
> >> For more options, visit this group at
> >> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> >> --
> >> Gopakumar P.G.
>
> >> --
> >> 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-PLSQL@googlegroups.com <mailto:Oracle-PLSQL@googlegroups.com>
> >> To unsubscribe from this group, send email to
> >> oracle-plsql-unsubscr...@googlegroups.com
> >> <mailto:oracle-plsql-unsubscr...@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 post to this group, send email to Oracle-PLSQL@googlegroups.com
> > <mailto:Oracle-PLSQL@googlegroups.com>
> > To unsubscribe from this group, send email to
> > oracle-plsql-unsubscr...@googlegroups.com
> > <mailto:oracle-plsql-unsubscr...@googlegroups.com>
> > For more options, visit this group at
> > http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> > --
> > Gopakumar P.G.
>
> > --
> > 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-PLSQL@googlegroups.com
> > To unsubscribe from this group, send email to
> > oracle-plsql-unsubscr...@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 post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
