10 Replies Latest reply: Nov 26, 2012 12:05 PM by Billy~Verreynne RSS

    Obtain string of IN variable name in PL/SQL

    chris001
      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
          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
            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
              >
              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
                <Irrelevant - Deleted>

                Edited by: jeneesh on Nov 21, 2012 9:16 AM
                • 5. Re: Obtain string of IN variable name in PL/SQL
                  Billy~Verreynne
                  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
                    Gaurav Bhide
                    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
                      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
                        Billy~Verreynne
                        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
                          Thanks Billy, that was really cool.
                          • 10. Re: Obtain string of IN variable name in PL/SQL
                            Billy~Verreynne
                            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.

                            :-)