Sunil Kumar Sunil Kumar - 11 days ago 5
SQL Question

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.

Answer

You have some errors in your code;

CREATE OR REPLACE PACKAGE mypackage AS
    TYPE custom1 IS TABLE OF INTEGER
        INDEX BY BINARY_INTEGER;
END mypackage;

CREATE OR REPLACE PROCEDURE MYPROCEDURE(param1 IN mypackage.custom1, count1 OUT INTEGER) IS
BEGIN
    count1    := param1.COUNT();
END MYPROCEDURE;

To call your procedure, you simply need to define two variables and call the procedure with them; for example, in an anonymous block:

declare
    v mypackage.custom1;
    n number;
begin
    select 1
    bulk collect into v
    from dual connect by level <= 5;    
    --
    MYPROCEDURE(v, n);
    dbms_output.put_line('n= ' || n);
end;

n= 5

The same way, you can build your stored procedures, packages, ... to call your procedure.

Comments