2 Replies Latest reply: Apr 8, 2013 8:30 AM by 912502 RSS

    SQL query to get XML from audit_details Table.

    1000155
      Hi All ,

      I am trying to execute below query to get the XML from audit_details table for a particular instance .

      This is failing with the below Error .Please help me ,I need this urgentley .


      SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_COMPRESS.LZ_UNCOMPRESS(ci.bin))
      FROM PROD_SOAINFRA.audit_details ci
      WHERE cikey = 848063749

      Errors:
      ----------------------------------------------------------------
      ORA-29261: bad argument
      ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 56
      ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 226
      ORA-06512: at "SYS.UTL_COMPRESS", line 89
      29261. 00000 - "bad argument"
      *Cause:    A bad argument was passed to the PL/SQL API.
      *Action:   Check the arguments passed to the PL/SQL API and retry the call.

      Thanks in Advance
      Surfraz Mitegar
        • 1. Re: SQL query to get XML from audit_details Table.
          1000155
          hi All,

          I tried below . but still no luck .

          CREATE OR REPLACE FUNCTION get_audit_trail_log(cikey IN INTEGER) RETURN blob IS
          CURSOR c_log(l_cikey INTEGER) IS
          SELECT *
          FROM PROD_SOAINFRA.audit_details atr
          WHERE cikey = l_cikey
          ORDER BY count_id;

          bl BLOB;
          BEGIN
          dbms_lob.createtemporary (bl, TRUE);
          FOR r_log IN c_log(cikey)
          LOOP
          dbms_lob.append (bl,r_log.log);
          END LOOP;

          RETURN(bl);
          END;

          =======================================

          SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_COMPRESS.LZ_UNCOMPRESS(get_audit_trail_log(ci.cikey)))
          FROM PROD_SOAINFRA.audit_details ci
          WHERE cikey = 848063749

          =======================================

          Error:
          =====================
          ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 20958, maximum: 2000)
          22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
          *Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
          the LOB size was bigger than the buffer limit for CHAR and RAW
          types.
          Note that widths are reported in characters if character length
          semantics are in effect for the column, otherwise widths are
          reported in bytes.
          *Action:   Do one of the following
          1. Make the LOB smaller before performing the conversion,
          for example, by using SUBSTR on CLOB
          2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.
          • 2. Re: SQL query to get XML from audit_details Table.
            912502
            Can anybody please help me on this .