Thursday, May 24, 2012

Manupulating pdf Files Using Oracle Text.

This is also based on one of my post in OTN. In this post, I will demonstrate various thing you can do with pdf files with the help of Oracle Text.
Also you can see these other related posts in OTN : OTN1 OTN2 and MYBLOG
Oracle Text can manipulate pdf files to a greate extent. For more information about Oracle Text Please visit
Online Documentation
http://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm
Oracle Text Forum
https://forums.oracle.com/forums/forum.jspa?forumID=71
In the forum, I have gained valuable insights by reading the posts from Barbara Boehmer . You may also try those.
Here is some example of pdf file manipulation using Oracle Text:

Here is my odf files and OS related information.

[oracle@localhost ~]$ cd Saubhik/ 
[oracle@localhost Saubhik]$ ls -l *.pdf
-rw------- 1 oracle oracle 1358211 Sep 18  2011 Graphical_Plans_NoCOUG_Article.pdf
-rw------- 1 oracle oracle  374150 May 22 20:21 mypdf2.pdf
[oracle@localhost Saubhik]$ uname -a
Linux localhost.localdomain 2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@localhost Saubhik]$ pwd
/home/oracle/Saubhik
[oracle@localhost Saubhik]$

My database information.
[oracle@localhost ~]$ sqlplus scott/tiger
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 24 13:50:46 2012
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SELECT * FROM v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
 
SQL> SELECT directory_name,directory_path
  2  FROM dba_directories
  3  WHERE directory_name='TEMP';
 
DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
TEMP
/home/oracle/Saubhik
 
 
SQL>


Now some demos. This can be make better and efficient by reading the above mentioned links.
SQL> /* Creating my demo table */
SQL> CREATE TABLE pdm(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),pdf_file BLOB);
 
Table created.
 
SQL> /* Creating a procedure which will load the BLOBs (pdf files) into the table */
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE
  2  PROCEDURE load_file(
  3      pi_id  IN INTEGER,
  4      pfname IN VARCHAR2)
  5  IS
  6    src_file BFILE;
  7    dst_file BLOB;
  8    lgh_file BINARY_INTEGER;
  9  BEGIN
 10    src_file := bfilename('TEMP', pfname);
 11    INSERT
 12    INTO pdm
 13      (
 14        id_pk,
 15        fname,
 16        pdf_file
 17      )
 18      VALUES
 19      (
 20        pi_id,
 21        pfname,
 22        EMPTY_BLOB()
 23      )
 24    RETURNING pdf_file
 25    INTO dst_file;
 26    DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
 27    lgh_file := dbms_lob.getlength(src_file);
 28    DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
 29    dbms_lob.close(src_file);
 30    COMMIT;
 31* END load_file;
SQL> / 
 
Procedure created.
 
SQL> EXECUTE load_file(1,'mypdf2.pdf');
 
PL/SQL procedure successfully completed.
 
SQL> EXECUTE load_file(2,'Graphical_Plans_NoCOUG_Article.pdf');
 
PL/SQL procedure successfully completed.
 
SQL> /* Checking the rows just inserted in the table */
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(pdf_file) file_size
  2  FROM pdm;
 
     ID_PK FNAME                                               FILE_SIZE
---------- -------------------------------------------------- ----------
         1 mypdf2.pdf                                             374150
         2 Graphical_Plans_NoCOUG_Article.pdf                    1358211
 
SQL> /* Creating the index */
SQL> CREATE INDEX pdm_pdf_idx ON pdm(pdf_file) INDEXTYPE IS CTXSYS.CONTEXT;
 
Index created.
 
SQL> /* Checking for any errors in index creation */
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> /* Now, you can search words and find in which pdf file that exists!  
SQL> Here are some examples */
SQL> SELECT ID_PK,FNAME
FROM PDM
WHERE CONTAINS(PDF_FILE,'Chris Lawson')>0;  2    3  
 
     ID_PK FNAME
---------- --------------------------------------------------
         2 Graphical_Plans_NoCOUG_Article.pdf
 
SQL> ed  
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'SAUBHIK')>0
SQL> / 
 
     ID_PK FNAME
---------- --------------------------------------------------
         1 mypdf2.pdf
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'MEGHDEEP')>0
SQL> / 
 
     ID_PK FNAME
---------- --------------------------------------------------
         1 mypdf2.pdf
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'DBMS_XPLAN')>0
SQL> / 
 
no rows selected
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'www.graphviz.org')>0
SQL> / 
 
     ID_PK FNAME
---------- --------------------------------------------------
         2 Graphical_Plans_NoCOUG_Article.pdf
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'Bla Bla')>0
SQL> / 
 
no rows selected
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'DBMS_XPLAN-')>0
SQL> / 
SELECT ID_PK,FNAME
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 12
 
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT ID_PK,FNAME
  2  FROM PDM
  3* WHERE CONTAINS(PDF_FILE,'DBMS')>0
SQL> / 
 
     ID_PK FNAME
---------- --------------------------------------------------
         2 Graphical_Plans_NoCOUG_Article.pdf
 
SQL> /* Now say, You want to get some portions of the words from your pdf document */
SQL> /* For this purpose, I have created a function. I know, this function can be 
SQL>    very well written. Also you may try to use some other features like 
SQL>    CTX_DOC.SNIPPET and Regular expressions.  
SQL> */
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE
  2    FUNCTION search_pdf(
  3        pi_index_name  VARCHAR2,
  4        pi_search_word VARCHAR2,
  5        pi_pk_val      VARCHAR2 )
  6      RETURN VARCHAR2
  7    IS
  8      v_clob CLOB;
  9      v_amt_cut INTEGER :=250;
 10      v_buffer CLOB;
 11    BEGIN
 12      CTX_DOC.markup(pi_index_name,pi_pk_val,pi_search_word, v_clob);
 13      v_buffer:=DBMS_LOB.substr(v_clob,v_amt_cut,dbms_lob.instr(v_clob,pi_search_word,1,1));
 14      DBMS_LOB.FREETEMPORARY(v_clob);
 15      RETURN REGEXP_REPLACE(v_buffer,'<[^>]+>');
 16*   END search_pdf;
SQL> / 
 
Function created.
 
SQL> SELECT search_pdf('PDM_PDF_IDX','SAUBHIK','1') FROM dual;
 
SEARCH_PDF('PDM_PDF_IDX','SAUBHIK','1')
--------------------------------------------------------------------------------
SAUBHIK>>>  BANERJEE
***: M
Age: 36
<
 
 
SQL> ed
Wrote file afiedt.buf
 
  1* SELECT search_pdf('PDM_PDF_IDX','MEGHDEEP','1') FROM dual
SQL> / 
 
SEARCH_PDF('PDM_PDF_IDX','MEGHDEEP','1')
--------------------------------------------------------------------------------
MEGHDEEP>>> BANERJEE
***: M
Age: 4
<d
 
 
SQL> ed
Wrote file afiedt.buf
 
  1* SELECT search_pdf('PDM_PDF_IDX','DBMS','2') FROM dual
SQL> / 
 
SEARCH_PDF('PDM_PDF_IDX','DBMS','2')
--------------------------------------------------------------------------------
DBMS>>>_XPLAN?not very
easy to read, especially when many tables are involved.
<div class="c" style="top:1391px;left:85px;font-size:13px;font
 
 
SQL> 
SQL> ed
Wrote file afiedt.buf
 
  1* SELECT search_pdf('PDM_PDF_IDX','Graphviz','2') FROM dual
SQL> / 
 
SEARCH_PDF('PDM_PDF_IDX','GRAPHVIZ','2')
--------------------------------------------------------------------------------
Graphviz>>> that
can be downloaded from
<div class="c" style="top:1615px;left:196px;font-size:13px;color:#0058C5;font-fa
mily:Minion, serif;"
 
 Today I found another interesting post :
https://forums.oracle.com/forums/thread.jspa?threadID=2394143&#10359156

Monday, September 5, 2011

Some Datapump Scripts.

Thesee are some scripts I posted in OTN various times. I am trying to collate them together in this thread.

Script I: Exporting particular schema(s).

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'SCHEMA_EXPR',
                                value  => 'IN (''HR'',''SAUBHIK'')');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;
---------------------------------------------------------------------
Script II: Exporting particular tables with LIKE clause.

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'TABLE',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'NAME_EXPR',
                                value  => 'LIKE ''EMP%''');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;
-------------------------------------------------------------------
SELECT * FROM user_datapump_jobs;

Script III: Exporting particular table.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    l_dp_handle    NUMBER;
  3    l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  4    l_job_state    VARCHAR2(30) := 'UNDEFINED';
  5    l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  6    l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
  7    l_tbl_name    VARCHAR2(30) :='EMP';
  8  BEGIN
  9    l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
 10                     job_mode    => 'TABLE',
 11                     remote_link => NULL,
 12                     job_name    => 'SAUBHIK_EXPORT',
 13                     version     => 'COMPATIBLE');
 14    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 15                  filename    => l_expfilename,
 16                  directory => 'SAUBHIK',
 17                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 18    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 19                  filename    => l_logfilename,
 20                  directory => 'SAUBHIK',
 21                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 22    dbms_datapump.metadata_filter(handle => l_dp_handle,
 23                     name   => 'NAME_EXPR',
 24                     value  =>'= '||''''||l_tbl_name||'''');
 25    dbms_datapump.start_job(l_dp_handle);
 26    dbms_datapump.detach(l_dp_handle);
 27  EXCEPTION
 28    WHEN OTHERS THEN
 29     dbms_datapump.stop_job(l_dp_handle);
 30     RAISE;
 31* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM user_datapump_jobs;

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 EXECUTING                     1       0         2

SQL> /

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 NOT RUNNING                 0       0         0

SQL> /

no rows selected

SQL>

Friday, September 2, 2011

Partitioning an existing non partitioned table with data, index and constraints - PART I


Method I: Using dbms_redefinition.

Step 1: Create my table:

SQL>
SQL> drop table not_paritioned_tbl purge;

Table dropped.

SQL> CREATE TABLE not_partitioned_tbl AS
  2  SELECT object_id,object_name,created,owner FROM all_objects;

Table created.

SQL> ALTER TABLE not_partitioned_tbl ADD CONSTRAINT not_partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX npt_created ON not_partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','NOT_PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM not_partitioned_tbl;

  COUNT(*)
----------
     42540

SQL>


Step 2: Create a partitioned table.
SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE partitioned_tbl (object_id NUMBER, object_name VARCHAR2(30), created DATE, owner VARCHAR2(30))
  2  PARTITION BY RANGE (created)
  3  (PARTITION pt_2005 VALUES LESS THAN (to_date('01/01/2006','dd/mm/rrrr')),
  4   PARTITION pt_2006 VALUES LESS THAN (to_date('01/01/2007','dd/mm/rrrr')),
  5   PARTITION pt_2007 VALUES LESS THAN (to_date('01/01/2008','dd/mm/rrrr')),
  6*  PARTITION pt_rest VALUES LESS THAN (MAXVALUE))
SQL> /

Table created.


Step 3: Verify whether we can redefine the existing table.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('SCOTT','NOT_PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>


Step 4: Status of original and interim table

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
PARTITIONED_TBL            YES
NOT_PARTITIONED_TBL           NO

SQL>


Step 4: Connect as sys (I don't want to give grants to scott) and start redefinition.

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 2 15:37:17 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> BEGIN
  2   DBMS_REDEFINITION.start_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>


Step 5: Create index, constraints.

SQL> ALTER TABLE partitioned_tbl ADD CONSTRAINT partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX pt_created ON partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL>


Step 6: Finish the redef (as sys)

SQL> EXECUTE dbms_redefinition.finish_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>


Step 7: Now non_partitioned_tbl should become partitioned.

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
NOT_PARTITIONED_TBL           YES
PARTITIONED_TBL            NO

SQL>

SQL> DROP TABLE partitioned_tbl purge;

Table dropped.

SQL>

Step 8: You can rename the index and constraints to match the table name.

SQL> SELECT index_name,table_name FROM user_indexes WHERE table_name='NOT_PARTITIONED_TBL';

INDEX_NAME               TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL
PT_CREATED               NOT_PARTITIONED_TBL

SQL> SELECT constraint_name,table_name FROM user_constraints WHERE table_name='NOT_PARTITIONED_TBL';

CONSTRAINT_NAME            TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL

SQL>

Further Reading:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php


Wednesday, July 27, 2011

How To Set a Virtual Private Database (VPD). - Part II

This is a continuation of my previous post about VPD.
The requirement: I have a table like
test_vpd (id_col INTEGER, name_col VARCHAR2(30),contact_no NUMBER, 
email VARCHAR2(50), city VARCHAR2(50))
 
The user JAMES can not view any email address and also, He can not modify any rows where id_col is less than 20.
 
 oracle@ubuntu-desktop:~$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 27 16:13:34 2011
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> /* Granting scott, the owner of the table a create contetx privilege */
SQL> GRANT CREATE ANY CONTEXT TO scott;
 
Grant succeeded.
 
Now, It's time to create the table and some sample data with SCOTT user.

SQL> /* Creating a conetxt */
SQL> CREATE OR REPLACE CONTEXT saubhik_test_context USING test_vpd_pkg;

Context created.
 
SQL> CREATE TABLE test_vpd (id_col INTEGER, name_col VARCHAR2(30),contact_no NUMBER, email VARCHAR2(50), city VARCHAR2(50));
 
Table created.
SQL> ed
Wrote file afiedt.buf
 
  1  INSERT INTO test_vpd SELECT rownum,initcap(substr(object_name,1,30)),object_id,owner||'@mail.com',object_type
  2  FROM all_objects
  3* WHERE rownum<50
SQL> / 
 
49 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> set line 180
SQL> SELECT * FROM test_vpd;
 
    ID_COL NAME_COL     CONTACT_NO EMAIL      CITY
---------- ------------------------------ ---------- -------------------------------------------------- --------------------------------------------------
  1 Dual      258 SYS@mail.com     TABLE
  2 Dual      259 PUBLIC@mail.com     SYNONYM
  3 System_Privilege_Map    311 SYS@mail.com     TABLE
  4 System_Privilege_Map    313 PUBLIC@mail.com     SYNONYM
  5 Table_Privilege_Map    314 SYS@mail.com     TABLE
  6 Table_Privilege_Map    316 PUBLIC@mail.com     SYNONYM
  7 Stmt_Audit_Option_Map   317 SYS@mail.com     TABLE
  8 Stmt_Audit_Option_Map   319 PUBLIC@mail.com     SYNONYM
  9 Ol$      452 OUTLN@mail.com     TABLE
 10 Ol$Hints     453 OUTLN@mail.com     TABLE
 11 Ol$Nodes     456 OUTLN@mail.com     TABLE
 
    ID_COL NAME_COL     CONTACT_NO EMAIL      CITY
---------- ------------------------------ ---------- -------------------------------------------------- --------------------------------------------------
 12 Map_Object     604 SYS@mail.com     TABLE
 13 Map_Object     605 PUBLIC@mail.com     SYNONYM
 14 Re$Nv_List     886 SYS@mail.com     TYPE
 15 Standard     887 SYS@mail.com     PACKAGE
 16 Dbms_Standard    889 SYS@mail.com     PACKAGE
 17 Dbms_Standard    890 PUBLIC@mail.com     SYNONYM
 18 V_$Map_Library    900 SYS@mail.com     VIEW
 19 V$Map_Library    901 PUBLIC@mail.com     SYNONYM
 20 V_$Map_File     902 SYS@mail.com     VIEW
 21 V$Map_File     903 PUBLIC@mail.com     SYNONYM
 22 V_$Map_File_Extent    904 SYS@mail.com     VIEW
.............................................................................................................................................................................

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

Now, It's time to write a package to setup the VPD from scott
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PACKAGE test_vpd_pkg
  3  AS
  4    --This procedure sets the application context.
  5    --------------------------------------------
  6  PROCEDURE set_app_context;
  7    --This function builds the predicate (the where caluse).
  8    --------------------------------------------------------
  9    --This where caluse filters the rows and determines which can be accessed.
 10    -----------------------------------------------------------------------
 11    --Two parameters pi_schema and pi_name is NOT used anywhere, still those are mandetory!
 12    FUNCTION the_predicate1(
 13    pi_schema IN VARCHAR2,
 14    pi_name   IN VARCHAR2)
 15  RETURN VARCHAR2;
 16  FUNCTION the_predicate2(
 17    pi_schema IN VARCHAR2,
 18    pi_name   IN VARCHAR2)
 19  RETURN VARCHAR2;
 20* END test_vpd_pkg;
 21  /

Package created.
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE
  2  PACKAGE BODY test_vpd_pkg
  3  AS
  4    c_context_name VARCHAR2(30):= 'saubhik_test_context';
  5    c_attribute_name VARCHAR2(30):= 'ID_COL';
  6  PROCEDURE set_app_context
  7  IS
  8    v_user_name VARCHAR2(30);
  9  BEGIN
 10    SELECT USER INTO v_user_name FROM dual;
 11    DBMS_SESSION.SET_CONTEXT (namespace=>c_context_name, attribute=>c_attribute_name,value=>v_user_name);
 12  END set_app_context;
 13  FUNCTION the_predicate1(
 14  pi_schema IN VARCHAR2,
 15  pi_name   IN VARCHAR2)
 16    RETURN VARCHAR2
 17  IS
 18    v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
 19    v_restriction   VARCHAR2(2000);
 20  BEGIN
 21    IF v_context_value = 'JAMES' THEN
 22  v_restriction:= ' ID_COL > 20';
 23    ELSE
 24  v_restriction:= NULL;
 25    END IF;
 26    RETURN v_restriction;
 27  END the_predicate1;
 28  FUNCTION the_predicate2(
 29  pi_schema IN VARCHAR2,
 30  pi_name   IN VARCHAR2)
 31    RETURN VARCHAR2
 32  IS
 33    v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
 34    v_restriction   VARCHAR2(2000);
 35  BEGIN
 36    IF v_context_value = 'JAMES' THEN
 37  v_restriction:= ' 1=2';
 38    ELSE
 39  v_restriction:= NULL;
 40    END IF;
 41    RETURN v_restriction;
 42  END the_predicate2;
 43* END test_vpd_pkg;
 44  /

Package body created.

SQL> 

Now, add the policy, and a logon trigger for JAMES from sys.
SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'SCOTT'
                      ,OBJECT_NAME => 'TEST_VPD'
                      ,POLICY_NAME => 'TEST_VPD_POLICY1'
                      ,FUNCTION_SCHEMA => 'SCOTT'
                      ,POLICY_FUNCTION => 'TEST_VPD_PKG.THE_PREDICATE1'
                      ,STATEMENT_TYPES =>'UPDATE, DELETE' --You can add select also if required.
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;   2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'SCOTT'
                      ,OBJECT_NAME => 'TEST_VPD'
                      ,POLICY_NAME => 'TEST_VPD_POLICY2'
                      ,FUNCTION_SCHEMA => 'SCOTT'
                      ,POLICY_FUNCTION => 'TEST_VPD_PKG.THE_PREDICATE2'
                      ,SEC_RELEVANT_COLS=>'EMAIL'
                      ,SEC_RELEVANT_COLS_OPT=>dbms_rls.ALL_ROWS
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;   2    3    4    5    6    7    8    9   10   11   12  
 13  /

PL/SQL procedure successfully completed.

SQL> 
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE TRIGGER test_vpd_trig
  2   AFTER LOGON ON DATABASE
  3   BEGIN
  4    SCOTT.test_vpd_pkg.set_app_context;
  5*  END;
  6  / 
 
Trigger created.
 
SQL> CREATE USER JAMES IDENTIFIED BY james;
 
User created.
 
SQL> GRANT connect, resource to james;
 
Grant succeeded.
 
SQL> GRANT select,update,delete on scott.test_vpd to james;
 
Grant succeeded.
 
SQL>
All set!. Time to test the VPD. 
oracle@ubuntu-desktop:~$ sqlplus james/james

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 27 18:41:21 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> SET line 200
SQL> SELECT * FROM scott.test_vpd;

    ID_COL NAME_COL     CONTACT_NO E CITY
---------- ------------------------------ ---------- - --------------------------------------------------
  1 Dual      258   TABLE
  2 Dual      259   SYNONYM
  3 System_Privilege_Map    311   TABLE
  4 System_Privilege_Map    313   SYNONYM
  5 Table_Privilege_Map    314   TABLE
  6 Table_Privilege_Map    316   SYNONYM
  7 Stmt_Audit_Option_Map   317   TABLE
  8 Stmt_Audit_Option_Map   319   SYNONYM
  9 Ol$      452   TABLE
 10 Ol$Hints     453   TABLE
 11 Ol$Nodes     456   TABLE
So, No email is vissible!
SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=1
SQL> /

0 rows updated.

SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=21
SQL> /

1 row updated.

SQL> ed
Wrote file afiedt.buf

  1  UPDATE scott.test_vpd
  2  SET name_col='Saubhik'
  3* WHERE id_col=20
SQL> /

0 rows updated.

SQL> 
Anything less than 20 in id_col is can not be updated.

Monday, July 4, 2011

How To Set a Virtual Private Database (VPD).

This demonstration is based on Oracle 10G Release 2.
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


From documentation:
Virtual private database (VPD) enables you to enforce security, to a fine level of granularity, directly on tables, views, or synonyms. Because security policies are attached directly to tables, views, or synonyms and automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. The modification creates a WHERE condition (known as a predicate) returned by a function implementing the security policy. The statement is modified dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

Our VPD Requirement
QL> desc customers
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                   NOT NULL NUMBER(6)
 CUST_FIRST_NAME               NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                NOT NULL VARCHAR2(20)
 CUST_ADDRESS                        CUST_ADDRESS_TYP
 PHONE_NUMBERS                        PHONE_LIST_TYP
 NLS_LANGUAGE                        VARCHAR2(3)
 NLS_TERRITORY                        VARCHAR2(30)
 CREDIT_LIMIT                        NUMBER(9,2)
 CUST_EMAIL                        VARCHAR2(30)
 ACCOUNT_MGR_ID                     NUMBER(6)
 CUST_GEO_LOCATION                    MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                        DATE
 MARITAL_STATUS                     VARCHAR2(20)
 GENDER                         VARCHAR2(1)
 INCOME_LEVEL                        VARCHAR2(20)

SQL> SELECT account_mgr_id,COUNT(customer_id)
  2  FROM customers
  3  GROUP BY account_mgr_id;

ACCOUNT_MGR_ID COUNT(CUSTOMER_ID)
-------------- ------------------
       147               76
       149               74
       148               58
       145              111

So, there are four account manager under which, all the customers are assigned. Our requirement is to set a VPD, So that each account manager can
only see,modify his customers.

We assume each account manager will loginto the database using AM||account_mgr_id. That is, AM147, AM148, AM149 and AM145.


Steps for setting up a VPD.
******************************

Step 1: Set up a driving context. Connect database as OE.
--------

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

SQL> CREATE OR REPLACE CONTEXT saubhik_cust_context USING cust_vpd_pkg;

Context created.

SQL>

 


Step 2:  Creating the package.
-------- 

SQL> SHOW USER
USER is "OE"
SQL> CREATE OR REPLACE
PACKAGE cust_vpd_pkg
AS
  --This procedure sets the application context.
  --------------------------------------------
PROCEDURE set_app_context;
  --This function builds the predicate (the where caluse).
  --------------------------------------------------------
  --This where caluse filters the rows and determines which can be accessed.
  -----------------------------------------------------------------------
  FUNCTION the_predicate(
      pi_schema IN VARCHAR2,
      pi_name   IN VARCHAR2)
    RETURN VARCHAR2;
  END cust_vpd_pkg;  2    3    4    5    6    7    8    9   10   11   12   13   14   15 
 16  /

Package created.

SQL> CREATE OR REPLACE
PACKAGE BODY cust_vpd_pkg
AS
  c_context_name   VARCHAR2(30):= 'saubhik_cust_context';
  c_attribute_name VARCHAR2(30):= 'ACCOUNT_MGR';
PROCEDURE set_app_context
IS
  v_user_name VARCHAR2(30);
BEGIN
  SELECT USER INTO v_user_name FROM dual;
  DBMS_SESSION.SET_CONTEXT (namespace=>c_context_name, attribute=>c_attribute_name,value=>v_user_name);
END set_app_context;
FUNCTION the_predicate(
    pi_schema IN VARCHAR2,
    pi_name   IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_context_value VARCHAR2(200):=SYS_CONTEXT(c_context_name,c_attribute_name);
  v_restriction   VARCHAR2(2000);
BEGIN
  IF v_context_value LIKE 'AM%' THEN
    v_restriction:= ' ACCOUNT_MGR_ID = SUBSTR('''||v_context_value||''',3)';
  ELSE
    v_restriction:= NULL;
  END IF;
  RETURN v_restriction;
END THE_PREDICATE;
END cust_vpd_pkg;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28 
 29  /

Package body created.

SQL>





Step 3:
Defining the policy using DBMS_RLS.
---------

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 17:17:27 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> BEGIN
 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'OE'
                      ,OBJECT_NAME => 'CUSTOMERS'
                      ,POLICY_NAME => 'CUSTOMER_CREDIT_POLICY'
                      ,FUNCTION_SCHEMA => 'OE'
                      ,POLICY_FUNCTION => 'CUST_VPD_PKG.THE_PREDICATE'
                      ,STATEMENT_TYPES =>'SELECT, UPDATE, DELETE'
                      ,UPDATE_CHECK => FALSE
                      ,ENABLE => TRUE
                     );
END;  2    3    4    5    6    7    8    9   10   11 
 12  /

PL/SQL procedure successfully completed.

SQL>



Step 4: Setting a ON LOGON trigger (from SYSDBA) to set the context for each loged in user.

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 17:20:24 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> CREATE OR REPLACE TRIGGER cust_vpd_trig
 AFTER LOGON ON DATABASE
 BEGIN
  OE.cust_vpd_pkg.set_app_context;
 END;  2    3    4    5 
  6  /

Trigger created.

SQL
>

Testing Of my VPD

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 18:20:48 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> CREATE USER am145 identified by am145;

User created.

SQL> CREATE USER am147 identified by am147;

User created.

SQL> GRANT connect,resource to am145,am147;

Grant succeeded.

SQL> GRANT SELECT ON oe.customers to am145,am147;

Grant succeeded.

SQL>


Now, connecting AS new user AM145.

oracle@ubuntu-desktop:~$ sqlplus am145/am145

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 4 18:23:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


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

SQL> SELECT customer_id,account_mgr_id FROM oe.customers;

CUSTOMER_ID ACCOUNT_MGR_ID
----------- --------------
    101           145
    102           145
    103           145
    104           145
    105           145
    106           145
    107           145
    108           145
    109           145
    110           145
    111           145
...........................................
..........................................

So, He can only view his customers only.

Now, customer_id=782 belongs to account manager 149. We will try to update it from AM145.

SQL> l
  1  UPDATE oe.CUSTOMERS SET cust_first_name='abc'
  2* WHERE customer_id=782
SQL> /

0 rows updated.

SQL>

Thursday, June 9, 2011

Using JAVA in PL/SQL - PART - II Getting Operating System Information

This is PART II of JAVA with PL/SQL series. We will obtain operating system's information using JAVA in PL/SQL.

SQL> /* My database Version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> /* Creating JAVA class */
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED GetOperatingSystem AS
import java.util.*;
import java.lang.*;
import java.net.*;

public class GetOperatingSystem {
    public static void GetOsInfo() {
        try {
            String osNameString=System.getProperty("os.name");
            String osVersionString=System.getProperty("os.version");
            String osArchString=System.getProperty("os.arch");
            String osUserString=System.getProperty("user.name");
           
            System.out.println("Operating System: " +osNameString);
            System.out.println("Operating System Version: " +osVersionString);
            System.out.println("Operating System Architecture: " +osArchString);
            System.out.println("Operating System User Name: " +osUserString);
           
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("Exception:"+ e.getMessage());
        }

    }
}
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26 
 27  /

Java created.

SQL> /* Creating a wrapper procedure */
SQL> CREATE OR REPLACE PROCEDURE Get_OS
AS LANGUAGE JAVA
NAME 'GetOperatingSystem.GetOsInfo()';  2    3 
  4  /

Procedure created.

SQL> set serverout on
SQL> BEGIN                                                                         
  DBMS_JAVA.SET_OUTPUT(1000000);                                              
  Get_OS;     
END;  2    3    4 
  5  /
Operating System: Linux
Operating System Version: 2.6.31-23-generic
Operating System Architecture: i686
Operating System User Name:

PL/SQL procedure successfully completed.

SQL>

Using JAVA in PL/SQL - PART - I Listing Files with timestamp

This is slightly modified version of DirList class in asktom. There are three steps involved:
  1. Creating the JAVA class in the database.
  2. Creating a wrapper stored procedure/function to call this JAVA class.
  3. Running the wrapper procedure/function.
SQL> /* My database version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> /* Creating JAVA stored procedure */
SQL>   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ListDir AS
   import java.io.*;    
   import java.text.SimpleDateFormat;
   import java.util.*;
                                      
    public class ListDir                                    
   {                                                       
   public static void getList(String directory)            
                                       
   {                                                      
   File path = new File( directory );                     
   String[] list = path.list();                           
   String element;    
  
   for(int i = 0; i < list.length; i++)                   
   {                                                      
   element = list[i];
   String lasmod = new SimpleDateFormat("yyyy-MM-dd").format(new Date(path.lastModified()));
   System.out.println("File Name: "+element+" File Time Stamp: "+lasmod);                                   
     } 
    }
   }  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22 
 23  /

Java created.

SQL> /* Creating a wrapper procedure to call that JAVA class */
SQL> CREATE OR REPLACE
PROCEDURE Get_Filelist(path VARCHAR2)
AS LANGUAGE JAVA
NAME 'ListDir.getList(java.lang.String)';  2    3    4 
  5  /

Procedure created.

SQL> /* Calling that wrapper procedure */
SQL> BEGIN                                                                          
  DBMS_JAVA.SET_OUTPUT(1000000);                                               
  Get_Filelist('/home/oracle');                                                
END;   2    3    4 
  5  /

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> /
File Name: .cache File Time Stamp: 2011-06-09
File Name: .gksu.lock File Time Stamp: 2011-06-09
File Name: admin File Time Stamp: 2011-06-09
File Name: Downloads File Time Stamp: 2011-06-09
File Name: .bash_history File Time Stamp: 2011-06-09
File Name: Desktop File Time Stamp: 2011-06-09
File Name: .fontconfig File Time Stamp: 2011-06-09
File Name: .gstreamer-0.10 File Time Stamp: 2011-06-09
File Name: .recently-used.xbel File Time Stamp: 2011-06-09
File Name: workspace File Time Stamp: 2011-06-09
File Name: .icons File Time Stamp: 2011-06-09
File Name: .update-notifier File Time Stamp: 2011-06-09
File Name: .xsession-errors File Time Stamp: 2011-06-09
File Name: .pulse File Time Stamp: 2011-06-09
File Name: .sudo_as_admin_successful File Time Stamp: 2011-06-09
File Name: .gvfs File Time Stamp: 2011-06-09