Rem Rem Copyright (c) 2000 by Oracle Corporation Rem Rem NAME Rem WB_RT_MAPAUDIT_UTIL.sql Rem FUNCTION Rem Create runtime utility package (WB_RT_MAPAUDIT_UTIL) Rem NOTES Rem - This file can be tested using SQL*Plus. Rem - OWB developers must make sure this file is also runnable Rem using Runtime assistant. Rem Rem MODIFIED Rem nbaldwin 01/09/08 - Rem G. Watters 03/20/2002 - Created from WBPRTIU.SQL Rem Yu.Gong 04/03/2001 - Added disable/enable constraint API Rem Yu.Gong 03/21/2001 - Extended for PEL Rem Yu.Gong 12/06/2000 - Created. Rem CREATE OR REPLACE PACKAGE WB_RT_MAPAUDIT_UTIL IS TYPE ChildTab IS TABLE OF ALL_CONSTRAINTS%ROWTYPE INDEX BY BINARY_INTEGER ; ALLTab ChildTab ; Row_No INTEGER ; -- -- Return the version of the Runtime that may be displayed in the Client -- FUNCTION getRuntimeVersion RETURN VARCHAR2 ; -- -- Determine if the designVersion and minRuntimeVersion are supported by the runtime -- Return a value >= 1 if the designVersion is supported -- Return a value <= 0 if the designVersion is not supported or -- if one of the version strings is not properly formed -- FUNCTION supportsDesignClient(p_designVersion IN VARCHAR2, p_minRuntimeVersion IN VARCHAR2) RETURN NUMBER ; -- -- Compare two version strings. -- Return a value 1 if p_version1 > p_version2 -- Return a value 0 if p_version1 = p_version2 -- Return a value -1 if p_version1 < p_version2 -- FUNCTION compareVersions(p_version1 IN VARCHAR2, p_version2 IN VARCHAR2) RETURN NUMBER ; -- -- Count the number of full stop characters in the given version string. -- If the number counted is greater than c_maxFullStops then -1 is returned. -- FUNCTION countFullStops(p_version IN VARCHAR2) RETURN NUMBER ; -- -- Return the number section of a given version string according to -- the specified p_pos position. If p_pos <=0 or > c_maxFullStops+1 -- then -1 is returned. -- -- For example, for a version string of '111.222.333.444.555' -- p_pos | result -- -------------- -- 1 | 111 -- 2 | 222 -- 3 | 333 -- 4 | 444 -- 5 | 555 -- FUNCTION getVersionSection(p_version IN VARCHAR2, p_pos IN NUMBER) RETURN NUMBER ; -- -- Validate a given version string. -- Return a value >= 1 if the string is well formed. -- Return a value <= 0 if the string is not well formed. -- FUNCTION validateVersion(p_version IN VARCHAR2) RETURN NUMBER ; PROCEDURE GetObjDependency( p_table_name VARCHAR2 , p_owner IN VARCHAR2 DEFAULT 'USER', p_status IN VARCHAR2 DEFAULT 'ENABLED') ; PROCEDURE ExecSQL( p_SqlStmt VARCHAR2 ) ; PROCEDURE Print( p_Message VARCHAR2 DEFAULT NULL) ; PROCEDURE Truncate_Table( p_ObjectName IN VARCHAR2 , p_owner IN VARCHAR2 DEFAULT 'USER', p_reenablefk IN BOOLEAN DEFAULT FALSE, p_truncate IN BOOLEAN DEFAULT TRUE) ; PROCEDURE Enable_FK( p_owner IN VARCHAR2 , P_ObjectName IN VARCHAR2 ) ; PROCEDURE Create_Public_Synonym( p_synonym VARCHAR2) ; PROCEDURE Seed_Strings( p_type IN VARCHAR2 , P_value IN VARCHAR2 ) ; PROCEDURE Seed_Parameter( p_name IN VARCHAR2 , P_value IN VARCHAR2 , p_job_id IN NUMBER , p_modifiable IN VARCHAR2 ) ; ------------------------------------------------------------------- -- The following functions/procedures are added for implementing -- -- Partition Exchange Loading (PEL) feature. Included in this -- -- package because they could be used elsewhere in future. -- ------------------------------------------------------------------- -- get next unused temporary table name FUNCTION get_unused_temptable_name(p_owner IN VARCHAR2) RETURN VARCHAR2; -- get next unused temporary index name FUNCTION get_unused_tempindex_name(p_owner IN VARCHAR2) RETURN VARCHAR2; -- get next unused temporary constraint name FUNCTION get_unused_tempcons_name(p_owner IN VARCHAR2) RETURN VARCHAR2; -- create new table having same definition with the reference table PROCEDURE create_table_like(p_owner IN VARCHAR2, p_new_table IN VARCHAR2, p_ref_table IN VARCHAR2, p_tablespace IN VARCHAR2); -- create new temp table like ref table, return temp table name FUNCTION create_table_like(p_owner IN VARCHAR2, p_ref_table IN VARCHAR2, p_tablespace IN VARCHAR2) RETURN VARCHAR2; -- index a table according to all local indexes in the ref table PROCEDURE lindex_table_like(p_owner IN VARCHAR2, p_new_table IN VARCHAR2, p_ref_table IN VARCHAR2, p_lindex_tablespace IN VARCHAR2); -- add one type of constraint to table according to cons in ref table PROCEDURE add_cons_like(p_owner IN VARCHAR2, p_new_table IN VARCHAR2, p_ref_table IN VARCHAR2, p_cons_type IN VARCHAR2); -- add all constraints to a table according to cons in ref table PROCEDURE add_cons_like(p_owner IN VARCHAR2, p_new_table IN VARCHAR2, p_ref_table IN VARCHAR2); -- compose partition name based on a date value FUNCTION get_date_partition_name(p_date IN DATE, p_granularity IN VARCHAR2) RETURN VARCHAR2; -- grant system privilege PROCEDURE grant_sys_priv(p_grantee IN VARCHAR2, p_privilege IN VARCHAR2, p_admin_option IN BOOLEAN); -- grant object privilege PROCEDURE grant_obj_priv(p_grantee IN VARCHAR2, p_schema IN VARCHAR2, p_object IN VARCHAR2, p_privilege IN VARCHAR2, p_grant_option IN BOOLEAN); ------------------------------------------------------------------- -- The following functions/procedures are added for simplifying -- -- generated code. -- ------------------------------------------------------------------- -- disable all constraints of certain type: -- p_cons_type must take one of the values: P, U, R PROCEDURE disable_constraints(p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_cons_type IN VARCHAR2); -- disable one constraint PROCEDURE disable_constraint(p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_cons_name IN VARCHAR2); -- enable all constraints of certain type: -- p_exception_table can take one the following possible values: -- 1. NULL: No exception table is to be used. -- 2. 'CREATE': Create an exception table in p_owner's schema, then -- fill p_exception_table parameter (an IN OUT param) with -- the name of the freshly created exception table. -- 3. A table name: Use this table for the purpose of exception table. -- The table should be located in p_owner's schema. -- p_cons_type (if present) must take one of the values: P, U, R PROCEDURE enable_constraints(p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_cons_type IN VARCHAR2, p_exception_table IN OUT VARCHAR2); -- enable one constraint: -- p_exception_table can take one the following possible values: -- 1. NULL: No exception table is to be used. -- 2. 'CREATE': Create an exception table in p_owner's schema, then -- fill p_exception_table parameter (an IN OUT param) with -- the name of the freshly created exception table. -- 3. A table name: Use this table for the purpose of exception table. -- The table should be located in p_owner's schema. PROCEDURE enable_constraint(p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_cons_name IN VARCHAR2, p_exception_table IN OUT VARCHAR2); -- disable all referenced foreign keys for a certain table PROCEDURE disable_ref_fk(p_owner IN VARCHAR2, p_tablename IN VARCHAR2); -- enable all referenced foreign keys for a certain table -- p_exception_table can take one the following possible values: -- 1. NULL: No exception table is to be used. -- 2. 'CREATE': Create an exception table in p_owner's schema, then -- fill p_exception_table parameter (an IN OUT param) with -- the name of the freshly created exception table. -- 3. A table name: Use this table for the purpose of exception table. -- The table should be located in p_owner's schema. PROCEDURE enable_ref_fk(p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_exception_table IN OUT VARCHAR2); -- resolve a given owner and object name into the actual owner and object -- name by iteratively applying the following criteria until the actual -- owner and object name is located: -- 1. look up directly from the data dictionary for the given owner -- and object name. -- 2. look up from the private synonym name table for the given owner -- and object name. -- 3. look up from the public synonym name table for the given owner -- and object name. -- assumption: -- 1. the actual owner and object name cannot be resolved and will be -- returned as null if the given object is located at a different -- database instance. -- 2. only tables are supported. -- return: -- if the given owner and object name can be resolved. p_actual_owner -- and p_actual_name will contain the resolved owner and object name. -- otherwise, p_actual_owner and p_actual_name will be null. PROCEDURE resolve_name(p_original_owner IN VARCHAR2 DEFAULT USER, p_original_name IN VARCHAR2, p_actual_owner OUT VARCHAR2, p_actual_name OUT VARCHAR2); -- ygong_paris begin FUNCTION validate_runtime_parameter(p_name IN VARCHAR2, p_value IN VARCHAR2) RETURN BOOLEAN; PROCEDURE premap(p_map_name IN VARCHAR2, p_schema IN VARCHAR2, p_audit_id OUT NUMBER, p_object_id OUT NUMBER); PROCEDURE postmap(p_audit_id IN NUMBER, p_result IN NUMBER, p_return_code IN NUMBER); PROCEDURE register_sys_param(p_audit_execution_id IN NUMBER, p_parameter_name IN VARCHAR2, p_value IN VARCHAR2); PROCEDURE register_custom_param(p_audit_execution_id IN NUMBER, p_parameter_name IN VARCHAR2, p_value IN VARCHAR2); PROCEDURE show_run_results(p_audit_id IN NUMBER); -- ygong_paris end PROCEDURE set_schema_workspace(p_schema VARCHAR2); END WB_RT_MAPAUDIT_UTIL; /