Tuesday, February 22, 2011

Searching and Highlighting text in pdfs (Stored as BLOB) using PL/SQL.

We can search words withing a pdf (stored as BLOB in a table) and can highlight them and store them as html into CLOB using Oracle Text. This demo is also based on one of my posting in OTN.
SQL> /* The database version in which I am working */
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> /* Creating my demo table */
SQL> CREATE TABLE pdm(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(30),pdf_file BLOB);

Table created.

SQL> /* Creating the directory object for latter use */
SQL> CREATE OR REPLACE DIRECTORY TEMP AS 'c:\';

Directory created.

SQL> /* Creating a procedure which will load the BLOBs (pdf files) into the table */
SQL> CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
  2    src_file BFILE;
  3    dst_file BLOB;
  4    lgh_file BINARY_INTEGER;
  5  BEGIN
  6    src_file := bfilename('TEMP', pfname);
  7 
  8    INSERT INTO pdm
  9      (id_pk, fname, pdf_file)
 10    VALUES
 11      (pi_id, pfname, EMPTY_BLOB())
 12    RETURNING pdf_file INTO dst_file;
 13 
 14    DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
 15    lgh_file := dbms_lob.getlength(src_file);
 16    DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
 17    dbms_lob.close(src_file);
 18    COMMIT;
 19  END load_file;
 20  /

Procedure created.

SQL> /* Inserting some rows into my table (pdm) */
SQL>
SQL> EXECUTE load_file(1,'plsql_new_in_11gr1.pdf');

PL/SQL procedure successfully completed.

SQL> EXECUTE load_file(3,'conditional_compilation.pdf');

PL/SQL procedure successfully completed.

SQL>
SQL> /* Checking the rows just inserted in the table */
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file) file_length
  2  FROM pdm;

     ID_PK FNAME                          FILE_LENGTH
---------- ------------------------------ -----------
         1 plsql_new_in_11gr1.pdf              170264
         3 conditional_compilation.pdf         540594

SQL>
SQL> /* Creating the index */
SQL>
SQL> CREATE INDEX pdm_pdf_idx ON pdm(pdf_file) INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL> /* Checking for any errors*/
SQL> SELECT * FROM ctx_user_index_errors;

no rows selected

SQL> /* Checking the index */
SQL> SELECT idx_name,idx_table_owner,idx_table,idx_status,idx_type
  2  FROM ctx_user_indexes
  3  WHERE idx_name='PDM_PDF_IDX';

IDX_NAME                       IDX_TABLE_OWNER
------------------------------ ------------------------------
IDX_TABLE                      IDX_STATUS   IDX_TYP
------------------------------ ------------ -------
PDM_PDF_IDX                    SCOTT
PDM                            INDEXED      CONTEXT


SQL> /* Just gathering some statistics */
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PDM', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> /* Firing some quries to check the setup */
SQL>
SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'The Catch 22')>0;

     ID_PK FNAME
---------- ------------------------------
         3 conditional_compilation.pdf

SQL>
SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'Usability language feature:')>0;

     ID_PK FNAME
---------- ------------------------------
         1 plsql_new_in_11gr1.pdf

SQL>  
SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'blah blah blah')>0;

no rows selected

SQL> 
SQL> /* Chreting table to hold the markuped html as CLOB */
SQL>
SQL> CREATE TABLE pdm_markup_table (
  2    query_id  NUMBER,
  3    document  CLOB
  4  );
CREATE TABLE pdm_markup_table (
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> /* Chreting table to hold the markuped html as CLOB */
SQL>
SQL> CREATE TABLE pdm_markup_table (
  2    query_id  NUMBER,
  3    document  CLOB
  4  );

Table created.

SQL> /* This anonymous PL/SQL block will search the text from the pdfs
SQL>    and place those into pdm_markup_table.document as html
SQL> */
SQL>
SQL> DECLARE
  2    l_keywords VARCHAR2(200) := 'The Catch 22';
  3    l_query_id INTEGER := 1;
  4  BEGIN
  5    FOR pdm_cur IN (SELECT id_pk, fname
  6                      FROM pdm
  7                     WHERE CONTAINS(pdf_file, l_keywords) > 0)
  8    /* although, We know from the query fired earlier that only one row will be returned
  9       We are checking as if there may be several pdfs with the search words
 10    */
 11     LOOP
 12      -- Generate HTML version of document with
 13      -- highlighted search terms.
 14      CTX_DOC.markup(index_name => 'PDM_PDF_IDX', --My index
 15                     textkey    => TO_CHAR(pdm_cur.id_pk),
 16                     text_query => l_keywords,
 17                     restab     => 'pdm_markup_table', --My table
 18                     query_id   => l_query_id,
 19                     plaintext  => FALSE,
 20                     tagset     => 'HTML_NAVIGATE');
 21   
 22      l_query_id := l_query_id + 1;
 23   
 24    END LOOP;
 25  END;
 26  /

PL/SQL procedure successfully completed.

SQL> SELECT query_id,DBMS_LOB.getlength(document)
  2  FROM pdm_markup_table;

  QUERY_ID DBMS_LOB.GETLENGTH(DOCUMENT)
---------- ----------------------------
         1                      1170359

SQL> /* A small snippet of the html document */
SQL> SELECT query_id,DBMS_LOB.substr(document,300,1)
  2  FROM pdm_markup_table;

  QUERY_ID
----------
DBMS_LOB.SUBSTR(DOCUMENT,300,1)
--------------------------------------------------------------------------------
         1
<html><body><p/>
<br /><div style="position:absolute;top:961px;left:68px;font-family:'Times New R
oman';font-size:12pt;white-space:nowrap;">
</div>
<div style="position:absolute;top:139px;left:408px;font-family:'Times New Roman'
;font-size:17pt;white-space:nowrap;">PL/SQL conditional compilation
</div


SQL> 

Saturday, February 19, 2011

How to send mail when cursor limit exceeds (ORA-01000)

This is based on one of my posting in OTN. We will use AFTER SERVERERROR ON schema trigger along with UTL_SMTP to send mail.
SQL> conn scott@orclsb
Enter password: *****
Connected.
SQL>
SQL> /** The databse version in which this executed **/
SQL>
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> /** This package will be used for generating the
SQL>     ORA-0100: Maximum open cursors exceeded error **/
SQL> CREATE OR REPLACE PACKAGE test_open_cursor  AS
  2    cursor c01 is select * from dual;
  3    cursor c02 is select * from dual;
  4    cursor c03 is select * from dual;
  5    cursor c04 is select * from dual;
  6    cursor c05 is select * from dual;
  7    cursor c06 is select * from dual;
  8    cursor c07 is select * from dual;
  9    cursor c08 is select * from dual;
 10    cursor c09 is select * from dual;
 11    cursor c10 is select * from dual;
 12    cursor c11 is select * from dual;
 13    cursor c12 is select * from dual;
 14    cursor c13 is select * from dual;
 15    cursor c14 is select * from dual;
 16    cursor c15 is select * from dual;
 17    cursor c16 is select * from dual;
 18    cursor c17 is select * from dual;
 19    cursor c18 is select * from dual;
 20    cursor c19 is select * from dual;
 21    cursor c20 is select * from dual;
 22    cursor c21 is select * from dual;
 23    cursor c22 is select * from dual;
 24    cursor c23 is select * from dual;
 25    cursor c24 is select * from dual;
 26    cursor c25 is select * from dual;
 27    cursor c26 is select * from dual;
 28    cursor c27 is select * from dual;
 29    cursor c28 is select * from dual;
 30    cursor c29 is select * from dual;
 31    cursor c30 is select * from dual;
 32    cursor c31 is select * from dual;
 33    cursor c32 is select * from dual;
 34    cursor c33 is select * from dual;
 35    cursor c34 is select * from dual;
 36    cursor c35 is select * from dual;
 37    cursor c36 is select * from dual;
 38    cursor c37 is select * from dual;
 39    cursor c38 is select * from dual;
 40    cursor c39 is select * from dual;
 41    cursor c40 is select * from dual;
 42  END;
 43  /

Package created.

SQL> /** This procedure will be called from the trigger
SQL>     to send the mail. **/
SQL> CREATE OR REPLACE PROCEDURE send_cursor_mail AUTHID CURRENT_USER IS
  2    /*** UTL_SMTP related varriable ***/
  3    v_connection_handle  UTL_SMTP.connection;
  4    v_from_email_address VARCHAR2(30) := 'dabase_admin@oracle.com';
  5    v_to_email_address   VARCHAR2(30) := 'xyz@gmail.com';
  6    v_smtp_host          VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours
.
  7    v_subject            VARCHAR2(300) := 'ORA-0100: Maximum open cursors exceeded';
  8    l_message            VARCHAR2(32767) := 'ORA-0100: Maximum open cursors exceeded';
  9    crlf CONSTANT VARCHAR2(2):=CHR(13) || CHR(10);
 10    /* This send_header procedure is written in the documentation */
 11    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
 12    BEGIN
 13      UTL_SMTP.WRITE_DATA(v_connection_handle,
 14                          pi_name || ': ' || pi_header || crlf);
 15    END;
 16  BEGIN
 17    v_connection_handle := UTL_SMTP.open_connection(v_smtp_host);
 18    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
 19    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
 20    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
 21    UTL_SMTP.OPEN_DATA(v_connection_handle);
 22 
 23    send_header('From', '"Sender" <' || v_from_email_address || '>');
 24    send_header('To', '"Recipient" <' || v_to_email_address || '>');
 25    send_header('Subject', v_subject);
 26    UTL_SMTP.write_data(v_connection_handle, crlf || l_message);
 27    UTL_SMTP.close_data(v_connection_handle);
 28    UTL_SMTP.quit(v_connection_handle);
 29  END;
 30  /

Procedure created.

SQL> /** The trigger: This will capture the error and
SQL>     send a mail **/
SQL> CREATE OR REPLACE TRIGGER open_cur_servererr
  2    AFTER SERVERERROR ON schema
  3  BEGIN
  4    IF ora_is_servererror(1000) THEN
  5      DBMS_OUTPUT.put_line('ORA-0100: Captured in trigger');
  6      send_cursor_mail; --send the mail
  7      DBMS_OUTPUT.put_line('Mail sent to Saubhik');
  8    END IF;
  9  END;
 10  /

Trigger created.

SQL> SET SERVEROUT ON
SQL>
SQL> /** Generate the error */
SQL> BEGIN
  2    FOR i in 1 .. 40 LOOP
  3      execute immediate 'begin open test_open_cursor.c' || to_char(i, 'fm00') ||
  4                        '; end;';
  5    END LOOP;
  6  END;
  7  /
ORA-0100: Captured in trigger
Mail sent to Saubhik

BEGIN
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.TEST_OPEN_CURSOR", line 30
ORA-06512: at line 1
ORA-06512: at line 3


SQL> 

Look at the lines ORA-0100: Captured in trigger, Mail sent to Saubhik
and I have received the mail in my mail address specified in the procedure.
Another good example of this types of triggers can be found  here on OTN.

Thursday, February 17, 2011

Searching text from pdf file (BLOB) from PL/SQL.

This is based on one of my posting in OTN. We will use Oracle Text to search a string from pdf file stored in BLOB column.

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> /* Creating my demo table */
SQL> CREATE TABLE pdm(id_pk INTEGER,fname VARCHAR2(30),pdf_file BLOB);

Table created.
SQL> /* Creating the directory object for latter use */
SQL> CREATE OR REPLACE DIRECTORY TEMP AS 'c:\';

Directory created.

SQL> /* Creating a procedure which will load the BLOBs (pdf files) into the table */
SQL> CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
  2    src_file BFILE;
  3    dst_file BLOB;
  4    lgh_file BINARY_INTEGER;
  5  BEGIN
  6    src_file := bfilename('TEMP', pfname);
  7 
  8    INSERT INTO pdm
  9      (id_pk, fname, pdf_file)
 10    VALUES
 11      (pi_id, pfname, EMPTY_BLOB())
 12    RETURNING pdf_file INTO dst_file;
 13 
 14    DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
 15    lgh_file := dbms_lob.getlength(src_file);
 16    DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
 17    dbms_lob.close(src_file);
 18    COMMIT;
 19  END load_file;
 20  /

Procedure created.

SQL> EXECUTE load_file(1,'plsql_new_in_11gr1.pdf');

PL/SQL procedure successfully completed.

SQL> EXECUTE load_file(2,'Model clause.pdf');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file)
  2  FROM pdm;

     ID_PK FNAME                          DBMS_LOB.GETLENGTH(PDF_FILE)
---------- ------------------------------ ----------------------------
         1 plsql_new_in_11gr1.pdf                               170264
         2 Model clause.pdf                                    4288164

SQL> /* Creating the index */
SQL> CREATE INDEX pdm_pdf_idx ON pdm(pdf_file) INDEXTYPE IS CTXSYS.CONTEXT;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PDM', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'PL/SQL')>0;

     ID_PK FNAME
---------- ------------------------------
         1 plsql_new_in_11gr1.pdf

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'Fine-grained access control')>0;

     ID_PK FNAME
---------- ------------------------------
         1 plsql_new_in_11gr1.pdf

SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file)
  2  FROM pdm;

     ID_PK FNAME                          DBMS_LOB.GETLENGTH(PDF_FILE)
---------- ------------------------------ ----------------------------
         1 plsql_new_in_11gr1.pdf                               170264
         2 Model clause.pdf                                    4288164

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'Saubhik')>0;

no rows selected

SQL> EXECUTE load_file(3,'plsql_conditional_compilation.pdf');
BEGIN load_file(3,'plsql_conditional_compilation.pdf'); END;

*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."PDM"."FNAME" (actual: 33,
maximum: 30)
ORA-06512: at "SCOTT.LOAD_FILE", line 8
ORA-06512: at line 1


SQL> EXECUTE load_file(3,'conditional_compilation.pdf');

PL/SQL procedure successfully completed.

SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file)
  2  FROM pdm;

     ID_PK FNAME                          DBMS_LOB.GETLENGTH(PDF_FILE)
---------- ------------------------------ ----------------------------
         1 plsql_new_in_11gr1.pdf                               170264
         2 Model clause.pdf                                    4288164
         3 conditional_compilation.pdf                          540594

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PDM', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'conditional')>0;

     ID_PK FNAME
---------- ------------------------------
         1 plsql_new_in_11gr1.pdf
         3 conditional_compilation.pdf

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'The Catch 22')>0;

     ID_PK FNAME
---------- ------------------------------
         3 conditional_compilation.pdf

SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file)
  2  FROM pdm;

     ID_PK FNAME                          DBMS_LOB.GETLENGTH(PDF_FILE)
---------- ------------------------------ ----------------------------
         1 plsql_new_in_11gr1.pdf                               170264
         2 Model clause.pdf                                    4288164
         3 conditional_compilation.pdf                          540594

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'Programmers often need to trace the execution')>0;

     ID_PK FNAME
---------- ------------------------------
         3 conditional_compilation.pdf

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'Usability language feature:')>0;

     ID_PK FNAME
---------- ------------------------------
         1 plsql_new_in_11gr1.pdf

SQL> SELECT id_pk,fname
  2  FROM pdm
  3  WHERE CONTAINS(pdf_file,'blah blah blah')>0;

no rows selected



Tuesday, February 8, 2011

Generate DDLs (.SQL file) for every procedures and functions using DBMS_METADATA.

This is based on one of my posting in OTN. This shows, how to use DBMS_METADATA and DBMS_LOB.
This PL/SQL block generates a SQL file (with / character at the end of every object) for every functions and procedures.


/***************************************************************
 Generate a SQL file with ddls for any Procedures and Functions.
 This can be extended to get ddls of any objects.
****************************************************************/
DECLARE
--These varriables are to manipulate the LOBs
  myddls     CLOB := EMPTY_CLOB();
  metadata   CLOB := EMPTY_CLOB();
  ddls       CLOB := EMPTY_CLOB();
  v_amount   INTEGER;
  v_offset   INTEGER; -- Where to write.
  l_pos      INTEGER := 1;
  my_var     VARCHAR2(32767) := '/'; --Adding the / at the end.
  l_clob_len INTEGER;
  l_buffer   VARCHAR2(32767);
  l_amount   BINARY_INTEGER := 32767;

  --The cursor to fetch the object to get the DDLs.
  CURSOR cur_objcts IS
    SELECT object_name, object_type, owner
      FROM dba_objects
     WHERE object_type IN ('PROCEDURE', 'FUNCTION')
       AND owner IN ('SCOTT', 'HR'); --I am taking two schemas here. You can remove this restriction.

  --This function usages dbms_metadata to fetch the DDLs.
  FUNCTION get_metadata(pi_obj_type  IN VARCHAR2,
                        pi_obj_name  IN VARCHAR2,
                        pi_obj_owner IN VARCHAR2) RETURN CLOB IS
    h   NUMBER;
    th  NUMBER;
    doc CLOB;
  BEGIN
    h := DBMS_METADATA.open(pi_obj_type);
    DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
    DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
    th  := DBMS_METADATA.add_transform(h, 'MODIFY');
    th  := DBMS_METADATA.add_transform(h, 'DDL');
    doc := DBMS_METADATA.fetch_clob(h);
    DBMS_METADATA.CLOSE(h);
    return doc;
  END get_metadata;
 
------Begin main executable section.
BEGIN
  DBMS_LOB.createtemporary(myddls, TRUE);
  DBMS_LOB.createtemporary(metadata, TRUE);
  DBMS_LOB.open(myddls, DBMS_LOB.lob_readwrite);
  DBMS_LOB.open(metadata, DBMS_LOB.lob_readwrite);
  FOR i IN cur_objcts LOOP
    ddls := get_metadata(i.object_type, i.object_name, i.owner);
    --Get the total length of the CLOB
    l_clob_len := DBMS_LOB.getlength(ddls);
    --Read and Write in chunks.
    WHILE l_pos < l_clob_len LOOP
      DBMS_LOB.READ(ddls, l_amount, l_pos, l_buffer);
      DBMS_LOB.WRITE(metadata, l_amount, l_pos, l_buffer);
      l_pos := l_pos + l_amount;
    END LOOP;
    l_pos := 1;
    --append to another LOB
    DBMS_LOB.append(myddls,metadata);
    v_amount := DBMS_LOB.GETLENGTH(my_var);
    --Write the / character.
    DBMS_LOB.writeappend(myddls, v_amount, my_var);
 
  END LOOP;
  --Write the whle LOB to disk.
  DBMS_XSLPROCESSOR.clob2file(cl => myddls,flocation =>'SAUBHIK' ,fname =>'myddls.sql' );
 --Cleanups.
  DBMS_LOB.close(myddls);
  DBMS_LOB.close(metadata);
  DBMS_LOB.freetemporary(myddls);
  DBMS_LOB.freetemporary(metadata);

END;