DM_NONQUALIFIABLE.sql

CREATE OR REPLACE PACKAGE DM_NONQUALIFIABLE
AS
   FUNCTION READ_VALUE (p_object_id   IN VARCHAR2,
                        p_type_name   IN VARCHAR2,
                        p_attr_name   IN VARCHAR2)
      RETURN CLOB;

   FUNCTION READ_VALUE (p_property_bag   IN BLOB,
                        p_attribute_id   IN INTEGER,
                        p_object_id      IN VARCHAR2,
                        p_type_name      IN VARCHAR2)
      RETURN CLOB;

   FUNCTION READ_VALUE (p_property_bag IN BLOB, p_attribute_id IN INTEGER)
      RETURN CLOB;

   FUNCTION READ_VALUE (p_property_bag   IN CLOB,
                        p_attribute_id   IN INTEGER,
                        p_object_id      IN VARCHAR2,
                        p_type_name      IN VARCHAR2)
      RETURN CLOB;

   FUNCTION READ_VALUE (p_property_bag IN CLOB, p_attribute_id IN INTEGER)
      RETURN CLOB;

   FUNCTION READ_VALUE (p_object_id IN VARCHAR2, p_attr_name IN VARCHAR2)
      RETURN CLOB;
END DM_NONQUALIFIABLE;
/

CREATE OR REPLACE PACKAGE BODY DM_NONQUALIFIABLE
AS
   FUNCTION GET_ATTBIBUTE_ID (p_type_name   IN VARCHAR2,
                              p_attr_name   IN VARCHAR2)
      RETURN INTEGER
   AS
      v_select_stmt   VARCHAR (200);
      v_index         INTEGER;
   BEGIN
      v_select_stmt :=
         'SELECT ATTR_IDENTIFIER FROM dm_type_r WHERE attr_name=:attr_name 
                      AND r_object_id = (SELECT r_object_id FROM dm_type_s WHERE name=:type_name)';

      EXECUTE IMMEDIATE v_select_stmt
         INTO v_index
         USING p_attr_name, p_type_name;

      RETURN v_index;
   END GET_ATTBIBUTE_ID;

   FUNCTION GET_TYPE_NAME (p_object_id IN VARCHAR2)
      RETURN VARCHAR2
   AS
      v_select_stmt   VARCHAR2 (200);
      v_type_name     VARCHAR2 (32);
   BEGIN
      v_select_stmt :=
         'SELECT name FROM dm_type_s WHERE 
                      i_type = (SELECT i_type FROM dmi_object_type WHERE r_object_id=:object_id)';

      EXECUTE IMMEDIATE v_select_stmt INTO v_type_name USING p_object_id;

      RETURN v_type_name;
   END GET_TYPE_NAME;

   PROCEDURE READ_BAG_PROPERTY (p_object_id   IN            VARCHAR2,
                                p_type_name   IN            VARCHAR2,
                                p_repeating   IN            BOOLEAN,
                                p_data        IN OUT NOCOPY BLOB)
   AS
      TYPE PropCurType IS REF CURSOR;

      v_prop_cursor   PropCurType;
      v_prop_val      VARCHAR2 (4000);
      v_select_stmt   VARCHAR2 (200);
   BEGIN
      IF (p_repeating)
      THEN
         v_select_stmt :=
               'SELECT r_property_bag from '
            || p_type_name
            || '_rp WHERE r_object_id=:id ORDER BY i_position DESC';
      ELSE
         v_select_stmt :=
               'SELECT i_property_bag from '
            || p_type_name
            || '_sp WHERE r_object_id=:id';
      END IF;

      OPEN v_prop_cursor FOR v_select_stmt USING p_object_id;

      LOOP
         FETCH v_prop_cursor INTO v_prop_val;

         EXIT WHEN v_prop_cursor%NOTFOUND;

         IF (v_prop_val IS NOT NULL)
         THEN
            DBMS_LOB.APPEND (p_data, UTL_RAW.cast_to_raw (v_prop_val));
         END IF;
      END LOOP;

      CLOSE v_prop_cursor;
   END READ_BAG_PROPERTY;

   FUNCTION IS_WIHITESPACE (p_char IN VARCHAR2)
      RETURN BOOLEAN
   AS
   BEGIN
      RETURN p_char IN (CHR (32), CHR (10), CHR (13));
   END IS_WIHITESPACE;

   FUNCTION NEXT_TOKEN (p_data IN BLOB, p_offset IN OUT INTEGER)
      RETURN VARCHAR2
   AS
      v_symbol   VARCHAR2 (1);
      v_result   VARCHAR2 (2000);
   BEGIN
      v_result := NULL;

      LOOP
         v_symbol :=
            UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (p_data, 1, p_offset));

         IF (NOT IS_WIHITESPACE (v_symbol))
         THEN
            EXIT;
         END IF;

         p_offset := p_offset + 1;
      END LOOP;

      LOOP
         v_symbol :=
            UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (p_data, 1, p_offset));

         IF (IS_WIHITESPACE (v_symbol))
         THEN
            EXIT;
         END IF;

         p_offset := p_offset + 1;
         v_result := v_result || v_symbol;
      END LOOP;

      RETURN v_result;
   END NEXT_TOKEN;

   FUNCTION NEXT_TOKEN (p_data     IN     BLOB,
                        p_offset   IN OUT INTEGER,
                        p_skip     IN     INTEGER)
      RETURN VARCHAR2
   AS
      v_result   VARCHAR2 (2000);
   BEGIN
      FOR i IN 0 .. p_skip
      LOOP
         v_result := NEXT_TOKEN (p_data, p_offset);
      END LOOP;

      RETURN v_result;
   END NEXT_TOKEN;

   PROCEDURE READ_BDATA (p_in       IN            BLOB,
                         p_length   IN            INTEGER,
                         p_hex      IN            BOOLEAN,
                         p_offset   IN OUT        INTEGER,
                         p_out      IN OUT NOCOPY BLOB)
   AS
      v_remaning     INTEGER := p_length;
      v_chunk_size   INTEGER;
      v_buffer       RAW (2048);
   BEGIN
      WHILE (v_remaning > 0)
      LOOP
         v_chunk_size := v_remaning;

         IF (v_chunk_size > 2048)
         THEN
            v_chunk_size := 2048;
         END IF;

         DBMS_LOB.read (p_in,
                        v_chunk_size,
                        p_offset,
                        v_buffer);

         IF (p_hex)
         THEN
            DBMS_LOB.append (p_out,
                             HEXTORAW (UTL_RAW.CAST_TO_VARCHAR2 (v_buffer)));
         ELSE
            DBMS_LOB.append (p_out, v_buffer);
         END IF;

         p_offset := p_offset + v_chunk_size;
         v_remaning := v_remaning - v_chunk_size;
      END LOOP;
   END READ_BDATA;

   FUNCTION BLOB2CLOB (p_data BLOB)
      RETURN CLOB
   AS
      v_result         CLOB;
      v_offset         INTEGER := 1;
      v_blob_csid      NUMBER := DBMS_LOB.default_csid;
      v_lang_context   NUMBER := DBMS_LOB.default_lang_ctx;
      v_warning        INTEGER;
   BEGIN
      IF (p_data IS NULL OR LENGTH (p_data) = 0)
      THEN
         RETURN NULL;
      END IF;

      DBMS_LOB.CREATETEMPORARY (v_result, TRUE);
      DBMS_LOB.CONVERTTOCLOB (v_result,
                              p_data,
                              DBMS_LOB.lobmaxsize,
                              v_offset,
                              v_offset,
                              v_blob_csid,
                              v_lang_context,
                              v_warning);
      RETURN v_result;
   END BLOB2CLOB;

   FUNCTION CLOB2BLOB (p_data CLOB)
      RETURN BLOB
   AS
      v_result         BLOB;
      v_offset         INTEGER := 1;
      v_blob_csid      NUMBER := DBMS_LOB.default_csid;
      v_lang_context   NUMBER := DBMS_LOB.default_lang_ctx;
      v_warning        INTEGER;
   BEGIN
      IF (p_data IS NULL OR LENGTH (p_data) = 0)
      THEN
         RETURN NULL;
      END IF;

      DBMS_LOB.CREATETEMPORARY (v_result, TRUE);
      DBMS_LOB.CONVERTTOBLOB (v_result,
                              p_data,
                              DBMS_LOB.lobmaxsize,
                              v_offset,
                              v_offset,
                              v_blob_csid,
                              v_lang_context,
                              v_warning);
      RETURN v_result;
   END CLOB2BLOB;

   FUNCTION TO_INTEGER (p_str IN VARCHAR2)
      RETURN INTEGER
   AS
   BEGIN
      IF (p_str BETWEEN 'A' AND 'Z')
      THEN
         RETURN ASCII (p_str) - 65;
      END IF;

      IF (p_str BETWEEN 'a' AND 'z')
      THEN
         RETURN ASCII (p_str) - 71;
      END IF;

      IF (p_str BETWEEN '0' AND '9')
      THEN
         RETURN ASCII (p_str) + 4;
      END IF;

      IF (p_str = '+')
      THEN
         RETURN 62;
      END IF;

      IF (p_str = '/')
      THEN
         RETURN 63;
      END IF;

      RAISE_APPLICATION_ERROR (-20001, 'Invalid character: ' || p_str);
   END TO_INTEGER;

   FUNCTION TO_ATTRIBUTE_ID (p_str IN VARCHAR2)
      RETURN INTEGER
   AS
      v_result   INTEGER := 0;
   BEGIN
      FOR i IN REVERSE 1 .. LENGTH (p_str)
      LOOP
         v_result := v_result * 64 + TO_INTEGER (SUBSTR (p_str, i, 1));
      END LOOP;

      RETURN v_result;
   END TO_ATTRIBUTE_ID;

   FUNCTION READ_VALUE (p_property_bag   IN CLOB,
                        p_attribute_id   IN INTEGER,
                        p_object_id      IN VARCHAR2,
                        p_type_name      IN VARCHAR2)
      RETURN CLOB
   AS
   BEGIN
      RETURN READ_VALUE (CLOB2BLOB (p_property_bag),
                         p_attribute_id,
                         p_object_id,
                         p_type_name);
   END READ_VALUE;

   FUNCTION READ_VALUE (p_property_bag IN CLOB, p_attribute_id IN INTEGER)
      RETURN CLOB
   AS
   BEGIN
      RETURN READ_VALUE (p_property_bag,
                         p_attribute_id,
                         NULL,
                         NULL);
   END READ_VALUE;

   FUNCTION READ_VALUE (p_property_bag   IN BLOB,
                        p_attribute_id   IN INTEGER,
                        p_object_id      IN VARCHAR2,
                        p_type_name      IN VARCHAR2)
      RETURN CLOB
   AS
      v_offset         INTEGER := 0;
      v_attribute_id   INTEGER;
      v_token          VARCHAR (2000);
      v_data_length    INTEGER;
      v_value_count    INTEGER;
      v_repeating      BOOLEAN;
      v_data_type      INTEGER;
      v_encoding       VARCHAR (1);
      v_result         BLOB;
   BEGIN
      IF (p_attribute_id IS NULL)
      THEN
         RETURN NULL;
      END IF;

      v_token := NEXT_TOKEN (p_property_bag, v_offset, 1);

      IF (p_type_name IS NOT NULL AND v_token != p_type_name)
      THEN
         RAISE_APPLICATION_ERROR (
            -20001,
               'Type name does not match, expected: '
            || p_type_name
            || ' got: '
            || v_token);
      END IF;

      v_token := NEXT_TOKEN (p_property_bag, v_offset);

      IF (p_object_id IS NOT NULL AND v_token != p_object_id)
      THEN
         RAISE_APPLICATION_ERROR (
            -20001,
               'Id does not match, expected: '
            || p_object_id
            || ', got: '
            || v_token);
      END IF;

      v_token := NEXT_TOKEN (p_property_bag, v_offset, 2);

      IF (v_token != 'OBJ')
      THEN
         RAISE_APPLICATION_ERROR (-20001, 'Expected OBJ, got: ' || v_token);
      END IF;

      v_token := NEXT_TOKEN (p_property_bag, v_offset);

      IF (p_type_name IS NOT NULL AND v_token != p_type_name)
      THEN
         RAISE_APPLICATION_ERROR (
            -20001,
               'Type name does not match, expected: '
            || p_type_name
            || ' got: '
            || v_token);
      END IF;

      FOR i IN 1 .. TO_NUMBER (NEXT_TOKEN (p_property_bag, v_offset, 3))
      LOOP
         v_attribute_id :=
            TO_ATTRIBUTE_ID (NEXT_TOKEN (p_property_bag, v_offset));
         v_repeating := ('R' = NEXT_TOKEN (p_property_bag, v_offset));
         v_data_type := TO_NUMBER (NEXT_TOKEN (p_property_bag, v_offset));
         v_value_count := 1;

         IF (v_repeating)
         THEN
            v_value_count := TO_NUMBER (NEXT_TOKEN (p_property_bag, v_offset));
         END IF;

         IF (v_attribute_id = p_attribute_id)
         THEN
            DBMS_LOB.CREATETEMPORARY (v_result, TRUE);
         END IF;

         FOR j IN 1 .. v_value_count
         LOOP
            v_encoding := NEXT_TOKEN (p_property_bag, v_offset);
            v_data_length := TO_NUMBER (NEXT_TOKEN (p_property_bag, v_offset));

            IF ('H' = v_encoding)
            THEN
               v_data_length := v_data_length * 2;
            END IF;

            v_offset := v_offset + 1;

            IF (v_attribute_id = p_attribute_id)
            THEN
               READ_BDATA (p_property_bag,
                           v_data_length,
                           'H' = v_encoding,
                           v_offset,
                           v_result);

               IF (j < v_value_count AND v_data_length > 0)
               THEN
                  DBMS_LOB.APPEND (v_result, UTL_RAW.CAST_TO_RAW (', '));
               END IF;
            ELSE
               v_offset := v_offset + v_data_length;
            END IF;
         END LOOP;

         IF (v_attribute_id = p_attribute_id)
         THEN
            RETURN BLOB2CLOB (v_result);
         END IF;
      END LOOP;

      RETURN NULL;
   END READ_VALUE;

   FUNCTION READ_VALUE (p_property_bag IN BLOB, p_attribute_id IN INTEGER)
      RETURN CLOB
   AS
   BEGIN
      RETURN READ_VALUE (p_property_bag,
                         p_attribute_id,
                         NULL,
                         NULL);
   END READ_VALUE;

   FUNCTION READ_VALUE (p_object_id   IN VARCHAR2,
                        p_type_name   IN VARCHAR2,
                        p_attr_name   IN VARCHAR2)
      RETURN CLOB
   AS
      v_property_bag   BLOB;
      v_attribute_id   INTEGER;
   BEGIN
      DBMS_LOB.CREATETEMPORARY (v_property_bag, TRUE);
      READ_BAG_PROPERTY (p_object_id,
                         p_type_name,
                         FALSE,
                         v_property_bag);
      READ_BAG_PROPERTY (p_object_id,
                         p_type_name,
                         TRUE,
                         v_property_bag);
      v_attribute_id := GET_ATTBIBUTE_ID (p_type_name, p_attr_name);
      RETURN READ_VALUE (v_property_bag,
                         v_attribute_id,
                         p_object_id,
                         p_type_name);
   END READ_VALUE;

   FUNCTION READ_VALUE (p_object_id IN VARCHAR2, p_attr_name IN VARCHAR2)
      RETURN CLOB
   AS
      v_type_name   VARCHAR2 (32);
   BEGIN
      v_type_name := GET_TYPE_NAME (p_object_id);
      RETURN READ_VALUE (p_object_id, v_type_name, p_attr_name);
   END READ_VALUE;
END DM_NONQUALIFIABLE;
/