Search code examples
sqloracle-databasestored-proceduresprocedure

How to call procedure with package type param in oracle?

In oracle DB, I created a custom type in a package and i guess this type is similar to integer array.

create or replace PACKAGE mypackage AS 
   TYPE custom1 is table of integer index by binary_integer;
END mypackage;

Used type in procedure IN param and expecting out param to be size of IN param.

CREATE OR REPLACE PROCEDURE MYPROCEDURE( param1 in mypackage.custom1, count1 out integer) IS
begin
count1 := param.count();
END MYPROCEDURE

Now I want to call above procedure,for this I should prepare mypackage.custom1.

Please help me in constructing mypackage.custom1 and call above procedure.

Solution

  • Executing above procedure with list of integers passing to custom type

    SET SERVEROUTPUT = ON;
declare
    v mypackage.custom1;
    n number;
begin
    v(0) := 10;
    v(1) := 12;
    v(2) := 14;
    v(3) := 16;  
    --
    MYPROCEDURE(v, n);
    dbms_output.put_line('n= ' || n);
end;

    output :

    n = 4