This discussion is archived
10 Replies Latest reply: Nov 26, 2012 10:05 AM by BillyVerreynne RSS

Obtain string of IN variable name in PL/SQL

chris001 Newbie
Currently Being Moderated
Hi,
Odd question. Is there a way to access an IN variable's object name in a procedure?

eg.
procedure (var1    IN VARCHAR2(6),
                var2    IN VARCHAR2(6),
                var3    IN VARCHAR2(6))

IS....
You run this procedure and pass in parameters of:

var1 => 'abcdef',
var2 => 'defghi',
var3 => 'ghikjl';

In the code, I want to access the number in the string of the +variable name: var1+

In a naive sense,
num_of_in_var := substr(var1, 4,1) 
would be what i was hoping to achieve, but,

var1 is set to 'abcdef' so,
num_of_in_var := substr(var1, 4,1) 
would return 'd'.

Any thoughts?

Thanks

Edited by: chris001 on Nov 20, 2012 1:16 PM
  • 1. Re: Obtain string of IN variable name in PL/SQL
    sb92075 Guru
    Currently Being Moderated
    What problem are you really trying to solve?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Obtain string of IN variable name in PL/SQL
    user521233 Newbie
    Currently Being Moderated
    i'm not aware of a way of accessing input variable names in pl/sql like that... i imagine you are trying to iterate over them for some reason.

    since you've named them yourself you could just build an associative array with the variable name strings as subscript values.
  • 3. Re: Obtain string of IN variable name in PL/SQL
    rp0428 Guru
    Currently Being Moderated
    >
    In the code, I want to access the number in the string of the variable name: var1
    >
    You already know the 'number in the string of the variable name'. It is '1'. So write code to do whatever it is you are trying to do and use '1' in the code.

    If you explain what you are really trying to do there might be a better way to help you.
  • 4. Re: Obtain string of IN variable name in PL/SQL
    jeneesh Guru
    Currently Being Moderated
    <Irrelevant - Deleted>

    Edited by: jeneesh on Nov 21, 2012 9:16 AM
  • 5. Re: Obtain string of IN variable name in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    chris001 wrote:

    Odd question. Is there a way to access an IN variable's object name in a procedure?
    Yes, you can read the "compiler trace output" of code to see variables declared in the code. You can read the code's parameter signature from the data dictionary. Blu gave a nice example of the former in {message:id=10700970}.

    But the question is why? You cannot dynamically refer to any such variable or parameter in your code, as dynamic code is executed after pushing the stack - those name references will be out of scope as a result. To "send" these names and values to the dynamic code, requires some kind of variable assignment. This has to be static. So back to square one again as the basic problem is that dynamic referencing of local variables and parameters is not possible.

    Assuming there is some kind of sane reason behind your question (forum jury still undecided on that ;-) ) - this can be done in a fashion using object types and dynamic referencing to properties of an instantiated object.
  • 6. Re: Obtain string of IN variable name in PL/SQL
    GauravBhide Newbie
    Currently Being Moderated
    Hi Chris,

    You can find parameters details from user_arguments data dictionary view.
    select argument_name from user_arguments where object_name = 'PROCEDURE_NAME';
  • 7. Re: Obtain string of IN variable name in PL/SQL
    chris001 Newbie
    Currently Being Moderated
    Thanks for the responses.

    There was no sane reason, I was asking out of pure curiosity. The procedure receives strings to those IN variables. If the IN var is not null, then the string is parsed and sent (with a number) to another procedure. The number to accompany the string was always the number from the IN var. I thought of each of the ways mentioned, the associative array, just hard coding. I ended up hard coding the numbers, because, well, they won't change.

    I know that accessing that variable name was not the best way, but the thought occurred to me to see if it was possible, so I figured I would face the wrath of the forum and see what others ideas were because I typically learn something either way from here.

    Thanks
  • 8. Re: Obtain string of IN variable name in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Here's an example of how (what you want to do using a procedure or function), can be done using a ADT/UDT (Advance/User Defined Type) in Oracle.

    This approach will not work for standard procedures and functions as this type of dynamic referencing of the code unit/object to itself, is not possible.
    SQL> create or replace type TSomeObject as object(
      2          name    varchar2(10),
      3          id      integer,
      4          day     date,
      5  
      6          member function PropertyByNumber( n integer ) return varchar2
      7  );
      8  /
    
    Type created.
    
    SQL> 
    SQL> create or replace type body TSomeObject as
      2  
      3          member function PropertyByNumber( n integer ) return varchar2 is
      4                  PLSQL_PROP_GET  constant varchar2(1000) :=
      5                          'declare
      6                                  obj     TSomeObject;
      7                          begin
      8                                  obj := :1;
      9                                  :2 := to_char( obj.#PROPERTY# );
     10                          end;';
     11  
     12                  type            TStrings is table of varchar2(30);
     13                  property        TStrings;
     14                  dynamicBlock    varchar2(1000);
     15                  res             varchar2(4000);
     16          begin
     17                  select
     18                          a.attr_name bulk collect into property
     19                  from    user_type_attrs a
     20                  where   a.type_name = 'TSOMEOBJECT'
     21                  order by
     22                          a.attr_no;
     23  
     24                  dynamicBlock := replace( PLSQL_PROP_GET, '#PROPERTY#', property(n) );
     25                  execute immediate dynamicBlock
     26                  using   in self,
     27                          out res;
     28  
     29                  return( res );
     30          end;
     31  
     32  end;
     33  /
    
    Type body created.
    
    SQL> 
    SQL> declare
      2          obj     TSomeObject;
      3  begin
      4          obj := new TSomeObject( 'John Doe', 123, trunc(sysdate) );
      5  
      6          for i in 1..3 loop
      7                  dbms_output.put_line( 'property '||i||'='||obj.PropertyByNumber(i) );
      8          end loop;
      9  end;
     10  /
    property 1=John Doe
    property 2=123
    property 3=2012/11/23 00:00:00
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    Simplistic example (the PropertyByNumber could be a static class method defined in the abstract parent class) - and one that requires the object to essentially duplicate itself via a bind variable call to dynamic code. Not really the best of approaches, but demonstrates the flexibility (to do even interestingly weird stuff) in PL/SQL.
  • 9. Re: Obtain string of IN variable name in PL/SQL
    chris001 Newbie
    Currently Being Moderated
    Thanks Billy, that was really cool.
  • 10. Re: Obtain string of IN variable name in PL/SQL
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Well , looking at the superfluous bulk collection - why not simply a single row fetch using property number as predicate?

    So the code is shoddy. Apologies.

    :-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points