CODEWITHSUNDEEP
sqloracle-database
Kerui Cao
Kerui Cao

Reputation: 53

How do I pass a list of varchar to procedure in Oracle?

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

Upvotes: 1

Views: 2996

Answers (2)

user5683823
user5683823

Reputation:

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.

Upvotes: 4

MT0
MT0

Reputation: 167774

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;
/

Upvotes: 0

Related Questions