Wednesday, March 6, 2013

How to query collections types in PL SQL

Tenemos definido los siguientes objetos type:


 CREATE OR REPLACE TYPE "SOAADM"."AP_INVOICES_IF_T" AS OBJECT (
 INVOICE_ID NUMBER,
 INVOICE_NUM VARCHAR2(50 BYTE),
 INVOICE_TYPE_LOOKUP_CODE VARCHAR2(25 BYTE),
 INVOICE_DATE DATE,
 VENDOR_ID NUMBER
 )

 CREATE OR REPLACE TYPE "SOAADM"."AP_INVOICES_IF_TBL_T" AS TABLE OF AP_INVOICES_IF_T;


Ahora probamos con el siguiente script como saber si se pobló de manera correcta:
declare
  v_ap_if_tbl AP_INVOICES_IF_TBL_T;
  v_ap_if_t AP_INVOICES_IF_T;
  v_count number;
begin
  v_ap_if_t.INVOICE_ID:=1;
  v_ap_if_tbl := AP_INVOICES_IF_TBL_T(v_ap_if_t);
  select count(*) into v_count from table(cast(v_ap_if_tbl as AP_INVOICES_IF_TBL_T);
  dbms_output.put_line('length: '||v_count);
end;

No comments:

Post a Comment