Re: PL/SQL NAME_IN Function !

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 30 Sep 1998 16:26:10 GMT
Message-ID: <36185b19.16937584@192.86.155.100>

A copy of this was sent to Dheenadayalu <ddayalu_at_amgen.com> (if that email address didn't require changing) On Wed, 30 Sep 1998 08:30:26 -0700, you wrote:

>Hi,
>
>am trying to find an equivalent function in PL/SQL Triggers / Functions
>/ Procedures which act the same as the NAME_IN function in Forms.
>
>It is a kind of Show stopper for me.
>
>Thanx
>Dheena.

There is no equivalent.

If the variable you desire to know the value of just happens to be declared in a PACKAGE SPECIFICATION (not a standalone procedure/function and not in a package body) then we can use DBMS_SQL to discover its value.

For example:

create or replace package value_of
as

    theValue varchar2(2000);

    function something( p_fieldname in varchar2 ) return varchar2; end;
/

create or replace package body value_of as

function something( p_fieldname in varchar2 ) return varchar2 is

    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;

begin

    l_theCursor := dbms_sql.open_cursor;

    dbms_sql.parse( l_theCursor,

                    'begin value_of.theValue := ' || p_fieldname || ';end;',
                     dbms_sql.native );

    l_status := dbms_sql.execute(l_theCursor);     dbms_sql.close_cursor(l_theCursor);     return theValue;
end something;

end value_of;
/

REM -------- demo below ---------------------

create or replace package demo
as

    x number default 5;

    function foo return varchar2;
end;
/
create or replace package body demo
as

function foo return varchar2
is
begin

    return 'hello';
end;

end demo;
/

set serveroutput on
create or replace procedure run_demo
as

    l_theVar varchar2(255);
begin

    demo.x := 55;

    l_theVar := 'demo.x';

    dbms_output.put_line( 'the value of ' || l_theVar || ' is ' ||

                            value_of.something( l_theVar ) );

    l_theVar := 'demo.foo';

    dbms_output.put_line( 'the value of ' || l_theVar || ' is ' ||

                            value_of.something( l_theVar ) );

end;
/

exec run_demo  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 30 1998 - 11:26:10 CDT