Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/gccompliance/gccompliance_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem gccompliance_types.sql
Rem
Rem Copyright (c) 2014, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/gccompliance/gccompliance_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/gccompliance/gccompliance_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      gccompliance_types.sql - sql types used for 12.2
Rem
Rem    DESCRIPTION
Rem      Script to create SQL types for 12.2 release.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/gccompliance/gccompliance_types.sql 
Rem    SQL_SHIPPED_FILE: 
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE 
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    vmanikya    06/25/15 - update the EM_CS_CMP_RESULTS_ROW to add template related columns
Rem    xnguyen     03/25/14 - Created
Rem



Rem
Rem Type EM_CS_CMP_TEST_TGT
Rem
Rem Purpose: to store information of test target added from one page ui and to be passed to post-update.
Rem TARGET_NAME :  target name
Rem TARGET_TYPE : target type
Rem TARGET_GUID : target guid
Rem ROOT_TARGET_GUID : target guid
Rem CMP_DEF_TGT_ID : Cmp Def Tgt Id
Rem
Rem
CREATE OR REPLACE TYPE EM_CS_CMP_TEST_TGT IS OBJECT (
    TARGET_NAME      VARCHAR2(256),
    TARGET_TYPE      VARCHAR2(64),
    TARGET_GUID      RAW(16),
    ROOT_TARGET_GUID RAW(16),
    CMP_DEF_TGT_ID   NUMBER(38),
    STATIC FUNCTION NEW(
        P_TARGET_NAME       IN VARCHAR2,
        P_TARGET_TYPE       IN VARCHAR2,
        P_TARGET_GUID       IN RAW,
        P_ROOT_TARGET_GUID  IN RAW,
        P_CMP_DEF_TGT_ID    IN RAW
    )  RETURN EM_CS_CMP_TEST_TGT
);
/


CREATE OR REPLACE TYPE EM_CS_CMP_TEST_TGT_TABLE IS VARRAY(1000) OF EM_CS_CMP_TEST_TGT;
/

Rem
Rem Type EM_CS_CMP_TGT
Rem 
Rem Purpose : to pass the cmp def tgt id for targets associated to post update.
Rem 
Rem TARGET_GUID  : target guid
Rem CMP_DEF_TGT_ID : Cmp Def Target Id
Rem
CREATE OR REPLACE TYPE EM_CS_CMP_TGT IS OBJECT (
    TARGET_GUID      RAW(16),
    CMP_DEF_TGT_ID   NUMBER(38),
    STATIC FUNCTION NEW(
        P_TARGET_GUID       IN RAW,
        P_CMP_DEF_TGT_ID    IN NUMBER
    )  RETURN EM_CS_CMP_TGT
);
/


CREATE OR REPLACE TYPE EM_CS_CMP_TGT_TABLE IS VARRAY(1000) OF EM_CS_CMP_TGT;
/

Rem
Rem Type em_cs_tgt_eval_row
Rem 
Rem Purpose : to store cs-target eval details.
Rem 
Rem crit_violations  : no. of critical violations
Rem warn_violations : no. of warning violations
Rem mwarn_violations  : no. of minor warning violations
Rem compliance_score : compliance score
Rem
CREATE OR REPLACE TYPE em_cs_tgt_eval_row AS OBJECT
 (
 
    crit_violations            NUMBER,
    warn_violations            NUMBER,
    mwarn_violations           NUMBER,
    compliance_score           NUMBER
 );
 /
 
 CREATE OR REPLACE TYPE em_cs_tgt_eval AS TABLE OF em_cs_tgt_eval_row;
 /
 
Rem
Rem Type EM_CS_TEST_VIOL
Rem 
Rem Purpose : stores a cs-target violation detail row
Rem 
Rem ROOT_CS_GUID  : root cs guid
Rem RQS_GUID : rqs guid
Rem RULE_GUID  : rule guid
Rem ROOT_TARGET_GUID : root target guid
Rem TARGET_GUID   : target guid  
Rem VIOL_LEVEL     : violation level
Rem COL_NAME       : column name 
Rem COL_TYPE       : column type
Rem COL_VALUE      : column value
Rem COL_STR_VALUE  : column string value
Rem VIOL_GUID      : violation guid
Rem COL_INAME      : column iname
Rem COL_DNAME      : column dname
Rem COL_DNAME_NLSID : column dname nls id
Rem STATIC FUNCTION returns EM_CS_TEST_VIOL
 CREATE OR REPLACE TYPE EM_CS_TEST_VIOL IS OBJECT (
     ROOT_CS_GUID       RAW(16),
     RQS_GUID           RAW(16),
     RULE_GUID          RAW(16),
     ROOT_TARGET_GUID   RAW(16),
     TARGET_GUID        RAW(16),
     VIOL_LEVEL         NUMBER,
     COL_NAME        VARCHAR2(64),
     COL_TYPE        NUMBER,
     COL_VALUE       NUMBER,
     COL_STR_VALUE   VARCHAR2(4000),
     VIOL_GUID          RAW(16),
     COL_INAME       VARCHAR2(128),
     COL_DNAME       VARCHAR2(128),
     COL_DNAME_NLSID VARCHAR2(64),
     STATIC FUNCTION NEW(
         P_ROOT_CS_GUID       IN  RAW,
         P_RQS_GUID           IN  RAW,
         P_RULE_GUID          IN  RAW,
         P_ROOT_TARGET_GUID   IN  RAW,
         P_TARGET_GUID        IN  RAW,
         P_VIOL_LEVEL         IN  NUMBER,
         P_COL_NAME        IN  VARCHAR2,
         P_COL_TYPE        IN  NUMBER,
         P_COL_VALUE       IN  NUMBER,
         P_COL_STR_VALUE   IN  VARCHAR2,
         P_VIOL_GUID          IN  RAW,
         P_COL_INAME       IN  VARCHAR2,        
         P_COL_DNAME       IN  VARCHAR2,
         P_COL_DNAME_NLSID IN  VARCHAR2
     )  RETURN EM_CS_TEST_VIOL
 );
 /
 
 CREATE OR REPLACE TYPE EM_CS_TEST_VIOL_TABLE AS TABLE OF EM_CS_TEST_VIOL;
 /

Rem
Rem Type EM_CS_TGT_DRIFT_SUMM_ROW
Rem
Rem Purpose : stores a drift cs results summary row 
Rem
Rem non_compliant_evals : non-compliant evals for the target type
Rem compliant_evals     : compliant target evaluations for target type
Rem 
CREATE OR REPLACE TYPE EM_CS_TGT_DRIFT_SUMM_ROW AS OBJECT
(
         non_compliant_evals NUMBER,
         compliant_evals NUMBER
);
/

CREATE OR REPLACE TYPE EM_CS_TGT_DRIFT_SUMM AS TABLE OF EM_CS_TGT_DRIFT_SUMM_ROW;
/

Rem
Rem Type EM_CS_DRIFT_RESULTS_ROW
Rem
Rem Purpose : stores a drift cs results summary row
Rem
Rem cs_guid       : Cs Guid
Rem cs_dname      : Cs display name
Rem target_type          : targe type
Rem non_compliant_evals  : non-compliant evals
Rem compliant_evals      : compliant target evaluations
Rem compare_type         : compare type 
Rem source_target        : the respective rule guid of the drift cs
Rem total_diffs          : total differences found during compare
Rem
CREATE OR REPLACE TYPE EM_CS_DRIFT_RESULTS_ROW AS OBJECT
(
         cs_guid             RAW(16),
         cs_dname            VARCHAR2(128),
         target_type         VARCHAR2(256),
         non_compliant_evals NUMBER,
         compliant_evals NUMBER,
         compare_type        CHAR(1),
         source_target       RAW(16),
         total_diffs         NUMBER
);
/

CREATE OR REPLACE TYPE EM_CS_DRIFT_RESULTS AS TABLE OF EM_CS_DRIFT_RESULTS_ROW; 
/

Rem
Rem Type EM_CS_CMP_RESULTS_ROW
Rem
Rem Purpose : stores a drift cs results summary row
Rem
Rem cs_guid              : Cs Guid
Rem cs_dname             : Cs display name
Rem target_type          : targe type
Rem non_compliant_evals  : non-compliant evals
Rem compliant_evals      : compliant target evaluations
Rem compare_type         : compare type
Rem source_target        : the respective rule guid of the drift cs
Rem total_diffs          : total differences found during compare
Rem source_target_name   : the reference target name
Rem owner                : the owner of the compliance standard
Rem
CREATE OR REPLACE TYPE EM_CS_CMP_RESULTS_ROW AS OBJECT
(
         cs_guid             RAW(16),
         cs_dname            VARCHAR2(128),
         target_type         VARCHAR2(256),
         non_compliant_evals NUMBER,
         compliant_evals     NUMBER,
         error_evals         NUMBER,
         compare_type        CHAR(1),
         source_target       RAW(16),
         total_diffs         NUMBER,
         source_target_name  VARCHAR2(256),
         owner               VARCHAR2(256),
         template_id         NUMBER,
         template_name       VARCHAR2(256),
         name_nlsid          VARCHAR2(256),
         nls_bundle          VARCHAR2(256)
   
);
/

CREATE OR REPLACE TYPE EM_CS_CMP_RESULTS AS TABLE OF EM_CS_CMP_RESULTS_ROW;
/

Rem
Rem Type EM_CS_CMP_EVAL_ROW
Rem
Rem Purpose : stores a drift/consistency target evaluation row
Rem
Rem target_guid          : target guid
Rem cmp_def_target_id    : cmp def target id
Rem target_name          : target name
Rem target_type          : internal targe type
Rem
CREATE OR REPLACE TYPE EM_CS_CMP_EVAL_ROW AS OBJECT
(
     target_guid            RAW(16),
     cmp_def_target_id      NUMBER,
     target_name            VARCHAR2(256),
     target_type            VARCHAR2(256)
);
/

CREATE OR REPLACE TYPE EM_CS_CMP_EVAL AS TABLE OF EM_CS_CMP_EVAL_ROW;
/


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

CREATE OR REPLACE TYPE EM_CS_CCS_STATUS IS OBJECT (
    SNAPSHOT_TYPE      VARCHAR2(256),
    TARGET_TYPE        VARCHAR2(256),
    STATUS             NUMBER(1,0),

    STATIC FUNCTION NEW(
        P_SNAPSHOT_TYPE      IN VARCHAR2,
        P_TARGET_TYPE        IN VARCHAR2,
        P_STATUS             IN NUMBER DEFAULT 0
    )  RETURN EM_CS_CCS_STATUS
);
/

grant execute on EM_CS_CCS_STATUS to sysman with GRANT OPTION;

create or replace noneditionable synonym SYSMAN.EM_CS_CCS_STATUS for EM_CS_CCS_STATUS;

CREATE OR REPLACE TYPE EM_CS_CCS_STATUS_TABLE IS TABLE OF EM_CS_CCS_STATUS;
/

grant execute on EM_CS_CCS_STATUS_TABLE to sysman with GRANT OPTION;

create or replace noneditionable synonym SYSMAN.EM_CS_CCS_STATUS_TABLE for EM_CS_CCS_STATUS_TABLE;

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

show errors;



