Home » SQL & PL/SQL » SQL & PL/SQL » Pass type of an Array to a procedure
Pass type of an Array to a procedure [message #188594] Sun, 20 August 2006 08:48
stormsmoker
Messages: 1
Registered: August 2006
Location: Switzerland
Junior Member
Hi all,
I have to catch the records which are throwing exceptions (duplicates, constraints, ...) from our data loads and write them into a table.
I tried to realise it with merge statements but unfortunately we are using 10.1.x and the Oracle exception feature with merge is only available since 10.2.

So I realised it now with the FORALL loop to perform the insert stmt against a collection. It worked fine and was also fast..!


Because there are many inserts in many loads I tryed to create a generic procedure. It would be nice to pass the cursor and the target table (and perhaps the limit).
Otherwise it isnt so clear and a mess to maintain.


Already tried to pass CURSER, REF CURSER or Array but had different problems for any method..



Thanks a lot for your effort!
Regards, ksu



PACKAGE BODY BIQ_PG_TRANSFER
as
  procedure transfer_exception_handling;
  TYPE rc IS REF CURSOR;
  procedure transfer_generic_except_handl(bulk_limit in number, cursor1 in rc);
  
  

---------------------------------------------------
-- test procedure transfer_exception_handling
---------------------------------------------------
procedure transfer_exception_handling
is
  cursor1 rc;
  

begin
    biq_pg_utils.set_log_level(GP_LOG_VERBOSE);


  OPEN cursor1 FOR
    SELECT  null price_id, sysdate known_at_time, A.sec_rate_provider_id,
            A.ubs_rate_type_code, A.validity_start_ts, A.sec_rate_value,
            A.sec_rate_unit_ccy, null sec_rate_corr_fact, A.instrument_id, A.trading_place_gk
    FROM BIQ_V_IMPORT_ICE_PRICE A
    where not exists (
       SELECT 'X'
       FROM biq_live.biq_v_price LIVE2
       where A.research_id not in (select distinct research_id from BIQ_T_ICE_MSCI_MAPPING) AND
       A.instrument_id = LIVE2.INSTRUMENT_ID AND
          A.trading_place_gk = LIVE2.TRADING_PLACE_GK AND
          A.validity_start_ts = LIVE2.VALIDITY_START_TS AND
          sysdate = LIVE2.KNOWN_AT_TIME AND
          A.ubs_rate_type_code = LIVE2.UBS_RATE_TYPE_CODE AND
          A.sec_rate_unit_ccy = LIVE2.sec_rate_unit_ccy AND
          A.sec_rate_provider_id = LIVE2.sec_rate_provider_id
    );

    transfer_generic_except_handl(1000, cursor1);
    
end;




---------------------------------------------------
-- test procedure transfer_exception_handling
---------------------------------------------------
procedure transfer_generic_except_handl(bulk_limit in number, cursor1 in rc)
is

  v_inserts integer := 0;

  TYPE a_recs IS TABLE OF BIQ_LIVE.BIQ_V_PRICE%ROWTYPE; -- this tablename here should be dinamically
  v_recs a_recs;
  

begin
  biq_pg_utils.set_log_level(GP_LOG_VERBOSE);

  
  LOOP
    EXIT WHEN cursor1%NOTFOUND;

    FETCH cursor1 BULK COLLECT INTO v_recs LIMIT 10000;


    BEGIN
      FORALL i IN v_recs.FIRST..v_recs.LAST
        SAVE EXCEPTIONS
        INSERT INTO BIQ_LIVE.BIQ_V_PRICE
        VALUES v_recs(i);

    EXCEPTION
      WHEN OTHERS THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
          biq_pg_utils.log(GP_LOG_INFO, 'Found an error at ' ||
                SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                ' Error Msg: ' ||
                SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE), 'EXCEPTION');

          biq_pg_utils.log(GP_LOG_INFO,'Instrm_id: ' || v_recs(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).instrument_id || ', Price_value: ' || v_recs(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).sec_rate_value || ', ...', 'EXCEPTION');

        END LOOP;
    END;

  END LOOP;

v_inserts := cursor1%ROWCOUNT;
CLOSE cursor1;
--commit;
biq_pg_utils.log(GP_LOG_INFO,'Inserted ' || v_inserts || ' new MSCI Prices with FORALL loop', 'EXCEPTION');
rollback;

end;
    
  
END;




Previous Topic: Converting a table to be a partition of a partitioned table
Next Topic: Error while accessing External tables
Goto Forum:
  


Current Time: Mon Dec 05 09:12:19 CST 2016

Total time taken to generate the page: 0.13643 seconds