CODEWITHSUNDEEP
oracle-databaseplsql
thebox
thebox

Reputation: 3

Is there a chance in PL/SQL to get the name of a variable as a string?

I would like to get the name of a variable as a string within Oracle PL/SQL. Do you have any ideas, how to do so? In Oracle*Forms there are useful functions like NAME_IN which is not available in PL/SQL. But I need to do that within native PL/SQL. Thank you very much.

PROCEDURE px (pi_foo IN number) IS
begin
  insert into mytable (my_var_name, my_var_value)
               values (magic_function_we_are_searching_for(pi_foo), pi_foo);
end;

PRODEDURE py_calls_px IS
  v_var1 number := 999;
begin
  px(pi_foo => v_var1);
end;

Upvotes: 0

Views: 838

Answers (1)

Alexey Ivanov
Alexey Ivanov

Reputation: 136

There is start point to you.
You should need to learn more about utl_call_stack.subprogram and also make decision about access rights to SYS.ALL_ARGUMENTS (if you need it really).
But it's just details.

create or replace procedure my_proc(my_foo in varchar2) is
  PROC_NAME varchar2(50) := utl_call_stack.subprogram(1)(1);
  NAME_IN varchar2(50);
begin
    SELECT
        ARGUMENT_NAME
    INTO
        NAME_IN
    FROM SYS.ALL_ARGUMENTS
    where OBJECT_NAME  = PROC_NAME
      and IN_OUT = 'IN';
    DBMS_OUTPUT.PUT_LINE(NAME_IN || ' = ' || my_foo);
end;

begin
    my_proc('bar');
end;

-- result --
MY_FOO = bar

Upvotes: 3

Related Questions