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

comments are welcome