Search This Blog

how to pass table type to procedure in oracle

example:
CREATE OR REPLACE PACKAGE xxabc AS

TYPE qty_compl_t IS RECORD(
action VARCHAR2(30) ,
production_order VARCHAR2(30) ,
qty_completed NUMBER ,
operation_code NUMBER ,
production_card VARCHAR2(30) ,
shift VARCHAR2(10) ,
scrap NUMBER ,
resource_code VARCHAR(30) ,
time_usage NUMBER ,
operator VARCHAR2(200) ,
reg_date DATE ,
crate_id VARCHAR2(200));

TYPE qty_com_table_t IS TABLE OF qty_compl_t INDEX BY BINARY_INTEGER;

PROCEDURE qtycom (
p_prod_no IN VARCHAR2 ,
p_qty_com_table IN qty_com_table_t
);

END;
/

CREATE OR REPLACE PACKAGE BODY xxabc AS

PROCEDURE qtycom (
p_prod_no IN VARCHAR2 ,
p_qty_com_table IN qty_com_table_t ) IS

BEGIN

dbms_output.put_line('p_prod_no :' || p_prod_no);
dbms_output.put_line('p_qty_com_table.count :' || p_qty_com_table.count);

NULL;

END;

END; /

DECLARE

l_tab xxabc.qty_com_table_t;

BEGIN

l_tab(1).action := 'hello';
l_tab(2).action := 'world';

xxabc.qtycom('value', l_tab);

END;


No comments:

Post a Comment