Monday, March 30, 2015

Join using Like Condition - Using Oracle Text

This is also based on one of my posting in OTN. Here, I have demonstrated the use of Oracle Text in place of LIKE operator. This may give significant benefit in terms of performance.

The problem.
create table location_source
(code number,
description varchar2(100)
);

 

insert into location_source values('', 'check in london');
insert into location_source values('', 'flight Paris 9.30');
insert into location_source values('', 'baggage allowance 20kg Hong kong');
insert into location_source values('', 'check out 6.30');
insert into location_source values('', 'new york 23rd Jul');
insert into location_source values('', 'flight jy688 sydney');

 

create table location_lookup
(code number
location_lookup varchar2(10)
);

 

insert into location_lookup values (001, 'London');
insert into location_lookup values (002, 'London');
insert into location_lookup values (003, 'Paris');
insert into location_lookup values (004, 'Hong Kong');
insert into location_lookup values (005, 'Paris');
insert into location_lookup values (006, 'New York');

The SQL looks something like this:

select s.*, min(l.code) , l.location_lookup
from location_source s, locaction_lookup l
and UPPER(s.description||'%') like upper(l.location_lookup||'%')
group by s.*, l.location_lookup

 

Output Required:

 
Code	Description	                  Code	Location_Lookup
	check in london	                  001	London
	flight Paris 9.30                 003	Paris
	baggage allowance 20kg Hong kong  004	Hong Kong
	check out 6.30		
	new york 23rd Jul	          006	New York
	flight jy688 sydney

Now, The problem is other methods using LIKE operator is running slow for huge amount of data. Oracle Text is very useful in these cases.

Preparing SCOTT to use Oracle Text:
    SQL>   
    SQL> SHOW USER  
    USER is "SYS"  
    SQL>   
    SQL> GRANT ctxapp TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;  
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  

Now the actual test::
    SQL> SHOW user  
    USER is "SCOTT"  
    SQL> --My database version.  
    SQL> SELECT * FROm v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> SELECT * FROM location_source;  
      
          CODE DESCRIPTION  
    ---------- ----------------------------------------------------------------------------------------------------  
               check in london  
               flight Paris 9.30  
               baggage allowance 20kg Hong kong  
               check out 6.30  
               new york 23rd Jul  
               flight jy688 sydney  
      
    6 rows selected.  
      
    SQL> SELECT * FROM location_lookup;  
      
          CODE LOCATION_L  
    ---------- ----------  
             1 London  
             2 London  
             3 Paris  
             4 Hong Kong  
             5 Paris  
             6 New York  
      
    6 rows selected.  
      
    SQL> CREATE INDEX location_source_idx ON location_source(description) INDEXTYPE IS CTXSYS.CONTEXT;  
      
    Index created.  
      
    SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LOCATION_SOURCE', cascade=>TRUE);  
      
    PL/SQL procedure successfully completed.  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT ls.code,  
      2    ls.description,  
      3    MIN(ll.code) new_code,  
      4    ll.location_lookup  
      5  FROM location_source ls  
      6  LEFT OUTER JOIN location_lookup ll  
      7  ON contains(ls.description,ll.location_lookup)>0  
      8  GROUP BY ls.code,  
      9    ls.description,  
     10*   ll.location_lookup  
    SQL> /  
      
          CODE DESCRIPTION                                                                                            NEW_CODE LOCATION_L  
    ---------- ---------------------------------------------------------------------------------------------------- ---------- ----------  
               check in london                                                                                               1 London  
               baggage allowance 20kg Hong kong                                                                              4 Hong Kong  
               check out 6.30  
               new york 23rd Jul                                                                                             6 New York  
               flight Paris 9.30                                                                                             3 Paris  
               flight jy688 sydney  
      
    6 rows selected.  

Tuesday, March 10, 2015

Listing Operating system disk usages with External Table PREPROCESSOR feature

This is based on one of my posting in OTN.
Database version and directory setups:

    SQL> SHOW user  
    USER is "SYS"  
    SQL>  
    SQL> -- My database version.  
    SQL> SELECT * FROM v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> --Creating directory object.  
    SQL> CREATE OR REPLACE DIRECTORY saubhik AS '/home/oracle/saubhik';  
      
    Directory created.  
    SQL> GRANT READ, WRITE, EXECUTE ON DIRECTORY saubhik to scott;  
      
    Grant succeeded.  
      
    SQL>
    
    Now, the OS setups:
    1. [oracle@localhost saubhik]$  
    2. [oracle@localhost saubhik]$ ## My Operating System.  
    3. [oracle@localhost saubhik]$ uname -a  
    4. Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux  
    5. [oracle@localhost saubhik]$ pwd  
    6. /home/oracle/saubhik  
    7. [oracle@localhost saubhik]$ cat free_space.sh  
    8. #This shell script is call by External Table's PREPROCESSOR claus.  
    9. # As per the external table definitin the file named as "frespace.txt" is passed as argument 1 ($1) to this script.  
    10. #!/bin/bash  
    11.   
    12. # Reading the output of the file into a varriable  
    13. a=`/bin/cat $1`  
    14.   
    15. # Listing the space informations  
    16. /bin/df -Pkh $a  
    17. exit  
    18. [oracle@localhost saubhik]$  
    19. [oracle@localhost saubhik]$ cat freespace.txt  
    20. /home  
    21. [oracle@localhost saubhik]$  
    Now the External Table setup:
    1. SQL>  
    2. SQL> SHOW USER  
    3. USER is "SCOTT"  
    4. SQL> --My database version.  
    5. SQL> SELECT * FROM v$version;  
    6.   
    7. BANNER  
    8. --------------------------------------------------------------------------------  
    9. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    10. PL/SQL Release 11.2.0.1.0 - Production  
    11. CORE    11.2.0.1.0      Production  
    12. TNS for Linux: Version 11.2.0.1.0 - Production  
    13. NLSRTL Version 11.2.0.1.0 - Production  
    14.   
    15. SQL> -- Dropping the table before creating. You may not ned this.  
    16. SQL> DROP TABLE freespace_ext PURGE;  
    17.   
    18. Table dropped.  
    19.   
    20. SQL> --Creating the table.  
    21. SQL> ed  
    22. Wrote file afiedt.buf  
    23.   
    24.   1   CREATE TABLE "SCOTT"."FREESPACE_EXT"  
    25.   2     (       "FILESYSTEM" VARCHAR2(50 BYTE),  
    26.   3     "FSIZE" VARCHAR2(20 BYTE),  
    27.   4     "USED" VARCHAR2(20 BYTE),  
    28.   5     "FREE" VARCHAR2(20 BYTE),  
    29.   6     "USE_PER" VARCHAR2(20 BYTE),  
    30.   7     "MOUNTPOINT" VARCHAR2(20 BYTE)  
    31.   8     )  
    32.   9     ORGANIZATION EXTERNAL  
    33. 10      ( TYPE ORACLE_LOADER  
    34. 11        DEFAULT DIRECTORY "SAUBHIK"  
    35. 12        ACCESS PARAMETERS  
    36. 13        ( RECORDS DELIMITED BY NEWLINE  
    37. 14             PREPROCESSOR SAUBHIK: 'free_space.sh'  
    38. 15             skip 2  
    39. 16             badfile SAUBHIK:'freespace_ext%a_%p.bad'  
    40. 17             logfile SAUBHIK:'freespace_ext%a_%p.log'  
    41. 18             fields terminated by whitespace lrtrim  
    42. 19             missing field values are null (filesystem,  
    43. 20                                            fsize ,  
    44. 21                                            used ,  
    45. 22                                            free ,  
    46. 23                                            use_per ,  
    47. 24                                            mountpoint)  
    48. 25                                               )  
    49. 26        LOCATION  
    50. 27         ( "SAUBHIK":'freespace.txt'  
    51. 28         )  
    52. 29      )  
    53. 30     REJECT LIMIT UNLIMITED  
    54. 31*   PARALLEL 2  
    55. SQL> /  
    56.   
    57. Table created.  
    58.   
    59. SQL> -- A sample testing. Our /home/oracle/freespace.txt contains /home. So it should report the usages of that.  
    60. SQL> SELECT * FROM freespace_ext;  
    61.   
    62. FILESYSTEM                                         FSIZE                USED                 FREE                 USE_PER              MOUNTPOINT  
    63. -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------  
    64. Filesystem                                         Size                 Used                 Avail                Use%                 Mounted  
    65. /dev/mapper/VolGroup00-LogVol04                    2.0G                 104M                 1.8G                 6%                   /home  
    66.   
    67. SQL>  
    Now if you want it in a flexible way:
      SQL> SQL> SHOW USER USER is "SCOTT" SQL> --Creating a pipeline table function to get the free space. The mount point passed as an argument. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE freespace_typ AS OBJECT 2 ( filesystem VARCHAR2(50), 3 fsize VARCHAR2(20), 4 used VARCHAR2(20), 5 free VARCHAR2(20), 6 use_per VARCHAR2(20), 7 mountpoint VARCHAR2(20) 8* ) SQL> / Type created. SQL> CREATE OR REPLACE TYPE freespace_tbl_typ AS TABLE OF freespace_typ; 2 / Type created. SQL> ed Wrote file afiedt.buf 1 ---My wrapper function. 2 CREATE OR REPLACE 3 FUNCTION get_free_space 4 ( 5 pi_path VARCHAR2) 6 RETURN freespace_tbl_typ PIPELINED 7 AS 8 v_file_handle utl_file.file_type; 9 v_dir_name VARCHAR2(50):='SAUBHIK'; 10 v_max_linesize INTEGER :=32767; 11 v_file_name VARCHAR2(50):='freespace.txt'; 12 v_write_buffer VARCHAR2(4000); 13 BEGIN 14 v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize); 15 v_write_buffer:=pi_path; 16 utl_file.put_line(v_file_handle,v_write_buffer,TRUE); 17 utl_file.fclose(v_file_handle); 18 FOR i IN 19 (SELECT filesystem, 20 fsize , 21 used , 22 free , 23 use_per , 24 mountpoint 25 FROM freespace_ext 26 ) 27 LOOP 28 PIPE ROW (freespace_typ(i.filesystem,i.fsize,i.used,i.free,i.use_per,i.mountpoint)); 29 END LOOP; 30 RETURN; 31* END get_free_space; SQL> / Function created. SQL> SELECT * FROM TABLE(get_free_space('/')); FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('/u01')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 SQL> ed Wrote file afiedt.buf 1* SELECT * FROM TABLE(get_free_space('')) SQL> / FILESYSTEM FSIZE USED FREE USE_PER MOUNTPOINT -------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- Filesystem Size Used Avail Use% Mounted /dev/mapper/VolGroup00-LogVol00 8.8G 3.2G 5.2G 38% / /dev/mapper/VolGroup00-LogVol02 20G 5.7G 13G 31% /u01 /dev/mapper/VolGroup00-LogVol04 2.0G 104M 1.8G 6% /home /dev/mapper/VolGroup00-LogVol03 5.7G 851M 4.6G 16% /tmp /dev/sda1 99M 13M 82M 14% /boot tmpfs 1.5G 719M 782M 48% /dev/shm 7 rows selected. SQL>

    Wednesday, October 1, 2014

    Listing Operating system files with External Table PREPROCESSOR feature

    I have read few posts/articles about listing files using external table PREPROCESSOR feature. Most of them is based on Win* systems and with some limitations.
    I have taken help from the following articles:
    http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html
    http://www.oracle.com/technetwork/articles/servers-storage-admin/linux-from-database-2008379.html
    There are some spelling mistakes in this article and please accept my apology for that. I will try to correct those in my off time.
     
     SQL> SHOW USER
    USER is "SYS"
    SQL> ---- My database version
    SQL> SELECT * FROM v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> -- Creating the directory object.
    SQL> CREATE OR REPLACE DIRECTORY SAUBHIK AS '/home/oracle/saubhik';
    
    Directory created.
    
    SQL> --- Granting the required permissions.
    SQL> GRANT read, write, execute on DIRECTORY saubhik TO scott;
    
    Grant succeeded.
    
    SQL> 
    
    [oracle@localhost saubhik]$ ## My Operating system details
    [oracle@localhost saubhik]$ uname -a
    Linux localhost.localdomain 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
    [oracle@localhost saubhik]$ # My list_file.sh which is used in External Table Definition
    [oracle@localhost saubhik]$ cat list_file.sh
    # This shell script is call by External Table's PREPROCESSOR claus.
    # As per the external table definitin the file named as "listfile.txt" is passed as argument 1 ($1) to this script.
    
    #!/bin/bash
    
    # Reading the output of the file into a varriable
    a=`/bin/cat $1`
    
    # Changing the directory.
    cd $a
    
    # Listing the files
    /bin/ls -lh|/bin/awk '{print $1,",",$2,",",$3,",",$4,",",$5,",",$6,$7,",",$8,","$9}'
    exit
    [oracle@localhost saubhik]$ #Current content of the listfile.txt
    [oracle@localhost saubhik]$ cat listfile.txt
    /u01/app/oracle/product/11.2.0/db_1
    [oracle@localhost saubhik]$ 
    
    SQL> SHOW USER
    USER is "SCOTT"
    SQL> SELECT * FROM v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> --Dropping the table before creation!
    SQL> DROP TABLE LISTFILE_EXT purge;
    
    Table dropped.
    
    SQL> -- Creating the external table. 
    SQL> ed
    Wrote file afiedt.buf
    
      1   CREATE TABLE LISTFILE_EXT
      2     (       fpermission VARCHAR2(500),
      3        flink VARCHAR2(2),
      4        fowner VARCHAR2(500),
      5        fgroup VARCHAR2(500),
      6        fsize VARCHAR2(500),
      7        fdate VARCHAR2(20),
      8        ftime VARCHAR2(20),
      9        FNAME VARCHAR2(500 BYTE)
     10     )
     11     ORGANIZATION EXTERNAL
     12      ( TYPE ORACLE_LOADER
     13        DEFAULT DIRECTORY SAUBHIK
     14        ACCESS PARAMETERS
     15        ( RECORDS DELIMITED BY NEWLINE
     16          PREPROCESSOR SAUBHIK: 'list_file.sh'
     17          skip 2
     18          badfile SAUBHIK:'listfile_ext%a_%p.bad'
     19          logfile SAUBHIK:'listfile_ext%a_%p.log'
     20          fields terminated by ',' lrtrim
     21          missing field values are null (fpermission,
     22                                         flink ,
     23                                         fowner ,
     24                                         fgroup ,
     25                                         fsize ,
     26                                         fdate,
     27                                         ftime ,
     28                                         FNAME )
     29                                        )
     30        LOCATION
     31         ( SAUBHIK:'listfile.txt'
     32         )
     33      )
     34     REJECT LIMIT UNLIMITED
     35*   PARALLEL 2
    SQL> /
    
    Table created.
    
    SQL>
    
    


    Now, I expect that, If I do a select from my external table, then it will in turn use list_file.sh with argument listfile.txt. So, We should have the directory listing of the path mentioned in the file listfile.txt which is /u01/app/oracle/product/11.2.0/db_1, my Oracle home directory!.


















    So, it is working as expected!. Now I want to make it little bit flexible. That means when user will pass some directory path, it should display the files in that directory.
    For that, we need to modify the listfile.txt as per user need and also Oracle user should have read access to that path (otherwise you will get an error!).
    Here is my wrapper function.

    SQL> --Creating the type.
    SQL> ed
    Wrote file afiedt.buf
    
      1    CREATE OR REPLACE TYPE LISTFILE_TYP AS OBJECT
      2     (       fpermission VARCHAR2(500),
      3        flink VARCHAR2(2),
      4        fowner VARCHAR2(500),
      5        fgroup VARCHAR2(500),
      6        fsize VARCHAR2(500),
      7        fdate VARCHAR2(20),
      8        ftime VARCHAR2(20),
      9        FNAME VARCHAR2(500)
     10*    )
    SQL> /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE LISTFILE_TBL_TYP AS TABLE OF  LISTFILE_TYP;  
      2  /
    
    Type created.
    
    SQL>
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  ---My wrapper function.
      2  CREATE OR REPLACE
      3  FUNCTION get_file_list
      4    (
      5      pi_path VARCHAR2)
      6    RETURN LISTFILE_TBL_TYP PIPELINED
      7  AS
      8    v_file_handle utl_file.file_type;
      9    v_dir_name     VARCHAR2(50):='SAUBHIK';
     10    v_max_linesize INTEGER     :=32767;
     11    v_file_name    VARCHAR2(50):='listfile.txt';
     12    v_write_buffer VARCHAR2(4000);
     13  BEGIN
     14    v_file_handle :=utl_file.fopen(v_dir_name,v_file_name,'w',v_max_linesize);
     15    v_write_buffer:=pi_path;
     16    utl_file.put_line(v_file_handle,v_write_buffer,TRUE);
     17    utl_file.fclose(v_file_handle);
     18    FOR i IN
     19    (SELECT fpermission,
     20      flink ,
     21      fowner ,
     22      fgroup ,
     23      fsize ,
     24      fdate,
     25      ftime ,
     26      FNAME
     27    FROM listfile_ext
     28    )
     29    LOOP
     30      PIPE ROW (LISTFILE_TYP(i.fpermission,i.flink,i.fowner,i.fgroup,i.fsize,i.fdate,i.ftime,i.fname));
     31    END LOOP;
     32* END;
    SQL> /
    
    Function created.
    
    SQL>
    
    
    Now, it's time to post some test cases!.


    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 pdf 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. 
    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 data-blogger-escaped-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 visible!
    
    
    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.