Saubhik's Blog On Oracle SQL, PL/SQL, DBA, PostgreSQL, AWS, DynamoDB and Python
Wednesday, February 23, 2011
Sending Multiple attachment (BLOB) using UTL_SMTP.
This summary is not available. Please
click here to view the post.
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> 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>
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.
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.
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>
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.
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
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;
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;
Subscribe to:
Posts
(
Atom
)