Advertisements

Tuesday, June 11, 2013

Populating type table

El siguiente script de PL SQL muestra un ejemplo de como llenar una colección de tipo tabla, en la cual se definen puros tipos de datos simples.
DECLARE

  V_LIST AP_OTM_IF_TBL_T := AP_OTM_IF_TBL_T();
  V_LINE1 AP_OTM_IF_T :=    AP_OTM_IF_T(1,1,'n',SYSDATE,SYSDATE,'N','N','N',1,'N','N',1,SYSDATE,'N','N',1,1,'N','N',1,SYSDATE,1,1,SYSDATE,'N',1,1);

  V_RES CHAR(20) :='N';
BEGIN
  V_LIST.extend();
  V_LIST(V_LIST.count()) := V_LINE1;
  DBMS_OUTPUT.PUT_LINE('COUNT = ' || V_LIST.count());
  SOA_AP_INVOICES_IF_INPUT.SEND_VOUCHER(V_LIST,V_RES);
   
DBMS_OUTPUT.PUT_LINE('V_RESULT = ' || V_RES);

END;


Este es otro ejemplo de como poblar un objeto de tipo tabla usando BULK COLLECT INTO.
set serveroutput on;

DECLARE
  V_DATE_FORMAT varchar2(50) := 'DD-MON-YYYY';
  v_date varchar2(50) := '11-DEC-2013';
  v_eexp_list SWC_FA_EEXP_IFACE_TBL_T := SWC_FA_EEXP_IFACE_TBL_T();
BEGIN

select 
SWC_FA_EEXP_IFACE_T (ADMIN_REVIEWED,    
  APPROVER_TEMPLATE_PHASE,   
  APP_ID,         
  BRIEF_DESC,  
  CHANGED_BY,  
  COST_CENTER,   
  DATE_CHANGED,           
  DATE_CREATED,           
  DIVISION,    
  DIV_NAME,   
  DIV_ID,         
  LONG_DESC, 
  ORIGINATING_LOCATION,  
  PRIME_ACCOUNT,   
  PRIOR_REQUEST,    
  PRIOR_REQUEST_AMT,         
  PROJECT,   
  REQUEST_ID,         
  REQUEST_STATUS,    
  REVISED_TOTAL_AMT,         
  SENT_TO_ONBASE,           
  SITE_CITY_OTHER,   
  SITE_ID,         
  SITE_NUM_OTHER,   
  SITE_STATE_OTHER,    
  SUB_ACCOUNT,    
  SUPPORT_MAILED,    
  TOTAL_COST_AMT ,         
  DATE_APPROVED)
BULK COLLECT INTO v_eexp_list from swc_fa_eexp_v where TO_CHAR(date_created,V_DATE_FORMAT) = v_date and request_status IN ('A', 'D');
  dbms_output.put_line('COUNT: ' || v_eexp_list.count());

END;