Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ORACLE 9.2.0 OLAP: Running .PLS files

Posted on 2003-03-19
7
Medium Priority
?
2,762 Views
Last Modified: 2007-12-19
I have created an OLAP cube for which I need to populate the Dimensions Tables by using the .PLS files created by Oracle Warehouse Builder.

My question is: How do I run those .PLS files?

The PLS files are actually intended to take the data from 1 schema's tables and insert into the Dimension tables that have been created in the 2nd schema.

0
Comment
Question by:ashish_me2
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:bkowalski
ID: 8174608
It's possible that they can be run using SQL Plus.  Can you post one of the .pls files?
0
 

Author Comment

by:ashish_me2
ID: 8178676
Here's one of the .PLS files

/*****************************************************************************
--  Oracle Warehouse Builder
--  Generator Version           : 9.0.3.33.0
--  Minimum Runtime Repository
--   Version Required           : 9.0.3.0.1
--  Created Date                : Thu Mar 20 11:13:11 IST 2003
--  Modified Date               : Thu Mar 20 11:13:11 IST 2003
--  Created By                  : olap_dev
--  Modified By                 : olap_dev
--  Generated Object Type       : PL/SQL Package
--  Generated Object Name       : PERIODS_IN
*****************************************************************************/
--  Copyright(c) 1999-2002 Oracle Corporation.

CREATE OR REPLACE PACKAGE PERIODS_IN AS

-- Auditing mode constants
AUDIT_NONE                    CONSTANT BINARY_INTEGER := 0;
AUDIT_STATISTICS              CONSTANT BINARY_INTEGER := 1;
AUDIT_ERROR_DETAILS           CONSTANT BINARY_INTEGER := 2;
AUDIT_COMPLETE                CONSTANT BINARY_INTEGER := 3;

-- Operating mode constants
MODE_SET                      CONSTANT BINARY_INTEGER := 0;
MODE_ROW                      CONSTANT BINARY_INTEGER := 1;
MODE_ROW_TARGET               CONSTANT BINARY_INTEGER := 2;
MODE_SET_FAILOVER_ROW         CONSTANT BINARY_INTEGER := 3;
MODE_SET_FAILOVER_ROW_TARGET  CONSTANT BINARY_INTEGER := 4;

-- Variables for auditing
get_runtime_audit_id          NUMBER(22) := 0;
get_audit_level               BINARY_INTEGER := AUDIT_ERROR_DETAILS;
get_cycle_date                CONSTANT DATE := SYSDATE;
get_model_id                  CONSTANT NUMBER(22) := 0;
get_model_name                CONSTANT VARCHAR2(40) := 'PERIODS_IN';
get_purge_group               CONSTANT VARCHAR2(40) := 'wb';

-- Processing variables
get_selected                  NUMBER(22) := 0;
get_inserted                  NUMBER(22) := 0;
get_updated                   NUMBER(22) := 0;
get_deleted                   NUMBER(22) := 0;
get_merged                    NUMBER(22) := 0;
get_errors                    NUMBER(22) := 0;
get_abort                     BOOLEAN    := FALSE;

get_max_errors                NUMBER(22) := 50;
get_commit_frequency          NUMBER(22) := 50;
get_operating_mode            BINARY_INTEGER := MODE_SET_FAILOVER_ROW;
check_record_cnt              NUMBER(22) := 0;
sql_stmt                      VARCHAR2(32767);
error_stmt                    VARCHAR2(2000);

-- Bulk processing variables
get_bulk_size                 NATURAL := 50;

-- DML Exceptions
checked_table_not_empty       EXCEPTION;
PRAGMA EXCEPTION_INIT(checked_table_not_empty, -111);
invalid_dml                   EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_dml, -112);

-- Status variable for Batch cursors
"PERIODS_St" BOOLEAN;

-- Bulk: types for collection variables
TYPE "T_TG_PERIOD_PERIOD_CODE" IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE "T_TG_PERIOD_PERIOD_DESC" IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
TYPE "T_PERIODS_PERIOD_CODE" IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE "T_PERIODS_PERIOD_DESCRIPTION" IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE "T_TG_PERIOD_PERIOD_CODE$0" IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE "T_TG_PERIOD_PERIOD_DESC$0" IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
TYPE "T_PERIODS_PERIOD_CODE$0" IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE "T_PERIODS_PERIOD_DESCRIPTION$0" IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

-- Bulk: collection variables
"TG_PERIOD_PERIOD_CODE" "T_TG_PERIOD_PERIOD_CODE";
"TG_PERIOD_PERIOD_DESC" "T_TG_PERIOD_PERIOD_DESC";
"PERIODS_PERIOD_CODE" "T_PERIODS_PERIOD_CODE";
"PERIODS_PERIOD_DESCRIPTION" "T_PERIODS_PERIOD_DESCRIPTION";
"TG_PERIOD_PERIOD_CODE$0" "T_TG_PERIOD_PERIOD_CODE$0";
"TG_PERIOD_PERIOD_DESC$0" "T_TG_PERIOD_PERIOD_DESC$0";
"PERIODS_PERIOD_CODE$0" "T_PERIODS_PERIOD_CODE$0";
"PERIODS_PERIOD_DESCRIPTION$0" "T_PERIODS_PERIOD_DESCRIPTION$0";

-- Procedure Main -- Entry point in package PERIODS_IN
PROCEDURE Main(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL') ;  

END PERIODS_IN;

/

CREATE OR REPLACE PACKAGE BODY PERIODS_IN AS


---------------------------------------------------------------------------
-- Function "PERIODS_Bat"
--   performs batch extraction
--   Returns TRUE on success
--   Returns FALSE on failure
---------------------------------------------------------------------------
FUNCTION "PERIODS_Bat"  RETURN BOOLEAN IS  
  batch_selected        NUMBER(22) := 0;
  batch_errors          NUMBER(22) := 0;
  batch_inserted        NUMBER(22) := 0;
  batch_merged          NUMBER(22) := 0;
  batch_action          VARCHAR2(12);
  actual_owner          VARCHAR2(30);
  actual_name           VARCHAR2(30);
  num_fk_err            NUMBER(22);
  l_rowkey              NUMBER(22) := 0;
  l_table               VARCHAR2(30) := 'CREATE';
  l_rowid               ROWID;
  l_owner               VARCHAR2(30);
  l_tablename           VARCHAR2(30);
  l_constraint          VARCHAR2(30);
  sql_excp_stmt         VARCHAR2(32767);
  batch_exception       BOOLEAN := FALSE;
  get_map_num_rows      NUMBER(22) := 0;
  TYPE exceptionsCurType IS REF CURSOR;
  exceptions_cursor     exceptionsCurType;
  "PERIODS_id" NUMBER(22) := 0;

BEGIN
  IF get_abort THEN
    RETURN FALSE;
  END IF;
  IF get_audit_level != AUDIT_NONE THEN
    "PERIODS_id" := WBPRTI.auditd_begin(
      get_runtime_audit_id,
      0,
      '"PERIODS_Bat" ',
      '*',
      '"PERIODS"',
      NULL,
      NULL
    );
  END IF;
  COMMIT;
 
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
 
  BEGIN  
   
    -- resolve actual owner and name
    WBPRTIU.resolve_name(USER, 'PERIODS', actual_owner, actual_name);
   
    IF NOT "PERIODS_St" THEN  
     
      batch_action := 'BATCH INSERT';
      INSERT
      /*+ APPEND PARALLEL("PERIODS", DEFAULT, DEFAULT)  */
      INTO
        "PERIODS"
        ("PERIOD_CODE",
        "PERIOD_DESCRIPTION")
        (SELECT
        /*+ NO_MERGE  */
          "TG_PERIOD_PERIODS"."PERIOD_CODE" "PERIOD_CODE",
          "TG_PERIOD_PERIODS"."PERIOD_DESC" "PERIOD_DESC"
        FROM
          "TG_PERIOD"@"PERIODS" "TG_PERIOD_PERIODS"
        );
      batch_inserted := SQL%ROWCOUNT;
      batch_selected := SQL%ROWCOUNT;
      IF get_errors + batch_errors > get_max_errors THEN
        get_abort := TRUE;
      END IF;
      COMMIT;
    END IF;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    batch_errors := batch_errors + 1;
    IF get_errors + batch_errors > get_max_errors THEN
      get_abort := TRUE;
    END IF;
    IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
      WBPRTI.error(
        get_runtime_audit_id,
        0,
        "PERIODS_id",
        0,
        '"PERIODS"',
        '*',
        NULL,
        batch_action,
        SQLCODE,
        SQLERRM,
        NULL
      );
    END IF;
    get_errors := get_errors + batch_errors;
    get_selected := get_selected + batch_selected;
    IF get_audit_level != AUDIT_NONE THEN
      WBPRTI.auditd_end(
        p_rtd=>"PERIODS_id",
        p_sel=>batch_selected,
        p_ins=>NULL,
        p_upd=>NULL,
        p_del=>NULL,
        p_err=>batch_errors,
        p_dis=>NULL,
        p_mer=>NULL
      );
    END IF;
    COMMIT;
    batch_exception := TRUE;
  END;
 
  BEGIN  
    IF NOT batch_exception THEN  
      IF actual_name IS NOT NULL THEN  
        SELECT NVL(num_rows, 0) INTO get_map_num_rows
        FROM all_tables
        WHERE table_name = actual_name
        AND owner = actual_owner;
        IF (batch_inserted + batch_merged) >= get_map_num_rows THEN  
          DBMS_STATS.gather_table_stats(  
            ownname=>actual_owner,
            tabname=>actual_name,
            estimate_percent=>90,
            granularity=>'GLOBAL',
            degree=>null
          );
        END IF;
      END IF;
    END IF;
  EXCEPTION WHEN OTHERS THEN  
    IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
      WBPRTI.error(
        get_runtime_audit_id,
        0,
        "PERIODS_id",
        0,
        '"PERIODS"',
        '*',
        NULL,
        batch_action,
        SQLCODE,
        SQLERRM,
        NULL
      );
    END IF;
  END;
 
  IF batch_exception THEN  
    RETURN FALSE;
  END IF;
  get_inserted := get_inserted + batch_inserted;
  get_errors := get_errors + batch_errors;
  get_selected := get_selected + batch_selected;
  get_merged := get_merged + batch_merged;
  IF get_audit_level != AUDIT_NONE THEN
    WBPRTI.auditd_end(
      p_rtd=>"PERIODS_id",
      p_sel=>batch_selected,
      p_ins=>batch_inserted,
      p_upd=>NULL,
      p_del=>NULL,
      p_err=>batch_errors,
      p_dis=>NULL,
      p_mer=>batch_merged
    );
  END IF;
  COMMIT;
  RETURN TRUE;
END "PERIODS_Bat";


-- Procedure "TG_PERIOD_p" is the entry point for map "TG_PERIOD_p"

PROCEDURE "TG_PERIOD_p" IS
-- Constants for this map
get_map_name               CONSTANT VARCHAR2(40) := '"TG_PERIOD_p"';
get_source_name            CONSTANT VARCHAR2(80) := SUBSTR('"TG_PERIOD"@"PERIODS"',0,80);
get_step_number            CONSTANT NUMBER(22) := 1;

get_is_cursor_open         BOOLEAN := FALSE;
get_map_selected           NUMBER(22) := 0;
get_map_errors             NUMBER(22) := 0;
get_map_num_rows           NUMBER(22) := 0;

actual_owner               VARCHAR2(30);
actual_name                VARCHAR2(30);

-- Constraint management
num_fk_err                 NUMBER(22);
l_rowkey                   NUMBER(22) := 0;
l_table                    VARCHAR2(30) := 'CREATE';
l_rowid                    ROWID;
l_owner                    VARCHAR2(30);
l_tablename                VARCHAR2(30);
l_constraint               VARCHAR2(30);
sql_excp_stmt              VARCHAR2(32767);
TYPE exceptionsCurType IS REF CURSOR;
exceptions_cursor          exceptionsCurType;

normal_action              VARCHAR2(20);
error_action               VARCHAR2(20);
get_audit_detail_id        NUMBER(22) := 0;
get_target_name            VARCHAR2(80);
error_column               VARCHAR2(80);
error_value                VARCHAR2(2000);


-- Scalar variables for auditing
"PERIODS_id" NUMBER(22) := 0;
"PERIODS_ins" NUMBER(22) := 0;
"PERIODS_upd" NUMBER(22) := 0;
"PERIODS_del" NUMBER(22) := 0;
"PERIODS_err" NUMBER(22) := 0;

-- Variables for auditing in bulk processing
one_rowkey            NUMBER(22) := 0;
get_rowkey            NUMBER(22) := 0;
get_rowkey_bulk       WBPRTI.NUMBERLIST;
one_rowid             ROWID;
get_rowid             WBPRTI.ROWIDLIST;
rowkey_bulk_index     NUMBER(22) := 0;
x_it_err_count        NUMBER(22) := 0;

"PERIODS_srk" WBPRTI.NUMBERLIST;

-- Bulk processing
error_index                NUMBER(22);
update_bulk                WBPRTI.NUMBERLIST;
update_bulk_index          NUMBER(22) := 0;
insert_bulk_index          NUMBER(22) := 0;
last_successful_index      NUMBER(22) := 0;
feedback_bulk_limit        NUMBER(22) := 0;

"TG_PERIOD_si" NUMBER(22) := 0;
"TG_PERIOD_i" NUMBER(22) := 0;
"PERIODS_si" NUMBER(22) := 0;
"PERIODS_i" NUMBER(22) := 0;

-- Cursor declaration
CURSOR "TG_PERIOD_c" IS
  SELECT
  /*+ NO_MERGE  */
    "TG_PERIOD_PERIODS"."PERIOD_CODE" "PERIOD_CODE$0",
    "TG_PERIOD_PERIODS"."PERIOD_DESC" "PERIOD_DESC$0"
  FROM
    "TG_PERIOD"@"PERIODS" "TG_PERIOD_PERIODS";

---------------------------------------------------------------------------
-- This procedure records column values of one erroneous source row
-- into an audit trail table named WB_RT_ERROR_SOURCES.  Each column is
-- recorded by one row in the audit trail.  To collect all source column
-- values corresponding to one erroneous source row, query the audit
-- trail and specify:
--    RTA_IID, uniquely identifies one audited run,
--    RTE_ROWKEY, uniquely identifies a source row within and audited run
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_ES"(error_index IN NUMBER) IS
BEGIN
  WBPRTI.error_source(
    get_runtime_audit_id,
    get_rowkey + error_index - 1,
    1,
    1,
    SUBSTR('"TG_PERIOD"@"PERIODS"',0,80),
    SUBSTR('TG_PERIOD_PERIOD_CODE',0,80),
    SUBSTR("TG_PERIOD_PERIOD_CODE"(error_index),0,2000),
    get_step_number
  );
  WBPRTI.error_source(
    get_runtime_audit_id,
    get_rowkey + error_index - 1,
    2,
    1,
    SUBSTR('"TG_PERIOD"@"PERIODS"',0,80),
    SUBSTR('TG_PERIOD_PERIOD_DESC',0,80),
    SUBSTR("TG_PERIOD_PERIOD_DESC"(error_index),0,2000),
    get_step_number
  );
  RETURN;
END "TG_PERIOD_ES";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_ER" registers error for one errorneous row
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_ER"(p_statement IN VARCHAR2, p_column IN VARCHAR2, p_col_value IN VARCHAR2, p_sqlcode IN NUMBER, p_sqlerrm IN VARCHAR2, p_auditd_id IN NUMBER, p_error_index IN NUMBER) IS
l_source_target_name VARCHAR2(80);
BEGIN  
  IF p_auditd_id IS NULL THEN  
    l_source_target_name := get_source_name;
  ELSE  
    l_source_target_name := get_target_name;
  END IF;
  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
    WBPRTI.error(  
      get_runtime_audit_id,
      get_step_number,
      p_auditd_id,
      get_rowkey + p_error_index - 1,
      l_source_target_name,
      p_column,
      p_col_value,
      p_statement,
      p_sqlcode,
      p_sqlerrm,
      NULL
    );
  END IF;
 
  IF get_audit_level = AUDIT_COMPLETE THEN  
    WBPRTI.register_feedback(  
      get_runtime_audit_id,
      get_step_number,
      get_rowkey + p_error_index - 1,
      'ERROR',
      l_source_target_name,
      'T',
      SUBSTR(p_statement,0,30)
    );
  END IF;
 
  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
    "TG_PERIOD_ES"(p_error_index);
  END IF;
END "TG_PERIOD_ER";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_SU" opens and initializes data source
-- for map "TG_PERIOD_p"
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_SU" IS
BEGIN
  IF get_abort THEN
    RETURN;
  END IF;
  OPEN "TG_PERIOD_c";
  get_is_cursor_open := TRUE;
END "TG_PERIOD_SU";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_RD" fetches a bulk of rows from
--   the data source for map "TG_PERIOD_p"
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_RD" IS
BEGIN  
  IF get_abort THEN  
    RETURN;
  END IF;
 
  BEGIN  
    "TG_PERIOD_PERIOD_CODE".DELETE;
"TG_PERIOD_PERIOD_DESC".DELETE;
   
    FETCH  
      "TG_PERIOD_c"
    BULK COLLECT INTO  
      "TG_PERIOD_PERIOD_CODE",
      "TG_PERIOD_PERIOD_DESC"
    LIMIT get_bulk_size;
   
    IF "TG_PERIOD_c"%NOTFOUND AND "TG_PERIOD_PERIOD_CODE".COUNT = 0 THEN  
      RETURN;
    END IF;
   
    -- register feedback for successful reads
    IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
      get_rowkey := WBPRTI.get_rowkey("TG_PERIOD_PERIOD_CODE".COUNT);
    END IF;
   
    IF get_audit_level = AUDIT_COMPLETE THEN  
      WBPRTI.register_feedback_bulk(  
        get_runtime_audit_id,
        get_step_number,
        get_rowkey,
        'NEW',
        get_source_name,
        'S',
        'SELECT',
        "TG_PERIOD_PERIOD_CODE".COUNT
      );
    END IF;
    get_map_selected := get_map_selected + "TG_PERIOD_PERIOD_CODE".COUNT;
  EXCEPTION  
    WHEN OTHERS THEN  
      -- register error
      IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
        one_rowkey := WBPRTI.get_rowkey;
        WBPRTI.error(
          get_runtime_audit_id,
          get_step_number,
          NULL,
          one_rowkey,
          get_source_name,
          '*',
          NULL,
          'SELECT',
          SQLCODE,
          SQLERRM,
          NULL
        );
      END IF;
     
      -- register feedback for the erroneous row
      IF get_audit_level = AUDIT_COMPLETE THEN
        WBPRTI.register_feedback(
          get_runtime_audit_id,
          get_step_number,
          one_rowkey,
          'ERROR',
          get_source_name,
          'S',
          'SELECT'
        );
      END IF;
      get_errors := get_errors + get_bulk_size - "TG_PERIOD_PERIOD_CODE".COUNT;
      IF get_errors > get_max_errors THEN  
        get_abort := TRUE;
      END IF;
  END;
END "TG_PERIOD_RD";


---------------------------------------------------------------------------
-- "TG_PERIOD_p" main
---------------------------------------------------------------------------

BEGIN  
  IF get_abort THEN  
    RETURN;
  END IF;
 
  sql_stmt := 'ALTER SESSION DISABLE PARALLEL DML';
  EXECUTE IMMEDIATE sql_stmt;
 
  IF NOT "PERIODS_St" THEN  
   
    IF get_audit_level != AUDIT_NONE THEN  
      IF NOT "PERIODS_St" THEN  
        "PERIODS_id" :=  
          WBPRTI.auditd_begin(get_runtime_audit_id,get_step_number,get_map_name,
          get_source_name,'"PERIODS"',NULL,NULL);
      END IF;
      COMMIT;
    END IF;
   
    "TG_PERIOD_si" := 0;
    "PERIODS_i" := 1;
   
    IF NOT get_abort THEN  
      "TG_PERIOD_SU";
     
      LOOP  
        IF "TG_PERIOD_si" = 0 THEN  
          "TG_PERIOD_RD";   -- Fetch data from source
        END IF;
       
        -- Processing:
        "TG_PERIOD_i" := "TG_PERIOD_si";
        BEGIN  
          LOOP  
            EXIT WHEN "PERIODS_i" > get_bulk_size OR get_abort;
           
            "TG_PERIOD_i" := "TG_PERIOD_i" + 1;
            "TG_PERIOD_si" := "TG_PERIOD_i";
           
            IF "TG_PERIOD_i" > "TG_PERIOD_PERIOD_CODE".COUNT THEN  
              "TG_PERIOD_si" := 0;
              EXIT;
            END IF;
           
            get_target_name := '"PERIODS"';
            get_audit_detail_id := "PERIODS_id";
            IF NOT "PERIODS_St" THEN  
              BEGIN  
               
                error_stmt := SUBSTR('"PERIODS_PERIOD_CODE"("PERIODS_i") :=  "TG_PERIOD_PERIOD_CODE"("TG_PERIOD_i"); ',0,2000);
                error_column := SUBSTR('"PERIODS_PERIOD_CODE"',0,80);
                error_value := SUBSTR("TG_PERIOD_PERIOD_CODE"("TG_PERIOD_i"),0,2000);
                "PERIODS_PERIOD_CODE"("PERIODS_i") := "TG_PERIOD_PERIOD_CODE"("TG_PERIOD_i");
               
                error_stmt := SUBSTR('"PERIODS_PERIOD_DESCRIPTION"("PERIODS_i") :=  "TG_PERIOD_PERIOD_DESC"("TG_PERIOD_i"); ',0,2000);
                error_column := SUBSTR('"PERIODS_PERIOD_DESCRIPTION"',0,80);
                error_value := SUBSTR("TG_PERIOD_PERIOD_DESC"("TG_PERIOD_i"),0,2000);
                "PERIODS_PERIOD_DESCRIPTION"("PERIODS_i") := "TG_PERIOD_PERIOD_DESC"("TG_PERIOD_i");
               
                IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
                  "PERIODS_srk"("PERIODS_i") := "TG_PERIOD_i";
                END IF;
                "PERIODS_i" := "PERIODS_i" + 1;
              EXCEPTION  
                WHEN OTHERS THEN  
                  "TG_PERIOD_ER"(error_stmt, error_column, error_value, SQLCODE, SQLERRM, get_audit_detail_id, "TG_PERIOD_i");
                  "PERIODS_err" := "PERIODS_err" + 1;
                  IF get_errors + "PERIODS_err" > get_max_errors THEN  
                    get_abort := TRUE;
                  END IF;
              END;
            END IF;
           
          END LOOP;
         
          IF NOT "PERIODS_St" THEN  
            -- Insert DML for "PERIODS"
            normal_action := 'INSERT';
            error_action  := 'INSERT';
            get_target_name := '"PERIODS"';
            get_audit_detail_id := "PERIODS_id";
            "PERIODS_si" := 1;
            LOOP  
              EXIT WHEN "PERIODS_i" <= get_bulk_size AND "TG_PERIOD_c"%FOUND AND NOT get_abort;
             
              get_rowid.DELETE;
             
              BEGIN  
                FORALL i IN "PERIODS_si".."PERIODS_i" - 1  
                  INSERT
                  /*+ APPEND PARALLEL("PERIODS", DEFAULT, DEFAULT) */
                  INTO  
                    "PERIODS"
                    ("PERIOD_CODE",
                    "PERIOD_DESCRIPTION")
                  VALUES  
                    ("PERIODS_PERIOD_CODE"(i),
          "PERIODS_PERIOD_DESCRIPTION"(i))
                  RETURNING ROWID BULK COLLECT INTO get_rowid;
               
                error_index := "PERIODS_si" + get_rowid.COUNT;
              EXCEPTION  
                WHEN OTHERS THEN  
                  error_index := "PERIODS_si" + get_rowid.COUNT;
                  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
                    "TG_PERIOD_ER"(error_action, '*', NULL, SQLCODE, SQLERRM, get_audit_detail_id, "PERIODS_srk"(error_index));
                  END IF;
                  "PERIODS_err" := "PERIODS_err" + 1;
                  IF get_errors + "PERIODS_err" > get_max_errors THEN  
                    get_abort := TRUE;
                  END IF;
              END;
             
              -- feedback for a bulk of rows
              IF get_audit_level = AUDIT_COMPLETE THEN  
                get_rowkey_bulk.DELETE;
                rowkey_bulk_index := 1;
                FOR rowkey_index IN "PERIODS_si"..error_index - 1 LOOP  
                  get_rowkey_bulk(rowkey_bulk_index) := get_rowkey + "PERIODS_srk"(rowkey_index) - 1;
                  rowkey_bulk_index := rowkey_bulk_index + 1;
                END LOOP;
              END IF;
              IF get_audit_level = AUDIT_COMPLETE THEN  
                WBPRTI.register_feedback_bulk(  
                  get_runtime_audit_id,
                  get_step_number,
                  get_rowkey_bulk,
                  'NEW',
                  get_target_name,
                  'T',
                  normal_action,
                  get_rowid
                );
              END IF;
             
              "PERIODS_ins" := "PERIODS_ins" + get_rowid.COUNT;
              "PERIODS_si" := error_index + 1;
             
              IF "PERIODS_si" >= "PERIODS_i" OR get_abort THEN  
                "PERIODS_i" := 1;
                EXIT;
              END IF;
            END LOOP;
          END IF;
        EXCEPTION  
          WHEN OTHERS THEN  
            "TG_PERIOD_ER"(error_stmt, '*', NULL, SQLCODE, SQLERRM, NULL, "TG_PERIOD_i");
            get_errors := get_errors + 1;
            IF get_errors > get_max_errors THEN  
              get_abort := TRUE;
            END IF;
        END;
       
        IF NOT "PERIODS_St" THEN  
          IF get_audit_level != AUDIT_NONE THEN  
            WBPRTI.auditd_progress(  
              p_rtd=>"PERIODS_id",
              p_sel=>get_map_selected,
              p_ins=>"PERIODS_ins",
              p_upd=>"PERIODS_upd",
              p_del=>"PERIODS_del",
              p_err=>"PERIODS_err",
              p_dis=>NULL
            );
          END IF;
          COMMIT;
        END IF;
       
      EXIT WHEN get_abort OR ("TG_PERIOD_c"%NOTFOUND AND "TG_PERIOD_i" > "TG_PERIOD_PERIOD_CODE".COUNT);
      END LOOP;
    END IF;
    COMMIT;
    BEGIN  
      IF get_is_cursor_open THEN  
        CLOSE "TG_PERIOD_c";
      END IF;
    EXCEPTION WHEN OTHERS THEN  
      NULL;
    END;
   
    IF NOT get_abort THEN  
      -- resolve actual owner and name
      WBPRTIU.resolve_name(USER, 'PERIODS', actual_owner, actual_name);
     
      BEGIN  
        IF actual_name IS NOT NULL THEN  
          SELECT NVL(num_rows, 0) INTO get_map_num_rows
          FROM all_tables
          WHERE table_name = actual_name
          AND owner = actual_owner;
          IF ("PERIODS_ins" + "PERIODS_upd" + "PERIODS_del") >= get_map_num_rows THEN  
            DBMS_STATS.gather_table_stats(  
              ownname=>actual_owner,
              tabname=>actual_name,
              estimate_percent=>90,
              granularity=>'GLOBAL',
              degree=>null
            );
          END IF;
        END IF;
      EXCEPTION WHEN OTHERS THEN  
        IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
          WBPRTI.error(
            get_runtime_audit_id,
            get_step_number,
            0,
            0,
            'PERIODS',
            '*',
            NULL,
            NULL,
            SQLCODE,
            SQLERRM,
            NULL
          );
        END IF;
      END;
    END IF;
  END IF;
 
  IF NOT "PERIODS_St"
  AND get_audit_level != AUDIT_NONE THEN  
    WBPRTI.auditd_end(  
      p_rtd=>"PERIODS_id",
      p_sel=>get_map_selected,
      p_ins=>"PERIODS_ins",
      p_upd=>"PERIODS_upd",
      p_del=>"PERIODS_del",
      p_err=>"PERIODS_err",
      p_dis=>NULL
    );
  END IF;
 
  get_inserted := get_inserted + "PERIODS_ins";
  get_updated  := get_updated  + "PERIODS_upd";
  get_deleted  := get_deleted  + "PERIODS_del";
  get_errors   := get_errors   + "PERIODS_err";
  get_selected := get_selected + get_map_selected;
  COMMIT;
END "TG_PERIOD_p";


-- Procedure "TG_PERIOD_t" is the entry point for map "TG_PERIOD_t"

PROCEDURE "TG_PERIOD_t" IS
-- Constants for this map
get_map_name               CONSTANT VARCHAR2(40) := '"TG_PERIOD_t"';
get_source_name            CONSTANT VARCHAR2(80) := SUBSTR('"TG_PERIOD"@"PERIODS"',0,80);
get_step_number            CONSTANT NUMBER(22) := 1;

get_is_cursor_open         BOOLEAN := FALSE;
get_map_selected           NUMBER(22) := 0;
get_map_errors             NUMBER(22) := 0;
get_map_num_rows           NUMBER(22) := 0;

actual_owner               VARCHAR2(30);
actual_name                VARCHAR2(30);

-- Constraint management
num_fk_err                 NUMBER(22);
l_rowkey                   NUMBER(22) := 0;
l_table                    VARCHAR2(30) := 'CREATE';
l_rowid                    ROWID;
l_owner                    VARCHAR2(30);
l_tablename                VARCHAR2(30);
l_constraint               VARCHAR2(30);
sql_excp_stmt              VARCHAR2(32767);
TYPE exceptionsCurType IS REF CURSOR;
exceptions_cursor          exceptionsCurType;

normal_action              VARCHAR2(20);
error_action               VARCHAR2(20);
get_audit_detail_id        NUMBER(22) := 0;
get_target_name            VARCHAR2(80);
error_column               VARCHAR2(80);
error_value                VARCHAR2(2000);


-- Scalar variables for auditing
"PERIODS_id" NUMBER(22) := 0;
"PERIODS_ins" NUMBER(22) := 0;
"PERIODS_upd" NUMBER(22) := 0;
"PERIODS_del" NUMBER(22) := 0;
"PERIODS_err" NUMBER(22) := 0;

-- Variables for auditing in bulk processing
one_rowkey            NUMBER(22) := 0;
get_rowkey            NUMBER(22) := 0;
get_rowkey_bulk       WBPRTI.NUMBERLIST;
one_rowid             ROWID;
get_rowid             WBPRTI.ROWIDLIST;
rowkey_bulk_index     NUMBER(22) := 0;
x_it_err_count        NUMBER(22) := 0;

"PERIODS_srk" WBPRTI.NUMBERLIST;

-- Bulk processing
error_index                NUMBER(22);
update_bulk                WBPRTI.NUMBERLIST;
update_bulk_index          NUMBER(22) := 0;
insert_bulk_index          NUMBER(22) := 0;
last_successful_index      NUMBER(22) := 0;
feedback_bulk_limit        NUMBER(22) := 0;

"TG_PERIOD_si$0" NUMBER(22) := 0;
"TG_PERIOD_i$0" NUMBER(22) := 0;
"PERIODS_si" NUMBER(22) := 0;
"PERIODS_i" NUMBER(22) := 0;

-- Cursor declaration
CURSOR "TG_PERIOD_c$0" IS
  SELECT
  /*+ NO_MERGE  */
    "TG_PERIOD_PERIODS"."PERIOD_CODE" "PERIOD_CODE$1",
    "TG_PERIOD_PERIODS"."PERIOD_DESC" "PERIOD_DESC$1"
  FROM
    "TG_PERIOD"@"PERIODS" "TG_PERIOD_PERIODS";

---------------------------------------------------------------------------
-- This procedure records column values of one erroneous source row
-- into an audit trail table named WB_RT_ERROR_SOURCES.  Each column is
-- recorded by one row in the audit trail.  To collect all source column
-- values corresponding to one erroneous source row, query the audit
-- trail and specify:
--    RTA_IID, uniquely identifies one audited run,
--    RTE_ROWKEY, uniquely identifies a source row within and audited run
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_ES$0"(error_index IN NUMBER) IS
BEGIN
  WBPRTI.error_source(
    get_runtime_audit_id,
    get_rowkey + error_index - 1,
    1,
    1,
    SUBSTR('"TG_PERIOD"@"PERIODS"',0,80),
    SUBSTR('TG_PERIOD_PERIOD_CODE',0,80),
    SUBSTR("TG_PERIOD_PERIOD_CODE$0"(error_index),0,2000),
    get_step_number
  );
  WBPRTI.error_source(
    get_runtime_audit_id,
    get_rowkey + error_index - 1,
    2,
    1,
    SUBSTR('"TG_PERIOD"@"PERIODS"',0,80),
    SUBSTR('TG_PERIOD_PERIOD_DESC',0,80),
    SUBSTR("TG_PERIOD_PERIOD_DESC$0"(error_index),0,2000),
    get_step_number
  );
  RETURN;
END "TG_PERIOD_ES$0";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_ER$0" registers error for one errorneous row
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_ER$0"(p_statement IN VARCHAR2, p_column IN VARCHAR2, p_col_value IN VARCHAR2, p_sqlcode IN NUMBER, p_sqlerrm IN VARCHAR2, p_auditd_id IN NUMBER, p_error_index IN NUMBER) IS
l_source_target_name VARCHAR2(80);
BEGIN  
  IF p_auditd_id IS NULL THEN  
    l_source_target_name := get_source_name;
  ELSE  
    l_source_target_name := get_target_name;
  END IF;
  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
    WBPRTI.error(  
      get_runtime_audit_id,
      get_step_number,
      p_auditd_id,
      get_rowkey + p_error_index - 1,
      l_source_target_name,
      p_column,
      p_col_value,
      p_statement,
      p_sqlcode,
      p_sqlerrm,
      NULL
    );
  END IF;
 
  IF get_audit_level = AUDIT_COMPLETE THEN  
    WBPRTI.register_feedback(  
      get_runtime_audit_id,
      get_step_number,
      get_rowkey + p_error_index - 1,
      'ERROR',
      l_source_target_name,
      'T',
      SUBSTR(p_statement,0,30)
    );
  END IF;
 
  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
    "TG_PERIOD_ES$0"(p_error_index);
  END IF;
END "TG_PERIOD_ER$0";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_SU$0" opens and initializes data source
-- for map "TG_PERIOD_t"
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_SU$0" IS
BEGIN
  IF get_abort THEN
    RETURN;
  END IF;
  OPEN "TG_PERIOD_c$0";
  get_is_cursor_open := TRUE;
END "TG_PERIOD_SU$0";

---------------------------------------------------------------------------
-- Procedure "TG_PERIOD_RD$0" fetches a bulk of rows from
--   the data source for map "TG_PERIOD_t"
---------------------------------------------------------------------------
PROCEDURE "TG_PERIOD_RD$0" IS
BEGIN  
  IF get_abort THEN  
    RETURN;
  END IF;
 
  BEGIN  
    "TG_PERIOD_PERIOD_CODE$0".DELETE;
"TG_PERIOD_PERIOD_DESC$0".DELETE;
   
    FETCH  
      "TG_PERIOD_c$0"
    BULK COLLECT INTO  
      "TG_PERIOD_PERIOD_CODE$0",
      "TG_PERIOD_PERIOD_DESC$0"
    LIMIT get_bulk_size;
   
    IF "TG_PERIOD_c$0"%NOTFOUND AND "TG_PERIOD_PERIOD_CODE$0".COUNT = 0 THEN  
      RETURN;
    END IF;
   
    -- register feedback for successful reads
    IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
      get_rowkey := WBPRTI.get_rowkey("TG_PERIOD_PERIOD_CODE$0".COUNT);
    END IF;
   
    IF get_audit_level = AUDIT_COMPLETE THEN  
      WBPRTI.register_feedback_bulk(  
        get_runtime_audit_id,
        get_step_number,
        get_rowkey,
        'NEW',
        get_source_name,
        'S',
        'SELECT',
        "TG_PERIOD_PERIOD_CODE$0".COUNT
      );
    END IF;
    get_map_selected := get_map_selected + "TG_PERIOD_PERIOD_CODE$0".COUNT;
  EXCEPTION  
    WHEN OTHERS THEN  
      -- register error
      IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
        one_rowkey := WBPRTI.get_rowkey;
        WBPRTI.error(
          get_runtime_audit_id,
          get_step_number,
          NULL,
          one_rowkey,
          get_source_name,
          '*',
          NULL,
          'SELECT',
          SQLCODE,
          SQLERRM,
          NULL
        );
      END IF;
     
      -- register feedback for the erroneous row
      IF get_audit_level = AUDIT_COMPLETE THEN
        WBPRTI.register_feedback(
          get_runtime_audit_id,
          get_step_number,
          one_rowkey,
          'ERROR',
          get_source_name,
          'S',
          'SELECT'
        );
      END IF;
      get_errors := get_errors + get_bulk_size - "TG_PERIOD_PERIOD_CODE$0".COUNT;
      IF get_errors > get_max_errors THEN  
        get_abort := TRUE;
      END IF;
  END;
END "TG_PERIOD_RD$0";


---------------------------------------------------------------------------
-- "TG_PERIOD_t" main
---------------------------------------------------------------------------

BEGIN  
  IF get_abort THEN  
    RETURN;
  END IF;
 
  sql_stmt := 'ALTER SESSION DISABLE PARALLEL DML';
  EXECUTE IMMEDIATE sql_stmt;
 
  IF NOT "PERIODS_St" THEN  
   
    IF get_audit_level != AUDIT_NONE THEN  
      IF NOT "PERIODS_St" THEN  
        "PERIODS_id" :=  
          WBPRTI.auditd_begin(get_runtime_audit_id,get_step_number,get_map_name,
          get_source_name,'"PERIODS"',NULL,NULL);
      END IF;
      COMMIT;
    END IF;
   
    "TG_PERIOD_si$0" := 0;
    "PERIODS_i" := 1;
   
    IF NOT get_abort THEN  
      "TG_PERIOD_SU$0";
     
      LOOP  
        IF "TG_PERIOD_si$0" = 0 THEN  
          "TG_PERIOD_RD$0";   -- Fetch data from source
        END IF;
       
        -- Processing:
        "TG_PERIOD_i$0" := "TG_PERIOD_si$0";
        BEGIN  
          LOOP  
            EXIT WHEN "PERIODS_i" > get_bulk_size OR get_abort;
           
            "TG_PERIOD_i$0" := "TG_PERIOD_i$0" + 1;
            "TG_PERIOD_si$0" := "TG_PERIOD_i$0";
           
            IF "TG_PERIOD_i$0" > "TG_PERIOD_PERIOD_CODE$0".COUNT THEN  
              "TG_PERIOD_si$0" := 0;
              EXIT;
            END IF;
           
            get_target_name := '"PERIODS"';
            get_audit_detail_id := "PERIODS_id";
            IF NOT "PERIODS_St" THEN  
              BEGIN  
               
                error_stmt := SUBSTR('"PERIODS_PERIOD_CODE$0"("PERIODS_i") :=  "TG_PERIOD_PERIOD_CODE$0"("TG_PERIOD_i$0"); ',0,2000);
                error_column := SUBSTR('"PERIODS_PERIOD_CODE$0"',0,80);
                error_value := SUBSTR("TG_PERIOD_PERIOD_CODE$0"("TG_PERIOD_i$0"),0,2000);
                "PERIODS_PERIOD_CODE$0"("PERIODS_i") := "TG_PERIOD_PERIOD_CODE$0"("TG_PERIOD_i$0");
               
                error_stmt := SUBSTR('"PERIODS_PERIOD_DESCRIPTION$0"("PERIODS_i") :=  "TG_PERIOD_PERIOD_DESC$0"("TG_PERIOD_i$0"); ',0,2000);
                error_column := SUBSTR('"PERIODS_PERIOD_DESCRIPTION$0"',0,80);
                error_value := SUBSTR("TG_PERIOD_PERIOD_DESC$0"("TG_PERIOD_i$0"),0,2000);
                "PERIODS_PERIOD_DESCRIPTION$0"("PERIODS_i") := "TG_PERIOD_PERIOD_DESC$0"("TG_PERIOD_i$0");
               
                IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
                  "PERIODS_srk"("PERIODS_i") := "TG_PERIOD_i$0";
                END IF;
                "PERIODS_i" := "PERIODS_i" + 1;
              EXCEPTION  
                WHEN OTHERS THEN  
                  "TG_PERIOD_ER$0"(error_stmt, error_column, error_value, SQLCODE, SQLERRM, get_audit_detail_id, "TG_PERIOD_i$0");
                  "PERIODS_err" := "PERIODS_err" + 1;
                  IF get_errors + "PERIODS_err" > get_max_errors THEN  
                    get_abort := TRUE;
                  END IF;
              END;
            END IF;
           
          END LOOP;
         
          IF NOT "PERIODS_St" THEN  
            -- Insert DML for "PERIODS"
            normal_action := 'INSERT';
            error_action  := 'INSERT';
            get_target_name := '"PERIODS"';
            get_audit_detail_id := "PERIODS_id";
            "PERIODS_si" := 1;
            LOOP  
              EXIT WHEN "PERIODS_i" <= get_bulk_size AND "TG_PERIOD_c$0"%FOUND AND NOT get_abort;
             
              get_rowid.DELETE;
             
              BEGIN  
                FORALL i IN "PERIODS_si".."PERIODS_i" - 1  
                  INSERT
                  /*+ APPEND PARALLEL("PERIODS", DEFAULT, DEFAULT) */
                  INTO  
                    "PERIODS"
                    ("PERIOD_CODE",
                    "PERIOD_DESCRIPTION")
                  VALUES  
                    ("PERIODS_PERIOD_CODE$0"(i),
          "PERIODS_PERIOD_DESCRIPTION$0"(i))
                  RETURNING ROWID BULK COLLECT INTO get_rowid;
               
                error_index := "PERIODS_si" + get_rowid.COUNT;
              EXCEPTION  
                WHEN OTHERS THEN  
                  error_index := "PERIODS_si" + get_rowid.COUNT;
                  IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN  
                    "TG_PERIOD_ER$0"(error_action, '*', NULL, SQLCODE, SQLERRM, get_audit_detail_id, "PERIODS_srk"(error_index));
                  END IF;
                  "PERIODS_err" := "PERIODS_err" + 1;
                  IF get_errors + "PERIODS_err" > get_max_errors THEN  
                    get_abort := TRUE;
                  END IF;
              END;
             
              -- feedback for a bulk of rows
              IF get_audit_level = AUDIT_COMPLETE THEN  
                get_rowkey_bulk.DELETE;
                rowkey_bulk_index := 1;
                FOR rowkey_index IN "PERIODS_si"..error_index - 1 LOOP  
                  get_rowkey_bulk(rowkey_bulk_index) := get_rowkey + "PERIODS_srk"(rowkey_index) - 1;
                  rowkey_bulk_index := rowkey_bulk_index + 1;
                END LOOP;
              END IF;
              IF get_audit_level = AUDIT_COMPLETE THEN  
                WBPRTI.register_feedback_bulk(  
                  get_runtime_audit_id,
                  get_step_number,
                  get_rowkey_bulk,
                  'NEW',
                  get_target_name,
                  'T',
                  normal_action,
                  get_rowid
                );
              END IF;
             
              "PERIODS_ins" := "PERIODS_ins" + get_rowid.COUNT;
              "PERIODS_si" := error_index + 1;
             
              IF "PERIODS_si" >= "PERIODS_i" OR get_abort THEN  
                "PERIODS_i" := 1;
                EXIT;
              END IF;
            END LOOP;
          END IF;
        EXCEPTION  
          WHEN OTHERS THEN  
            "TG_PERIOD_ER$0"(error_stmt, '*', NULL, SQLCODE, SQLERRM, NULL, "TG_PERIOD_i$0");
            get_errors := get_errors + 1;
            IF get_errors > get_max_errors THEN  
              get_abort := TRUE;
            END IF;
        END;
       
        IF NOT "PERIODS_St" THEN  
          IF get_audit_level != AUDIT_NONE THEN  
            WBPRTI.auditd_progress(  
              p_rtd=>"PERIODS_id",
              p_sel=>get_map_selected,
              p_ins=>"PERIODS_ins",
              p_upd=>"PERIODS_upd",
              p_del=>"PERIODS_del",
              p_err=>"PERIODS_err",
              p_dis=>NULL
            );
          END IF;
          COMMIT;
        END IF;
       
      EXIT WHEN get_abort OR ("TG_PERIOD_c$0"%NOTFOUND AND "TG_PERIOD_i$0" > "TG_PERIOD_PERIOD_CODE$0".COUNT);
      END LOOP;
    END IF;
    COMMIT;
    BEGIN  
      IF get_is_cursor_open THEN  
        CLOSE "TG_PERIOD_c$0";
      END IF;
    EXCEPTION WHEN OTHERS THEN  
      NULL;
    END;
   
    IF NOT get_abort THEN  
      -- resolve actual owner and name
      WBPRTIU.resolve_name(USER, 'PERIODS', actual_owner, actual_name);
     
      BEGIN  
        IF actual_name IS NOT NULL THEN  
          SELECT NVL(num_rows, 0) INTO get_map_num_rows
          FROM all_tables
          WHERE table_name = actual_name
          AND owner = actual_owner;
          IF ("PERIODS_ins" + "PERIODS_upd" + "PERIODS_del") >= get_map_num_rows THEN  
            DBMS_STATS.gather_table_stats(  
              ownname=>actual_owner,
              tabname=>actual_name,
              estimate_percent=>90,
              granularity=>'GLOBAL',
              degree=>null
            );
          END IF;
        END IF;
      EXCEPTION WHEN OTHERS THEN  
        IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
          WBPRTI.error(
            get_runtime_audit_id,
            get_step_number,
            0,
            0,
            'PERIODS',
            '*',
            NULL,
            NULL,
            SQLCODE,
            SQLERRM,
            NULL
          );
        END IF;
      END;
    END IF;
  END IF;
 
  IF NOT "PERIODS_St"
  AND get_audit_level != AUDIT_NONE THEN  
    WBPRTI.auditd_end(  
      p_rtd=>"PERIODS_id",
      p_sel=>get_map_selected,
      p_ins=>"PERIODS_ins",
      p_upd=>"PERIODS_upd",
      p_del=>"PERIODS_del",
      p_err=>"PERIODS_err",
      p_dis=>NULL
    );
  END IF;
 
  get_inserted := get_inserted + "PERIODS_ins";
  get_updated  := get_updated  + "PERIODS_upd";
  get_deleted  := get_deleted  + "PERIODS_del";
  get_errors   := get_errors   + "PERIODS_err";
  get_selected := get_selected + get_map_selected;
  COMMIT;
END "TG_PERIOD_t";


PROCEDURE Initialize(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL')  IS
BEGIN
  get_selected := 0;
  get_inserted := 0;
  get_updated  := 0;
  get_deleted  := 0;
  get_merged   := 0;
  get_errors   := 0;
  get_abort    := FALSE;
  IF p_bulk_size IS NOT NULL THEN
    get_bulk_size := p_bulk_size;
  END IF;
  IF p_audit_level IS NOT NULL THEN
    get_audit_level := p_audit_level;
  END IF;
  IF p_max_errors IS NOT NULL THEN
    get_max_errors := p_max_errors;
  END IF;
  IF p_commit_frequency IS NOT NULL THEN
    get_commit_frequency := p_commit_frequency;
  END IF;
  IF p_operating_mode IS NOT NULL THEN
    get_operating_mode := p_operating_mode;
  END IF;
  IF get_audit_level != AUDIT_NONE THEN
    get_runtime_audit_id := WBPRTI.audit_begin(
      p_job,
      get_model_id,
      get_model_name,
      get_purge_group,
      NULL,
      SUBSTR('"TG_PERIOD"@"PERIODS"',0,80),
      SUBSTR('"PERIODS"',0,80),
      NULL,
      get_cycle_date
    );
  END IF;
 
  COMMIT;
END Initialize;


PROCEDURE Finalize(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL')  IS
BEGIN
 
  IF p_bulk_size IS NOT NULL THEN
    get_bulk_size := p_bulk_size;
  END IF;
  IF p_audit_level IS NOT NULL THEN
    get_audit_level := p_audit_level;
  END IF;
  IF p_max_errors IS NOT NULL THEN
    get_max_errors := p_max_errors;
  END IF;
  IF p_commit_frequency IS NOT NULL THEN
    get_commit_frequency := p_commit_frequency;
  END IF;
  IF get_audit_level != AUDIT_NONE THEN
    IF NOT get_abort THEN
      WBPRTI.audit_end(
        get_runtime_audit_id,
        get_selected,
        get_inserted,
        get_updated,
        get_deleted,
        get_errors,
        NULL,
        get_merged
      );
    ELSE
      WBPRTI.audit_fail(
        p_rta=>get_runtime_audit_id,
        p_sel=>get_selected,
        p_ins=>get_inserted,
        p_upd=>get_updated,
        p_del=>get_deleted,
        p_err=>get_errors,
        p_dis=>NULL,
        p_mer=>get_merged
      );
      raise_application_error(-20102, 'Fatal error or maximum number of errors exceeded');
    END IF;
  END IF;
  COMMIT;
END Finalize;


PROCEDURE Main(p_operating_mode IN BINARY_INTEGER DEFAULT NULL, p_audit_level IN BINARY_INTEGER DEFAULT NULL, p_max_errors IN NUMBER DEFAULT NULL, p_commit_frequency IN NUMBER DEFAULT NULL, p_bulk_size IN NUMBER DEFAULT NULL, p_job IN VARCHAR2 DEFAULT 'NULL')  IS
get_batch_status           BOOLEAN := TRUE;
BEGIN
  IF WBPRTIU.supportsDesignClient(p_designVersion=>'9.0.3.33.0', p_minRuntimeVersion=>'9.0.3.0.1') < 1 THEN  
    raise_application_error(-20103, 'Incompatible runtime and design client versions.');
  END IF;
  Initialize(p_operating_mode, p_audit_level, p_max_errors, p_commit_frequency, p_bulk_size, p_job);
  -- Initialize all batch status variables
  "PERIODS_St" := FALSE;
 
  --  Processing for different operating modes
  IF get_operating_mode = MODE_SET THEN  
    "PERIODS_St" := "PERIODS_Bat";
  END IF;
  IF get_operating_mode = MODE_ROW THEN  
    "TG_PERIOD_p";
  END IF;
  IF get_operating_mode = MODE_ROW_TARGET THEN  
    "TG_PERIOD_t";
  END IF;
  IF get_operating_mode = MODE_SET_FAILOVER_ROW THEN  
    IF get_batch_status THEN  
      "PERIODS_St" := "PERIODS_Bat";
      get_batch_status := get_batch_status AND "PERIODS_St";
    END IF;
    "TG_PERIOD_p";
  END IF;
  IF get_operating_mode = MODE_SET_FAILOVER_ROW_TARGET THEN  
    IF get_batch_status THEN  
      "PERIODS_St" := "PERIODS_Bat";
      get_batch_status := get_batch_status AND "PERIODS_St";
    END IF;
    "TG_PERIOD_t";
  END IF;
 
  Finalize(p_operating_mode, p_audit_level, p_max_errors, p_commit_frequency, p_bulk_size, p_job);
END Main;

END PERIODS_IN;

/
0
 
LVL 3

Accepted Solution

by:
bkowalski earned 400 total points
ID: 8180913
You just need to login to the database with SQL Plus and run the script. Make sure to login as the user you want to own the objects. Once logged in:

SQL> @periods_in.pls

or whatever your filename is:

SQL> @<filename including path>
0
 

Author Comment

by:ashish_me2
ID: 8189660
This creates a Package named Periods_IN in the database and when I try compiling the package, it gives me an error in compilation.

Is it really possible to load dimension tables from one schema to another schema?
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 8195378
What's the error? Use the "show errors" command after running the script.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question