How to call kernel32.dll from PL/SQL.

This is also based on on one of my postings in OTN. Here, I will demonstrate, How to call kernel32.dll from PL/SQL.
By the way, I have copied kernel32.dll from C:\WINDOWS\system32 to C:\oracle\product\10.2.0\db_3\bin
My listener.ora setup

SID_LIST_LISTENEREXTPROC =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (ENV = "EXTPROC_DLLS=ONLY:C:\oracle\product\10.2.0\db_3\bin\kernel32.dll")
      (SID_NAME = extproc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_3)
    )
  )

My tnsnames.ora setup

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )

  )

A little verification.

LSNRCTL> stop  LISTENEREXTPROC
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
The command completed successfully
LSNRCTL> start LISTENEREXTPROC
Starting tnslsnr: please wait...
 
TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_3\network\admin\listener.o
a
Log messages written to C:\oracle\product\10.2.0\db_3\network\log\listenerextpr
c.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\extprocipc
))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
STATUS of the LISTENER
------------------------
Alias                     LISTENEREXTPROC
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Prod
ction
Start Date                21-JAN-2011 17:32:26
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_3\network\admin\listener.
ra
Listener Log File         C:\oracle\product\10.2.0\db_3\network\log\listenerext
roc.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\extprocipc)))
Services Summary...
Service "extproc" has 1 instance(s).
  Instance "extproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit
 
C:\Documents and Settings\Administrator>

C:\>tnsping EXTPROC_CONNECTION_DATA
 
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-JAN-2
011 21:53:10
 
Copyright (c) 1997, 2006, Oracle.  All rights reserved.
 
Used parameter files:
C:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)
(KEY = EXTPROC1))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))
OK (70 msec)
 
C:\>

Now, the actual codes.
SQL> CREATE OR REPLACE LIBRARY kernel32 AS 'C:\oracle\product\10.2.0\db_3\bin\kernel32.dll';
  2  / 
 
Library created.
 
SQL> CREATE OR REPLACE FUNCTION CreateFile (FileName VARCHAR2 --1
  2                                         ,p_DesiredAccess BINARY_INTEGER --2
  3                                         ,p_ShareMode BINARY_INTEGER --3
  4                                         ,p_SecurityAttributes BINARY_INTEGER --4
  5                                         ,p_CreationDisposition BINARY_INTEGER --5
  6                                         ,p_FlagsAndAttributes BINARY_INTEGER --6
  7                                         ,p_TemplateFile BINARY_INTEGER )--7
  8  Return BINARY_INTEGER
  9   IS EXTERNAL
 10  LIBRARY kernel32 Name "CreateFileA"
 11  PARAMETERS (FileName STRING  
 12              ,p_DesiredAccess long
 13              ,p_ShareMode long
 14              ,p_SecurityAttributes long
 15              ,p_CreationDisposition long
 16              ,p_FlagsAndAttributes long
 17              ,p_TemplateFile long
 18              ,return long );
 19  / 
 
Function created.
 
SQL> /* This is for closing the handle after use. */
SQL> CREATE OR REPLACE FUNCTION CloseFile (p_FileHandle BINARY_INTEGER)
  2                                        Return BINARY_INTEGER
  3   IS EXTERNAL
  4   LIBRARY kernel32 Name "CloseHandle"
  5   PARAMETERS (p_FileHandle long, return long);
  6  / 
 
Function created.
 
SQL> /* This is the main function for getting size */
SQL> CREATE OR REPLACE FUNCTION GetSize (p_FileHandle BINARY_INTEGER, 
  2                                      p_FileSizeHigh IN OUT BINARY_INTEGER) 
  3                                      RETURN BINARY_INTEGER
  4   IS EXTERNAL
  5  LIBRARY kernel32 NAME "GetFileSize"
  6  PARAMETERS (p_FileHandle long, p_FileSizeHigh long, return long );
  7  / 
 
Function created.
 
SQL> set serverout on
SQL> DECLARE
  2    v_FileSize BINARY_INTEGER;
  3    v_FileSizeHigh PLS_INTEGER;
  4    v_FileHandle BINARY_INTEGER;
  5    v_filename VARCHAR2(500) :='C:\test2.csv';
  6    v_dummy BINARY_INTEGER;
  7  BEGIN
  8    v_FileSizeHigh := 400000000;
  9    v_FileHandle:=CreateFile(v_filename -- File name
 10                             ,0 -- Type of access required (read/write ect)
 11                             ,0 -- disable share mode
 12                             ,0 --no securoty attribute
 13                             ,3 -- Means Open existing
 14                             ,128 --080h, File attribute normal.
 15                             ,0); --7
 16    v_FileSize := Getsize(v_FileHandle, v_FileSizeHigh);
 17    DBMS_OUTPUT.put_line('File Size in Bytes: ' ||v_FileSize);
 18    v_dummy:=CloseFile(v_FileHandle);
 19  END;
 20  / 
File Size in Bytes: 61
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> 
SQL> DECLARE
  2    v_FileSize BINARY_INTEGER;
  3    v_FileSizeHigh PLS_INTEGER;
  4    v_FileHandle BINARY_INTEGER;
  5    v_filename VARCHAR2(500) :='C:\Winter.jpg';
  6    v_dummy BINARY_INTEGER;
  7  BEGIN
  8    v_FileSizeHigh := 400000000;
  9    v_FileHandle:=CreateFile(v_filename -- File name
 10                             ,0 -- Type of access required (read/write ect)
 11                             ,0 -- disable share mode
 12                             ,0 --no securoty attribute
 13                             ,3 -- Means Open existing
 14                             ,128 --080h, File attribute normal.
 15                             ,0); --7
 16    v_FileSize := Getsize(v_FileHandle, v_FileSizeHigh);
 17    DBMS_OUTPUT.put_line('File Size in Bytes: ' ||v_FileSize);
 18    v_dummy:=CloseFile(v_FileHandle);
 19  END;
 20  / 
File Size in Bytes: 105542
 
PL/SQL procedure successfully completed.
 
SQL>
Verification.

C:\>dir test2.csv
 Volume in drive C has no label.
 Volume Serial Number is 6806-ABBD
 
 Directory of C:\
 
12/15/2010  01:35 PM                61 test2.csv
               1 File(s)             61 bytes
               0 Dir(s)   3,405,336,576 bytes free
 
C:\>dir Winter.jpg
 Volume in drive C has no label.
 Volume Serial Number is 6806-ABBD
 
 Directory of C:\
 
10/11/2010  05:27 PM           105,542 Winter.jpg
               1 File(s)        105,542 bytes
               0 Dir(s)   3,405,336,576 bytes free
 
C:\>

How to find column count of a query.

This is based on one of my post in OTN. Where an user want to count the columns of a dynamic SQL query.
SQL> CREATE OR REPLACE FUNCTION count_column(pi_sql_statement IN VARCHAR2)
  2    RETURN INTEGER IS
  3    cur_handle NUMBER;
  4    col_count  INTEGER;
  5    rec_tab    DBMS_SQL.DESC_TAB;
  6    var1           NUMBER;
  7  BEGIN
  8    cur_handle := DBMS_SQL.OPEN_CURSOR;
  9    DBMS_SQL.PARSE(cur_handle, pi_sql_statement, DBMS_SQL.NATIVE);
 10    var1 := DBMS_SQL.EXECUTE(cur_handle);
 11    DBMS_SQL.DESCRIBE_COLUMNS(cur_handle, col_count, rec_tab);
 12    DBMS_SQL.close_cursor(cur_handle);
 13    RETURN col_count;
 14  END count_column;
 15  /

Function created.

SQL> SELECT count_column('SELECT ename,sal FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         2

SQL> SELECT count_column('SELECT ename,sal,deptno FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         3

SQL> SELECT count_column('SELECT * FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         8

SQL>

How to convert NOARCHIVELOG to ARCHIVELOG

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 18 13:36:02 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1290208 bytes
Variable Size             201326624 bytes
Database Buffers           83886080 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Simple demo of DBMS_ERRLOG

This is a simple demo of DBMS_ERRLOG. This is also based on one of my posting in OTN.

SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> /* My first table with some duplicate records */
SQL> CREATE TABLE test_dbms_error_log1(col1 INTEGER);

Table created.

SQL> INSERT INTO test_dbms_error_log1 VALUES (&col1);
Enter value for col1: 1
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (1)

1 row created.

SQL> /
Enter value for col1: 2
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (2)

1 row created.

SQL> /
Enter value for col1: 3
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (3)

1 row created.

SQL> /
Enter value for col1: 4
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (4)

1 row created.

SQL> /
Enter value for col1: 4
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (4)

1 row created.

SQL> /
Enter value for col1: 1
old   1: INSERT INTO test_dbms_error_log1 VALUES (&col1)
new   1: INSERT INTO test_dbms_error_log1 VALUES (1)

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM test_dbms_error_log1;

      COL1
----------
         1
         2
         3
         4
         4
         1

6 rows selected.

SQL> /* My second table with primary key. I will insert into test_dbms_error_log2
DOC>   from test_dbms_error_log1.
DOC>   Because of duplicate rows into test_dbms_error_log1, Some rows will get rejected. */
SQL> CREATE TABLE test_dbms_error_log2(col1 INTEGER primary key);

Table created.
SQL> BEGIN
  2   DBMS_ERRLOG.create_error_log(dml_table_name =>'TEST_DBMS_ERROR_LOG2');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> /* The above statement will create a table named err$_TEST_DBMS_ERROR_LOG2 */
SQL>
SQL> /* Now my insert statement */
SQL> INSERT INTO TEST_DBMS_ERROR_LOG2 SELECT * FROM TEST_DBMS_ERROR_LOG1
  2  LOG ERRORS REJECT LIMIT UNLIMITED;

4 rows created.

SQL> SELECT * FROM test_dbms_error_log2;

      COL1
----------
         1
         2
         3
         4

SQL> /* So, two rows got rejected. */
SQL> 

SQL> desc err$_TEST_DBMS_ERROR_LOG2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 COL1                                               VARCHAR2(4000)

SQL> SELECT ora_err_number$,SUBSTR(ora_err_mesg$,1,50) err_msg
  2         ,ora_err_rowid$ rwid, col1 what_is_rejected
  3  FROM err$_test_dbms_error_log2;

ORA_ERR_NUMBER$ ERR_MSG
--------------- --------------------------------------------------
RWID
--------------------------------------------------------------------------------
WHAT_IS_REJECTED
--------------------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.SYS_C005514) v

4

              1 ORA-00001: unique constraint (SCOTT.SYS_C005514) v

1

ORA_ERR_NUMBER$ ERR_MSG
--------------- --------------------------------------------------
RWID
--------------------------------------------------------------------------------
WHAT_IS_REJECTED
--------------------------------------------------------------------------------

Sending binary attachment/images in mail with UTL_SMTP.

This is based on my posting on OTN for sending binary attachment using UTL_SMTP.
Here is my file (images) and DIRECTORY object setups.

C:\>dir *.jpg
 Volume in drive C has no label.
 Volume Serial Number is 6806-ABBD
 
 Directory of C:\
 
08/04/2004  10:30 AM            83,794 Waterlilies.jpg
10/11/2010  05:27 PM           105,542 Winter.jpg
               2 File(s)        189,336 bytes
               0 Dir(s)   8,408,399,872 bytes free
 
C:\>sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 11 17:42:37 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> /* I have already created DIRECTORY OBJECT */
SQL> SELECT directory_name,directory_path FROM dba_directories
  2  WHERE directory_name='SAUBHIK';
 
DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------
 
SAUBHIK
C:\
 
 
SQL>

Now my actual code.

SQL> conn scott@ORCLSB
Enter password: *****
Connected.
SQL> DECLARE
  2    /*LOB operation related varriables */
  3    v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
  4    l_buffer   RAW(54);
  5    l_amount   BINARY_INTEGER := 54;
  6    l_pos      INTEGER := 1;
  7    l_blob     BLOB := EMPTY_BLOB;
  8    l_blob_len INTEGER;
  9    v_amount   INTEGER;
 10  
 11    /*UTL_SMTP related varriavles. */
 12    v_connection_handle  UTL_SMTP.CONNECTION;
 13    v_from_email_address VARCHAR2(30) := 'aaaa@bb.com';
 14    v_to_email_address   VARCHAR2(30) := 'xxxx@yy.com';
 15    v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --My mail server, replace it with yours.
 16    v_subject            VARCHAR2(30) := 'Your Test Mail';
 17    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
 18  
 19    /* This send_header procedure is written in the documentation */
 20    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
 21    BEGIN
 22      UTL_SMTP.WRITE_DATA(v_connection_handle,
 23                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
 24    END;
 25  
 26  BEGIN
 27    /*Preparing the LOB from file for attachment. */
 28    DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
 29    DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
 30    v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
 31    DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
 32    l_blob_len := DBMS_LOB.getlength(l_blob);
 33  
 34    /*UTL_SMTP related coding. */
 35    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
 36    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
 37    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
 38    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
 39    UTL_SMTP.OPEN_DATA(v_connection_handle);
 40    send_header('From', '"Sender" <'>');
 41    send_header('To', '"Recipient" <'>');
 42    send_header('Subject', v_subject);
 43  
 44    --MIME header.
 45    UTL_SMTP.WRITE_DATA(v_connection_handle,
 46                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
 47    UTL_SMTP.WRITE_DATA(v_connection_handle,
 48                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
 49    UTL_SMTP.WRITE_DATA(v_connection_handle,
 50                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
 51                        UTL_TCP.CRLF);
 52    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 53  
 54    -- Mail Body
 55    UTL_SMTP.WRITE_DATA(v_connection_handle,
 56                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 57    UTL_SMTP.WRITE_DATA(v_connection_handle,
 58                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
 59    UTL_SMTP.WRITE_DATA(v_connection_handle,
 60                        ' charset=US-ASCII' || UTL_TCP.CRLF);
 61    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 62    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
 63    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 64  
 65    -- Mail Attachment
 66    UTL_SMTP.WRITE_DATA(v_connection_handle,
 67                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 68    UTL_SMTP.WRITE_DATA(v_connection_handle,
 69                        'Content-Type: application/octet-stream' ||
 70                        UTL_TCP.CRLF);
 71    UTL_SMTP.WRITE_DATA(v_connection_handle,
 72                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
 73    UTL_SMTP.WRITE_DATA(v_connection_handle,
 74                        ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
 75                        UTL_TCP.CRLF);
 76    UTL_SMTP.WRITE_DATA(v_connection_handle,
 77                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
 78    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 79  /* Writing the BLOL in chunks */
 80    WHILE l_pos < l_blob_len LOOP
 81      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
 82      UTL_SMTP.write_raw_data(v_connection_handle,
 83                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
 84      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 85      l_buffer := NULL;
 86      l_pos    := l_pos + l_amount;
 87    END LOOP;
 88    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 89  
 90    -- Close Email
 91    UTL_SMTP.WRITE_DATA(v_connection_handle,
 92                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
 93    UTL_SMTP.WRITE_DATA(v_connection_handle,
 94                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
 95  
 96    UTL_SMTP.CLOSE_DATA(v_connection_handle);
 97    UTL_SMTP.QUIT(v_connection_handle);
 98    DBMS_LOB.FREETEMPORARY(l_blob);
 99    DBMS_LOB.FILECLOSE(v_src_loc);
100  
101  EXCEPTION
102    WHEN OTHERS THEN
103      UTL_SMTP.QUIT(v_connection_handle);
104      DBMS_LOB.FREETEMPORARY(l_blob);
105      DBMS_LOB.FILECLOSE(v_src_loc);
106      RAISE;
107  END;
108  / 
 
PL/SQL procedure successfully completed.
 
SQL>

How to get back truncated data using DBMS_LOGMNR

This is also posted in OTN by me.
My test database is not in ARCHIVELOG mode. So

C:\Documents and Settings\Administrator>sqlplus /nolog
 
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 18 13:52:32 2010
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size             109055128 bytes
Database Buffers          171966464 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> alter database archivelog
  2  ;
 
Database altered.
 
SQL> alter database open
  2  ;
 
Database altered.
 
SQL> SELECT log_mode FROM v$database;
 
LOG_MODE
------------
ARCHIVELOG
 

SQL>

By the way in my version once the recovery has failed until I have used:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
Database altered.

Now the main part:

SQL> conn hr@xe
Enter password: **
Connected.
SQL> drop table emp_test_logminer purge;
 
Table dropped.
 
SQL> CREATE TABLE emp_test_logminer AS SELECT * FROM emp;
 
Table created.
 
SQL> SELECT COUNT(*) FROM emp_test_logminer;
 
  COUNT(*)
----------
        14
 
SQL> TRUNCATE TABLE emp_test_logminer;
 
Table truncated.
 
SQL> SELECT COUNT(*) FROM emp_test_logminer;
 
  COUNT(*)
----------
         0
 
SQL> conn sys@xe as sysdba
Enter password: ******
Connected.
SQL>   BEGIN
  2        DBMS_LOGMNR.START_LOGMNR (
  3              starttime => '18-NOV-2010 14:40:00',
  4              endtime   => '18-NOV-2010 14:45:00',
  5              options   => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+
  6                           dbms_logmnr.continuous_mine +
  7                           dbms_logmnr.no_sql_delimiter +
  8                           dbms_logmnr.print_pretty_sql
  9                                     );
 10    END;
 11  / 
  BEGIN
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 2
 
 
SQL> ALTER SESSION SET nls_date_format='DD-MON-RRRR hh24:mi:ss';
 
Session altered.
 
SQL> BEGIN
  2      DBMS_LOGMNR.START_LOGMNR (
  3            starttime => '18-NOV-2010 14:40:00',
  4            endtime   => '18-NOV-2010 14:45:00',
  5            options   => dbms_logmnr.DICT_FROM_ONLINE_CATALOG+
  6                         dbms_logmnr.continuous_mine +
  7                         dbms_logmnr.no_sql_delimiter +
  8                         dbms_logmnr.print_pretty_sql
  9                                   );
 10  END;
 11  / 
 
PL/SQL procedure successfully completed.
 
SQL> SELECT scn , sql_redo , sql_undo from v$logmnr_contents
  2  WHERE username = 'HR'
  3  AND   seg_name = 'EMP_TEST_LOGMINER';
 
       SCN SQL_REDO                            SQL_UNDO
---------- ----------------------------------- -----------------------------------
  23190408 drop table emp_test_logminer purge
  23190684 CREATE TABLE emp_test_logminer AS S
           ELECT * FROM emp
 
  23190689 insert into "HR"."EMP_TEST_LOGMINER delete from "HR"."EMP_TEST_LOGMINER
           "                                   "
            values                              where
               "EMPNO" = 7839,                     "EMPNO" = 7839 and
               "ENAME" = 'KING',                   "ENAME" = 'KING' and
               "JOB" = 'PRESIDENT',                "JOB" = 'PRESIDENT' and
               "MGR" IS NULL,                      "MGR" IS NULL and
               "HIREDATE" = TO_DATE('17-NOV-19     "HIREDATE" = TO_DATE('17-NOV-19
           81 00:00:00', 'DD-MON-RRRR hh24:mi: 81 00:00:00', 'DD-MON-RRRR hh24:mi:
           ss'),                               ss') and
               "SAL" = 5000,                       "SAL" = 5000 and
               "COMM" IS NULL,                     "COMM" IS NULL and
               "DEPTNO" = 10                       "DEPTNO" = 10 and
                                                   ROWID = 'AAAFRsAABAAAMFKAAA'
.................................................................................................

.................................................................................................

So all the 14 rows are there. You can now recover from this.

Validate XML stored in a CLOB is well formed or not.

You can use DBMS_XMLPARSER to parse an XML stored in a CLOB 

to find whether it is well formed or not.

I have posted this code in OTN also. 

SQL> set serverout on
SQL> DECLARE
  2    v_clob   CLOB := '<?xml version="1.0"?>
  3                      <ROWSET>
  4                      <IBSCOLYTD>
  5                      <ACTNOI>28004125</ACTNOI>
  6                      <MEMONOI>251942</MEMONOI>
  7                      <MEMODTEI>05-SEP-92</MEMODTEI>
  8                      <AMOUNTI>400</AMOUNTI>
  9                      <BRCDSI>513</BRCDSI>
 10                      <TYPEI>1</TYPEI>
 11                      <TRANSMONI>0</TRANSMONI>
 12                      </IBSCOLYTD>
 13                      <IBSCOLYTD>
 14                      <ACTNOI>28004125</ACTNOI>
 15                      <MEMONOI>251943</MEMONOI>
 16                      <MEMODTEI>04-OCT-92</MEMODTEI>
 17                      <AMOUNTI>400</AMOUNTI>
 18                      <BRCDSI>513</BRCDSI>
 19                      <TYPEI>1</TYPEI>
 20                      <TRANSMONI>0</TRANSMONI>
 21                      </IBSCOLYTD>
 22                      ';
 23    l_parser DBMS_XMLPARSER.Parser;
 24  BEGIN
 25    -- Create a parser.
 26    l_parser := DBMS_XMLPARSER.newParser;
 27    -- Parse the document 
 28    DBMS_XMLPARSER.parseClob(l_parser, v_clob);
 29    DBMS_XMLPARSER.freeParser(l_parser);
 30    DBMS_OUTPUT.put_line('Parsing Success');
 31  EXCEPTION
 32    WHEN OTHERS THEN
 33      DBMS_OUTPUT.put_line('Parsing failed');
 34      DBMS_XMLPARSER.freeParser(l_parser);
 35    
 36  END;
 37  / 
Parsing failed
 
PL/SQL procedure successfully completed.
 
SQL> DECLARE
  2    v_clob   CLOB := '<?xml version="1.0"?>
  3                      <ROWSET>
  4                      <IBSCOLYTD>
  5                      <ACTNOI>28004125</ACTNOI>
  6                      <MEMONOI>251942</MEMONOI>
  7                      <MEMODTEI>05-SEP-92</MEMODTEI>
  8                      <AMOUNTI>400</AMOUNTI>
  9                      <BRCDSI>513</BRCDSI>
 10                      <TYPEI>1</TYPEI>
 11                      <TRANSMONI>0</TRANSMONI>
 12                      </IBSCOLYTD>
 13                      <IBSCOLYTD>
 14                      <ACTNOI>28004125</ACTNOI>
 15                      <MEMONOI>251943</MEMONOI>
 16                      <MEMODTEI>04-OCT-92</MEMODTEI>
 17                      <AMOUNTI>400</AMOUNTI>
 18                      <BRCDSI>513</BRCDSI>
 19                      <TYPEI>1</TYPEI>
 20                      <TRANSMONI>0</TRANSMONI>
 21                      </IBSCOLYTD>
 22                      </ROWSET>      
 23                      '; --The last line added to make it ok.
 24    l_parser DBMS_XMLPARSER.Parser;
 25  BEGIN
 26    -- Create a parser.
 27    l_parser := DBMS_XMLPARSER.newParser;
 28    -- Parse the document 
 29    DBMS_XMLPARSER.parseClob(l_parser, v_clob);
 30    DBMS_XMLPARSER.freeParser(l_parser);
 31    DBMS_OUTPUT.put_line('Parsing Success');
 32  EXCEPTION
 33    WHEN OTHERS THEN
 34      DBMS_OUTPUT.put_line('Parsing failed');
 35      DBMS_XMLPARSER.freeParser(l_parser);
 36    
 37  END;
 38  / 
Parsing Success
 
PL/SQL procedure successfully completed.
 
SQL>