Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Parameter List (Oracle, 12.2.0.1.0, Linux)
Procedure Parameter List [message #686502] Wed, 28 September 2022 08:19 Go to next message
Duane
Messages: 496
Registered: December 2002
Senior Member
I have a feeling the answer is going to be No but I thought I would ask anyway in case there is some object that does do this.

Question. Is there an object or way to loop through the parameter list of a procedure? If my procedure has 5 parameters is there a way to loop through all five?

So, you're asking yourself, why would I want to do that? Each parameter has been encoded to Base64 format so I need to convert each one.


procedure FileUpload (Param1 in varchar2,
                      Param2 in varchar2,
                      Param3 in varchar2,
                      Param4 in varchar2,
                      Param5 in varchar2) is

  begin
    for i in ParamList
      loop
        Do something;
      end loop;

  end;

Re: Procedure Parameter List [message #686503 is a reply to message #686502] Wed, 28 September 2022 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No but you can do it using a SYS.ODCIVARCHAR2LIST as single parameter; this will also allow you to use a variable number of values.

Re: Procedure Parameter List [message #686511 is a reply to message #686502] Fri, 30 September 2022 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Re: Procedure Parameter List [message #686514 is a reply to message #686511] Fri, 30 September 2022 14:21 Go to previous messageGo to next message
Duane
Messages: 496
Registered: December 2002
Senior Member
I haven't had the time to look into what you are suggesting. I take that back. While in a meeting today, I did look up SYS.ODCIVARCHAR2LIST to see how I could use it. That was it and I had to jump back to the meeting. I'll see if I can look into it next week to see how I can make it work for me.
Re: Procedure Parameter List [message #686515 is a reply to message #686514] Fri, 30 September 2022 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just like that:
SQL> CREATE OR REPLACE PROCEDURE p (v IN SYS.ODCIVarchar2List)
  2  IS
  3    i PLS_INTEGER;
  4  BEGIN
  5    i := v.FIRST;
  6    LOOP
  7      EXIT WHEN i IS NULL;
  8      dbms_output.put_line('Parameter '||i||' is '||v(i));
  9      i := v.NEXT(i);
 10    END LOOP;
 11  END;
 12  /

Procedure created.

SQL> exec p(SYS.ODCIVarchar2List('First','Second','Third'))
Parameter 1 is First
Parameter 2 is Second
Parameter 3 is Third

PL/SQL procedure successfully completed.
Re: Procedure Parameter List [message #686516 is a reply to message #686515] Fri, 30 September 2022 14:39 Go to previous messageGo to next message
Duane
Messages: 496
Registered: December 2002
Senior Member
That seemed easy. What if one of the parameters is a CLOB?
Re: Procedure Parameter List [message #686517 is a reply to message #686516] Fri, 30 September 2022 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SYS.ODCIVARCHAR2LIST is defined as Varying Array(32767) of VARCHAR2(4000).
If your CLOB is smaller than 4000 then no problem to use it.
Otherwise, nothing prevents you from creating your own type as VARRAY of VARCHAR2(32767) or of CLOB although there may be some subtleties to use this later one... to test.

Re: Procedure Parameter List [message #686518 is a reply to message #686517] Fri, 30 September 2022 16:17 Go to previous message
Duane
Messages: 496
Registered: December 2002
Senior Member
I'll try playing around with a CLOB version. Kind of neat stuff. Thanks again.
Previous Topic: ANSI JOIN requirement
Next Topic: Columns replicate
Goto Forum:
  


Current Time: Wed Dec 07 10:39:51 CST 2022