Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/policy/config_util_pkgbody.sql /st_emdbsa_11.2/4 2011/07/27 07:54:06 yozhang Exp $
Rem
Rem config_util_pkgbody.sql
Rem
Rem Copyright (c) 2008, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      config_util_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    yozhang     07/25/11 - Add return in Exception block
Rem    mpawelko    04/26/11 - Backport mnihalan_bug-8837510_2 from
Rem                           st_emdbgc_10.2.0.1.0
Rem    mnihalan    06/11/09 - Fix bug 8591989
Rem    mnihalan    05/07/08 - Utils for config policies
Rem    mnihalan    05/07/08 - Created
Rem

CREATE OR REPLACE PACKAGE BODY CONFIG_UTIL
IS

  --DEPRECATED: DONT USE THIS FUNCTION.THIS CAUSED SQL INJECTION ISSUE (Bug 9098134)
  --Removed the implementation that concatenates the input parameters into a SQL and executes it.
  -- (To avoid sql injection issue).
  --Function is not removed for backward compatability.Implementation is dummy.

  -- Function to return a concatenated list (space delimited by default) a the
  -- given table and column, restricted by given predicate.
  -- If the max varchar2 length is exceeded (VALUE_ERROR) a truncated
  -- result is returned.
  --  COL      The name of the column, from the tab passed int.
  --  TAB      The table (or query in parens) from which to select the column.
  --  PREDICATE - Used in a where clause to restrict the selection.
  --  BIND_VAR    Bind variable to be used in the predicate
  --  DELIMETER   Used to separate the column values in the returned string.
  --  COL_LIMIT   Used to limit the number of COL values to return. If more then
  --              this limit exists the return string has an ellipsis added at
  --              the end
  FUNCTION CONCAT_FILE_NAME ( COL VARCHAR2,
                        TAB VARCHAR2,
                        PREDICATE VARCHAR2,
                        BIND_VAR  VARCHAR2,
                        DELIMETER VARCHAR2 := ' ',
                        COL_LIMIT NUMBER := -1)
  RETURN VARCHAR2
  IS
  BEGIN
   RETURN ' ';

  END;

 FUNCTION REDO_FILE_LIST( p_snapshot_guid IN VARCHAR2)
  RETURN VARCHAR2
  IS
    TYPE C1Type IS REF CURSOR;  -- define weak REF CURSOR type
    c1 C1Type;  
    result VARCHAR2(4000);
  BEGIN  
      OPEN c1 FOR 
        select file_name
        from mgmt_db_redologs_ecm
        where ecm_snapshot_id = hextoraw(p_snapshot_guid)
        order by group_num, file_name;

      result := CONCAT_RESULT_COL(c1, ',', 10);
      CLOSE c1;
      IF result IS NULL THEN
         result := ' ';
      END IF;
     RETURN result;
   EXCEPTION
    WHEN OTHERS THEN 
      IF c1%ISOPEN THEN
        CLOSE c1;
      END IF;    

   RETURN ' ';

  END REDO_FILE_LIST;

FUNCTION CONCAT_RESULT_COL( c1 RESULT_CURSOR,                        
  DELIMETER VARCHAR2 := ' ',
  COL_LIMIT NUMBER := -1)
 RETURN VARCHAR2
  IS    
    result VARCHAR2(4000);
    col_val VARCHAR2(1000);    
    tmpDel VARCHAR2(10) := '';
    ellipsis VARCHAR2(3) := '...';
    cnt NUMBER := 0;
  BEGIN    
    result := '';    
    LOOP
      FETCH c1 INTO col_val;
      EXIT WHEN c1%NOTFOUND;
      cnt := cnt + 1;
      IF (COL_LIMIT != -1 AND cnt > COL_LIMIT)
      THEN
        result := result || tmpDel || ellipsis;
        EXIT;
      END IF;       
      result := result || tmpDel || col_val;
      tmpDel := DELIMETER;
    END LOOP;
    IF result IS NULL THEN
      result := ' ';
    END IF;
    RETURN result;
  EXCEPTION
    WHEN VALUE_ERROR THEN RETURN result; -- Truncated to value prior to value error.

  RETURN ' ';

  END CONCAT_RESULT_COL;


FUNCTION REDO_GROUP_NUM_LIST( p_snapshot_guid IN VARCHAR2)
  RETURN VARCHAR2
  IS
    TYPE C1Type IS REF CURSOR;  -- define weak REF CURSOR type
    c1 C1Type;  
    result VARCHAR2(4000);

  BEGIN  
                          
      OPEN c1 FOR 
        select group_num
        from mgmt_db_redologs_ecm
        where ecm_snapshot_id = hextoraw(p_snapshot_guid)
        order by group_num, file_name;

      result := CONCAT_RESULT_COL(c1, ',', 10);
      CLOSE c1;
      IF result IS NULL THEN
         result := ' ';
      END IF;
     RETURN result;
   EXCEPTION
    WHEN OTHERS THEN 
      IF c1%ISOPEN THEN
        CLOSE c1;
      END IF;    

   RETURN ' ';

  END;

FUNCTION CONTROL_FILE_LIST( p_snapshot_guid IN VARCHAR2)
  RETURN VARCHAR2
  IS
    TYPE C1Type IS REF CURSOR;  -- define weak REF CURSOR type
    c1 C1Type;  
    result VARCHAR2(4000);

  BEGIN  
                          
      OPEN c1 FOR 
        select file_name
        from mgmt_db_controlfiles_ecm
        where ecm_snapshot_id = hextoraw(p_snapshot_guid)
        order by file_name;

      result := CONCAT_RESULT_COL(c1, ',', 10);
      CLOSE c1;
      IF result IS NULL THEN
         result := ' ';
      END IF;
     RETURN result;
   EXCEPTION
    WHEN OTHERS THEN 
      IF c1%ISOPEN THEN
        CLOSE c1;
      END IF;    

    RETURN ' ';

  END;

END CONFIG_UTIL;
/
show errors;