Using variables of schema defined type in Oracle Stored Procedure

1
votes

I have created a type in Oracle using 

create or replace type array_type 
as varray(6) of varchar2(20);

Now I want to use this as a variable in my stored procedure. I tried using it as below, 

create or replace PROCEDURE test_proc()
IS
my_type array_type;
BEGIN
....

This gives me error

Error(124,24): PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got ARRAY_TYPE

How do I use this custom type as a variable in my stored procedure?

2
oracleoracle-sqldeveloper
Oracle has three collection types-- nested tables, associative arrays, and varrays. 99.99% of the time, you want to use a nested table or an associative array. I imagine there may be a situation where you'd prefer a varray but I've never seen one and I'm hard pressed to imagine one. I'd actually be really interested if someone ever came up with a use case that preferred a varray. Which is a round about way of saying that unless this is a homework assignment to specifically use a varray, you probably want a different collection type. - Justin Cave
@JustinCave I want to create a dynamic where condition based on an input switch. So I want an array like (‘a’, ‘b’, ‘c’) and then use in my query as : select * from tblName where tblColumn IN array. If there is a better way to do this, please tell - Silencer310

2 Answers

1
votes

Not sure about the error, but you will have a problem with the parens in your proc definition.

This should work.

create or replace type array_type 
as varray(6) of varchar2(20);

create or replace PROCEDURE test_proc
IS
my_type array_type;
BEGIN 
  NULL;
END;
1
votes

It would be much more conventional to use a nested table type

create table tblFoo( 
  col1 varchar2(10)
);

insert into tblFoo values( 'a' );
insert into tblFoo values( 'b' );
insert into tblFoo values( 'c' );
insert into tblFoo values( 'd' );

create type nt_type as table of varchar2(10);
/

declare
  l_nt nt_type := nt_type( 'a', 'd', 'f' );
begin
  for i in (select col1
              from tblFoo
             where col1 MEMBER OF l_nt)
  loop
    dbms_output.put_line( i.col1 );
  end loop;
end;
/