1

I want to pass a list of char as one parameter to my procedure, I learned that I may use varray. but do I have to declare a varray instance before pass it to my procedure? What I want is something like this:

My_Procedure(['a','b','c','d','e'])

I can pass a list of array directly into the procedure. but what I learned is I have to do like this

create type my_type as  varray of varchar;
declare 
    my_array My_Arraytype;
begin
    my_array(1) := 'a';
    my_array(2) := 'b';
    my_array(3) := 'c';
    my_array(4) := 'd';

    my_procedure(my_array)
end;

Or is there any way other than varray?

Thanks

Kerui Cao
  • 53
  • 5
  • See this link https://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure – Saeideh miri Nov 07 '20 at 18:15
  • The type can also be declared in a package. – William Robertson Nov 07 '20 at 18:18
  • 2
    By the way, your varray is missing a limit, e.g. `create type my_type as varray(42) of varchar2(1);` I have rarely found a use for this, and varrays have reduced functionality compared to nested table collections (`type indicator_t as table of varchar2(1)`, so I'd recommend using those instead. – William Robertson Nov 07 '20 at 18:24
  • What is the problem with declaring the collection you want? You could leverage a collection declared in one of the Oracle supplied packages-- the dbms_sql package has a number of them. https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDCIIBF But it would generally make more sense to declare and use a collection type that you control. – Justin Cave Nov 07 '20 at 18:53
  • @WilliamRobertson - varrays are quite different from nested tables, even more so by definition (as compared to Oracle implementation). varrays are ordered, nested tables aren't - and to take advantage of Oracle's stupid implementation, where they *expose* array indices of nested tables (thankfully, only in PL/SQL, not also in plain SQL), to make nested tables *ordered* is quite a bad practice. If order has meaning in the OP's problem, then he **should** use varrays, not nested tables. –  Nov 08 '20 at 01:15
  • Is the order of individual strings meaningful in your procedure? Or can they be in any order? (In my answer I intentionally gave an example of a procedure like yours, in which the order of members is meaningful - the output from the procedure depends on the order of individual strings, not just on the "set" or "collection" of strings.) –  Nov 08 '20 at 01:28
  • @mathguy I've never understood what the manual means by varrays being ordered or the part about accessing the elements sequentially, because `my_type('Z','A')` doesn't rearrange itself as `('A','Z')`. I suppose it must be something that happens when you select from a column which is a collection type, under certain conditions which I have not been able to reproduce and hope never to need. – William Robertson Nov 08 '20 at 11:03
  • @WilliamRobertson - By definition, for a nested table there is no concept of "rearrange". Oracle implements nested tables as arrays, but the user shouldn't be able to see how Oracle implements an abstract data type. Taking the count of a nested table should be a valid operation; attempting to take the first element, or the last, or the "n-th", should throw an error. –  Nov 08 '20 at 15:15
  • @mathguy I see what you mean - a nested table *column in a database table* is an unordered set, implemented as a secondary table whose rows are of course in no particular order, while a varray *column* is stored within the row as some kind of encoded string, which I now realise explains what the manual means by *ordered*. Thanks! However this distinction is meaningless in the OP's case where `my_array` is simply a program variable and there's no sense in which a varray *variable* is any more "ordered" or suitable for "sequential access" than a nested table *variable*. – William Robertson Nov 08 '20 at 16:37

2 Answers2

3

You can create your own data type, as MT0 suggests, but you can also use data types provided by Oracle already. For example, sys.odcivarchar2list is a predefined varray of varchar2 type.

Below I show a procedure that accepts this data type as argument, and then I show how it can be invoked.

create or replace procedure my_procedure(str_list sys.odcivarchar2list)
as
  l_str varchar2(30000);
begin
  for i in 1 .. str_list.count loop
    l_str := case when i > 1 then l_str || ' ' end || str_list(i);
  end loop;
  dbms_output.put_line(l_str);
end;
/



exec my_procedure(sys.odcivarchar2list('eenie','meenie','miney','mo'))


eenie meenie miney mo


PL/SQL procedure successfully completed.

Either way, though, if the procedure must receive a dynamic number of arguments, that means you must use a collection type (either system-defined or your own), and you must use a constructor when you call the procedure (or outside the procedure - create an instance of your collection type, populate it, and pass it to the procedure; but you still must call the constructor somewhere). You can't simply give the individual strings and expect Oracle to treat that the same as passing the collection of strings.

0

Create a collection:

CREATE TYPE string_list IS TABLE OF VARCHAR2(10);

Then you can populate the collection as you instantiate it and do not need to assign it to a variable:

BEGIN
  my_procedure( string_list( 'A', 'B', 'C', 'D' ) );
END;
/
MT0
  • 143,790
  • 11
  • 59
  • 117