From: yogeshpalrecha@gmail.com   
      
   Trying to run this ETL code in Oracle. Not sure if this is a good design or   
   not. We are trying to build an ETL framework in Oracle which can handle   
   parallel processing and exception handling. Trying to run this ETL code in   
   Oracle. Not sure if this is a    
   good design or not.   
      
   create table prc_run_config (              
   prc_run_config_id   INTEGER,   
   prc_name            VARCHAR2(100),   
   prc_start_range     INTEGER,   
   prc_end_range       INTEGER,   
   prc_created_time    TIMESTAMP   
   );                         
                              
   create table prc_run_log    (              
   prc_run_log_id      INTEGER,   
   prc_run_config_id   INTEGER,   
   prc_start_time      TIMESTAMP,   
   prc_end_time        TIMESTAMP,   
   prc_status          VARCHAR2(100),   
   prc_run_sql         VARCHAR2(4000),   
   prc_run_msg         VARCHAR2(4000)   
   );   
      
   create table PARTY_LKP  (              
   party_id            INTEGER,   
   party_num           VARCHAR2(100)   
   )   
      
   DECLARE   
   BEGIN   
       FOR i IN 1..100   
       LOOP   
           INSERT INTO PARTY_LKP VALUES (i,SYS_GUID());   
       END LOOP;   
   END;   
   /   
   select rownum,party_id,party_num from party_lkp   
      
   --proc_gen_run_config (prc_name, parallel_threads)   
   DECLARE   
       v_total INT;   
       v_start_range INT;   
       v_end_range INT;   
   BEGIN   
       SELECT count(*) INTO v_total FROM PARTY_LKP;   
       FOR i IN 1..10   
       LOOP   
           IF i = 1 THEN   
               v_start_range := 1;   
               v_end_range := v_total/10;   
           ELSE   
               v_start_range := v_end_range+1;   
               v_end_range := v_start_range + v_total/10 - 1;   
           END IF;   
           INSERT INTO prc_run_config VALUES (i,'LOAD_CORE_TBL',v_s   
   art_range,v_end_range,SYSTIMESTAMP);   
       END LOOP;   
   END;   
   /   
      
   truncate table prc_run_config   
   select * from prc_run_config   
      
   --proc_run_prc (prc_run_config_id)   
   DECLARE   
       v_start_range INT;   
       v_end_range INT;   
       v_party_num VARCHAR2(100);   
       v_sql_error VARCHAR2(1000);   
   BEGIN   
       SELECT prc_start_range,prc_end_range INTO v_start_range,v_end_range FROM   
   PRC_RUN_CONFIG WHERE prc_run_config_id=1;   
       INSERT INTO prc_run_log VALUES (1,1,SYSTIMESTAMP,NULL,'START   
   D',NULL,'Processing started');   
       FOR i IN v_start_range..v_end_range   
       LOOP   
           SELECT party_num INTO v_party_num FROM PARTY_LKP WHERE party_id = i;   
           DBMS_OUTPUT.PUT_LINE('Processing party ' || v_party_num ||   
   '...');           
       END LOOP;   
       UPDATE prc_run_log SET prc_end_time=SYSTIMESTAMP, prc_status='COMPLETED',   
   prc_run_msg='Processing completed' WHERE prc_run_log_id=1;   
   EXCEPTION   
       WHEN OTHERS THEN   
       DBMS_OUTPUT.PUT_LINE (SQLERRM);   
       v_sql_error := SQLERRM;   
       UPDATE prc_run_log SET prc_end_time=SYSTIMESTAMP, prc_status='FAILED',   
   prc_run_msg=v_sql_error WHERE prc_run_log_id=1;   
   END;   
   /   
      
   SELECT rownum,party_num FROM PARTY_LKP WHERE rownum = 2;   
      
   truncate table prc_run_log   
   select * from prc_run_log   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)