ashish_me2
asked on
ORACLE 9.2.0 OLAP: Running .PLS files
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.
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.
It's possible that they can be run using SQL Plus. Can you post one of the .pls files?
ASKER
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_TARG ET 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_tab le_not_emp ty, -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_DESCRIPT ION" 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_DESCRIPT ION$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_DESCRIPTIO N" "T_PERIODS_PERIOD_DESCRIPT ION";
"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_DESCRIPTIO N$0" "T_PERIODS_PERIOD_DESCRIPT ION$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"."PERIO D_CODE" "PERIOD_CODE",
"TG_PERIOD_PERIODS"."PERIO D_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_st ats(
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"@"PERIO DS"',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"."PERIO D_CODE" "PERIOD_CODE$0",
"TG_PERIOD_PERIODS"."PERIO D_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"@"PERIO DS"',0,80) ,
SUBSTR('TG_PERIOD_PERIOD_C ODE',0,80) ,
SUBSTR("TG_PERIOD_PERIOD_C ODE"(error _index),0, 2000),
get_step_number
);
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
2,
1,
SUBSTR('"TG_PERIOD"@"PERIO DS"',0,80) ,
SUBSTR('TG_PERIOD_PERIOD_D ESC',0,80) ,
SUBSTR("TG_PERIOD_PERIOD_D ESC"(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_ind ex);
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".DE LETE;
"TG_PERIOD_PERIOD_DESC".DE LETE;
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".CO UNT = 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_PERI OD_PERIOD_ CODE".COUN T);
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b ulk(
get_runtime_audit_id,
get_step_number,
get_rowkey,
'NEW',
get_source_name,
'S',
'SELECT',
"TG_PERIOD_PERIOD_CODE".CO UNT
);
END IF;
get_map_selected := get_map_selected + "TG_PERIOD_PERIOD_CODE".CO UNT;
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".CO UNT;
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_ru ntime_audi t_id,get_s tep_number ,get_map_n ame,
get_source_name,'"PERIODS" ',NULL,NUL L);
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".CO UNT 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_CO DE"("PERIO DS_i") := "TG_PERIOD_PERIOD_CODE"("T G_PERIOD_i "); ',0,2000);
error_column := SUBSTR('"PERIODS_PERIOD_CO DE"',0,80) ;
error_value := SUBSTR("TG_PERIOD_PERIOD_C ODE"("TG_P ERIOD_i"), 0,2000);
"PERIODS_PERIOD_CODE"("PER IODS_i") := "TG_PERIOD_PERIOD_CODE"("T G_PERIOD_i ");
error_stmt := SUBSTR('"PERIODS_PERIOD_DE SCRIPTION" ("PERIODS_ i") := "TG_PERIOD_PERIOD_DESC"("T G_PERIOD_i "); ',0,2000);
error_column := SUBSTR('"PERIODS_PERIOD_DE SCRIPTION" ',0,80);
error_value := SUBSTR("TG_PERIOD_PERIOD_D ESC"("TG_P ERIOD_i"), 0,2000);
"PERIODS_PERIOD_DESCRIPTIO N"("PERIOD S_i") := "TG_PERIOD_PERIOD_DESC"("T G_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_DESCRIPTIO N"(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_actio n, '*', 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_bul k_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_b ulk(
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".CO UNT);
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_st ats(
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"@"PERIO DS"',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"."PERIO D_CODE" "PERIOD_CODE$1",
"TG_PERIOD_PERIODS"."PERIO D_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_ind ex IN NUMBER) IS
BEGIN
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
1,
1,
SUBSTR('"TG_PERIOD"@"PERIO DS"',0,80) ,
SUBSTR('TG_PERIOD_PERIOD_C ODE',0,80) ,
SUBSTR("TG_PERIOD_PERIOD_C ODE$0"(err or_index), 0,2000),
get_step_number
);
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
2,
1,
SUBSTR('"TG_PERIOD"@"PERIO DS"',0,80) ,
SUBSTR('TG_PERIOD_PERIOD_D ESC',0,80) ,
SUBSTR("TG_PERIOD_PERIOD_D ESC$0"(err or_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_stateme nt 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_i ndex);
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_PERI OD_PERIOD_ CODE$0".CO UNT);
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b ulk(
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_ru ntime_audi t_id,get_s tep_number ,get_map_n ame,
get_source_name,'"PERIODS" ',NULL,NUL L);
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_CO DE$0"("PER IODS_i") := "TG_PERIOD_PERIOD_CODE$0"( "TG_PERIOD _i$0"); ',0,2000);
error_column := SUBSTR('"PERIODS_PERIOD_CO DE$0"',0,8 0);
error_value := SUBSTR("TG_PERIOD_PERIOD_C ODE$0"("TG _PERIOD_i$ 0"),0,2000 );
"PERIODS_PERIOD_CODE$0"("P ERIODS_i") := "TG_PERIOD_PERIOD_CODE$0"( "TG_PERIOD _i$0");
error_stmt := SUBSTR('"PERIODS_PERIOD_DE SCRIPTION$ 0"("PERIOD S_i") := "TG_PERIOD_PERIOD_DESC$0"( "TG_PERIOD _i$0"); ',0,2000);
error_column := SUBSTR('"PERIODS_PERIOD_DE SCRIPTION$ 0"',0,80);
error_value := SUBSTR("TG_PERIOD_PERIOD_D ESC$0"("TG _PERIOD_i$ 0"),0,2000 );
"PERIODS_PERIOD_DESCRIPTIO N$0"("PERI ODS_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_stm t, 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_DESCRIPTIO N$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_act ion, '*', 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_bul k_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_b ulk(
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_stm t, '*', 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_st ats(
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_mod e 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"@"PERIO DS"',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_i d,
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(-2 0102, '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.supportsDesignClie nt(p_desig nVersion=> '9.0.3.33. 0', p_minRuntimeVersion=>'9.0. 3.0.1') < 1 THEN
raise_application_error(-2 0103, 'Incompatible runtime and design client versions.');
END IF;
Initialize(p_operating_mod e, 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_TARG ET 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;
/
/*************************
-- 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_TARG
-- 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_tab
invalid_dml EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_dml
-- 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_DESCRIPT
TYPE "T_TG_PERIOD_PERIOD_CODE$0
TYPE "T_TG_PERIOD_PERIOD_DESC$0
TYPE "T_PERIODS_PERIOD_CODE$0" IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
TYPE "T_PERIODS_PERIOD_DESCRIPT
-- 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_DESCRIPTIO
"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_DESCRIPTIO
-- 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,
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"."PERIO
"TG_PERIOD_PERIODS"."PERIO
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_st
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"@"PERIO
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"."PERIO
"TG_PERIOD_PERIODS"."PERIO
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
BEGIN
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
1,
1,
SUBSTR('"TG_PERIOD"@"PERIO
SUBSTR('TG_PERIOD_PERIOD_C
SUBSTR("TG_PERIOD_PERIOD_C
get_step_number
);
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
2,
1,
SUBSTR('"TG_PERIOD"@"PERIO
SUBSTR('TG_PERIOD_PERIOD_D
SUBSTR("TG_PERIOD_PERIOD_D
get_step_number
);
RETURN;
END "TG_PERIOD_ES";
--------------------------
-- Procedure "TG_PERIOD_ER" registers error for one errorneous row
--------------------------
PROCEDURE "TG_PERIOD_ER"(p_statement
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_ind
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".DE
"TG_PERIOD_PERIOD_DESC".DE
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".CO
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_PERI
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b
get_runtime_audit_id,
get_step_number,
get_rowkey,
'NEW',
get_source_name,
'S',
'SELECT',
"TG_PERIOD_PERIOD_CODE".CO
);
END IF;
get_map_selected := get_map_selected + "TG_PERIOD_PERIOD_CODE".CO
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".CO
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_ru
get_source_name,'"PERIODS"
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".CO
"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_CO
error_column := SUBSTR('"PERIODS_PERIOD_CO
error_value := SUBSTR("TG_PERIOD_PERIOD_C
"PERIODS_PERIOD_CODE"("PER
error_stmt := SUBSTR('"PERIODS_PERIOD_DE
error_column := SUBSTR('"PERIODS_PERIOD_DE
error_value := SUBSTR("TG_PERIOD_PERIOD_D
"PERIODS_PERIOD_DESCRIPTIO
IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
"PERIODS_srk"("PERIODS_i")
END IF;
"PERIODS_i" := "PERIODS_i" + 1;
EXCEPTION
WHEN OTHERS THEN
"TG_PERIOD_ER"(error_stmt,
"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_DESCRIPTIO
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_actio
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_bul
rowkey_bulk_index := rowkey_bulk_index + 1;
END LOOP;
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b
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,
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".CO
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,
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_st
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"@"PERIO
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"."PERIO
"TG_PERIOD_PERIODS"."PERIO
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_ind
BEGIN
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
1,
1,
SUBSTR('"TG_PERIOD"@"PERIO
SUBSTR('TG_PERIOD_PERIOD_C
SUBSTR("TG_PERIOD_PERIOD_C
get_step_number
);
WBPRTI.error_source(
get_runtime_audit_id,
get_rowkey + error_index - 1,
2,
1,
SUBSTR('"TG_PERIOD"@"PERIO
SUBSTR('TG_PERIOD_PERIOD_D
SUBSTR("TG_PERIOD_PERIOD_D
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_stateme
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_i
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".
"TG_PERIOD_PERIOD_DESC$0".
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".
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_PERI
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b
get_runtime_audit_id,
get_step_number,
get_rowkey,
'NEW',
get_source_name,
'S',
'SELECT',
"TG_PERIOD_PERIOD_CODE$0".
);
END IF;
get_map_selected := get_map_selected + "TG_PERIOD_PERIOD_CODE$0".
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".
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_ru
get_source_name,'"PERIODS"
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".
"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_CO
error_column := SUBSTR('"PERIODS_PERIOD_CO
error_value := SUBSTR("TG_PERIOD_PERIOD_C
"PERIODS_PERIOD_CODE$0"("P
error_stmt := SUBSTR('"PERIODS_PERIOD_DE
error_column := SUBSTR('"PERIODS_PERIOD_DE
error_value := SUBSTR("TG_PERIOD_PERIOD_D
"PERIODS_PERIOD_DESCRIPTIO
IF get_audit_level = AUDIT_ERROR_DETAILS OR get_audit_level = AUDIT_COMPLETE THEN
"PERIODS_srk"("PERIODS_i")
END IF;
"PERIODS_i" := "PERIODS_i" + 1;
EXCEPTION
WHEN OTHERS THEN
"TG_PERIOD_ER$0"(error_stm
"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_DESCRIPTIO
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_act
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_bul
rowkey_bulk_index := rowkey_bulk_index + 1;
END LOOP;
END IF;
IF get_audit_level = AUDIT_COMPLETE THEN
WBPRTI.register_feedback_b
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_stm
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".
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,
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_st
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_mod
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"@"PERIO
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_i
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(-2
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.supportsDesignClie
raise_application_error(-2
END IF;
Initialize(p_operating_mod
-- 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_TARG
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,
END Main;
END PERIODS_IN;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Is it really possible to load dimension tables from one schema to another schema?
What's the error? Use the "show errors" command after running the script.