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>

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

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.

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>

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

Reading and Writing Excel file (.xls) From PL/SQL Using COM - PART III

For details see Part I and Part II.

Here is another version of the package ORDExcelSB. The earlier versions has a drawback. If you modify an existing excel file, then you can't save it in the same name. That caused the application to be hanged.
You always have to save the modified file with a new name ("save as").
In this version, you can save it as the same name.

Package Specification:

CREATE OR REPLACE PACKAGE ORDExcelSB AS

  /* Saubhik: These constants are added */
  BorderEdgeLeft         CONSTANT BINARY_INTEGER := 7;
  BorderEdgeTop          CONSTANT BINARY_INTEGER := 8;
  BorderEdgeBottom       CONSTANT BINARY_INTEGER := 9;
  BorderEdgeRight        CONSTANT BINARY_INTEGER := 10;
  BorderInsideVertical   CONSTANT BINARY_INTEGER := 11;
  BorderInsideHorizontal CONSTANT BINARY_INTEGER := 12;

  /* Declare externally callable subprograms. */

  /* Start: These functions are newly aded by Saubhik Banerjee */
  FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer;

  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer;

  FUNCTION SetCellColor(range varchar2, style INTEGER) return binary_integer;

  FUNCTION SetCellBorder(range        varchar2,
                         BordersIndex BINARY_INTEGER,
                         BorderStyle  BINARY_INTEGER) return binary_integer;

  FUNCTION FormatFontStyle(range varchar2, fontstyle varchar2)
    return binary_integer;

  FUNCTION FormatFontColor(range varchar2, fontcolor INTEGER)
    return binary_integer;

  FUNCTION FormatFontName(range varchar2, fontname varchar2)
    return binary_integer;

  FUNCTION FormatFontSize(range varchar2, fontsize INTEGER)
    return binary_integer;

  FUNCTION SaveExcelFile(filename varchar2, overwrite VARCHAR2)
    return binary_integer;

  /* End: These functions are newly aded by Saubhik Banerjee */

  FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer;

  FUNCTION InsertData(range varchar2, data binary_integer, type varchar2)
    return binary_integer;

  FUNCTION InsertDataReal(range varchar2,
                          data  double precision,
                          type  varchar2) return binary_integer;

  FUNCTION GetDataNum(range varchar2) return binary_integer;

  FUNCTION GetDataStr(range varchar2) return varchar2;

  FUNCTION GetDataReal(range varchar2) return double precision;

  FUNCTION GetDataDate(range varchar2) return date;

  FUNCTION InsertData(range varchar2, data varchar2, type varchar2)
    return binary_integer;

  FUNCTION InsertData(range varchar2, data Date, type varchar2)
    return binary_integer;

  FUNCTION InsertChart(xpos   binary_integer,
                       ypos   binary_integer,
                       width  binary_integer,
                       height binary_integer,
                       range  varchar2,
                       type   varchar2) return binary_integer;

  FUNCTION SaveExcelFile(filename varchar2) return binary_integer;

  FUNCTION ExitExcel return binary_integer;

END ORDExcelSB;

Package Body:

CREATE OR REPLACE PACKAGE BODY ORDExcelSB AS

  DummyToken        binary_integer;
  applicationToken  binary_integer := -1;
  WorkBooksToken    binary_integer := -1;
  WorkBookToken     binary_integer := -1;
  WorkSheetToken    binary_integer := -1;
  WorkSheetToken1   binary_integer := -1;
  RangeToken        binary_integer := -1;
  ChartObjectToken  binary_integer := -1;
  ChartObject1      binary_integer := -1;
  Chart1Token       binary_integer := -1;
  i                 binary_integer;
  retNum            binary_integer;
  retReal           double precision;
  retStr            varchar2(255);
  retDate           DATE;
  error_src         varchar2(255);
  error_description varchar2(255);
  error_helpfile    varchar2(255);
  error_helpID      binary_integer;

  FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := OrdCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Invoking Workbooks...');
 
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    RETURN i;
  END CreateExcelApplication;

  /* Saubhik: This fuction is aded by Saubhik for readig an existing file */
  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Opening Excel file ' || filename || ' ...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
 
    i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Opening WorkBook');
 
    i := ORDCOM.GetProperty(applicationToken,
                            'ActiveWorkbook',
                            0,
                            WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Invoking WorkSheets..');
 
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Invoking WorkSheet');
    ORDCOM.InitArg();
    ORDCOM.SetArg(sheetname, 'BSTR');
 
    i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Opened ');
 
    RETURN i;
  END OpenExcelFile;

  /***************************************************************************
  * Invoke the Excel Automation Server and create a Workbook object as
  * well as a worksheet object
  ***************************************************************************/
  FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := ORDCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    dbms_output.put_line('Invoking Workbooks...');
    /*i:=ORDCOM.Invoke(applicationToken, 'WorkBooks',0, WorkBooksToken);*/
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    dbms_output.put_line('Invoking Add to WorkBooks...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(-4167, 'I4');
    i := ORDCOM.Invoke(WorkBooksToken, 'Add', 1, WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    dbms_output.put_line('Invoking WorkSheets..');
    ORDCOM.InitArg();
    ORDCOM.SetArg('Sheet 1', 'BSTR');
 
    /*  i:=ORDCOM.Invoke(applicationToken, 'WorkSheets', 1, WorkSheetToken);*/
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    return i;
  END CreateExcelWorkSheet;

  /***************************************************************************
  * Invoke the Range method to obtain a range token. Then set the property value
  * at the specified range to the data required
  ***************************************************************************/
  FUNCTION InsertData(range varchar2, data binary_integer, type varchar2)
    RETURN binary_integer IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    IF (i = 0) THEN
      i := ORDCOM.SetProperty(RangeToken, 'ColumnWidth', 15, 'I2');
    END IF;
 
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;

  /* Saubhik: This function is added to set the cell Border */
  FUNCTION SetCellBorder(range        varchar2,
                         BordersIndex BINARY_INTEGER,
                         BorderStyle  BINARY_INTEGER) return binary_integer IS
    --fontToken binary_integer;
    BorderToken BINARY_INTEGER;
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
 
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    ORDCOM.InitArg();
    ORDCOM.SetArg(BordersIndex, 'I2');
    i := ORDCOM.GetProperty(RangeToken, 'Borders', 1, BorderToken);
    i := ORDCOM.SetProperty(BorderToken, 'weight', BorderStyle, 'I2');
 
    i := ORDCOM.DestroyObject(BorderToken);
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END SetCellBorder;

  /* Saubhik: This function is added to set the cell background color */
  FUNCTION SetCellColor(range varchar2, style INTEGER) return binary_integer IS
    --fontToken binary_integer;
    DummyToken     BINARY_INTEGER;
    SelectionToken BINARY_INTEGER;
    InteriorToken  BINARY_INTEGER;
    --applicationToken
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.Invoke(RangeToken, 'SELECT', 0, DummyToken);
    i := ORDCOM.GetProperty(applicationToken,
                            'Selection',
                            0,
                            SelectionToken);
    i := ORDCOM.GetProperty(SelectionToken, 'Interior', 0, InteriorToken);
    i := ORDCOM.SetProperty(InteriorToken, 'ColorIndex', style, 'I2');
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END SetCellColor;

  /* Saubhik: This function is added to change the font style like Bold, Italic etc. */
  FUNCTION FormatFontStyle(range varchar2, fontstyle varchar2)
    return binary_integer IS
    fontToken binary_integer;
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
    IF fontstyle = 'Bold' OR fontstyle = 'Italic' OR
       fontstyle = 'Underline' THEN
      i := ORDCOM.SetProperty(FontToken, fontstyle, True, 'BOOL');
   
    END IF;
    i := ORDCOM.DestroyObject(FontToken);
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END FormatFontStyle;

  /* Saubhik: This function is added to change the font style like Arial, Bookman, Century etc. */
  FUNCTION FormatFontName(range varchar2, fontname varchar2)
    return binary_integer IS
    fontToken binary_integer;
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
    i := ORDCOM.SetProperty(FontToken, 'Name', fontname, 'BSTR');
 
    i := ORDCOM.DestroyObject(FontToken);
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END FormatFontName;

  /* Saubhik: This function is added to change the font style like Arial, Bookman, Century etc. */
  FUNCTION FormatFontSize(range varchar2, fontsize INTEGER)
    return binary_integer IS
    fontToken binary_integer;
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
    i := ORDCOM.SetProperty(FontToken, 'Size', fontsize, 'I2');
 
    i := ORDCOM.DestroyObject(FontToken);
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END FormatFontSize;

  /* Saubhik: This function is added to change the font color like Black, Blue etc. */
  FUNCTION FormatFontColor(range varchar2, fontcolor INTEGER)
    return binary_integer IS
    fontToken binary_integer;
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
 
    i := ORDCOM.SetProperty(FontToken, 'ColorIndex', fontcolor, 'I2');
    i := ORDCOM.DestroyObject(FontToken);
    i := ORDCOM.DestroyObject(RangeToken);
    return i;
  END FormatFontColor;

  /***************************************************************************
  * Invoke the Range method to obtain a range token. Then set the property value
  * at the specified range to the data required
  ***************************************************************************/
  FUNCTION GetDataNum(range varchar2) RETURN binary_integer IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retNum);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retNum;
  END GetDataNum;

  FUNCTION GetDataReal(range varchar2) RETURN double precision IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retReal);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retReal;
  END GetDataReal;

  FUNCTION GetDataStr(range varchar2) RETURN varchar2 IS
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retStr);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retStr;
  END GetDataStr;

  FUNCTION GetDataDate(range varchar2) RETURN Date IS
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retDate);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retDate;
  END GetDataDate;

  FUNCTION InsertData(range varchar2, data DATE, type varchar2)
    RETURN binary_integer IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;

  FUNCTION InsertDataReal(range varchar2,
                          data  double precision,
                          type  varchar2) RETURN binary_integer IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertDataReal;

  FUNCTION InsertData(range varchar2, data varchar2, type varchar2)
    RETURN binary_integer IS
  BEGIN
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;

  /******************************************************************************
  * Insert a chart at the x and y position of the spreadsheet with the desired
  * height and width. Then also uses the ChartWizard to draw the graph with data
  * in a specified range area with a specified charting type.
  *******************************************************************************/
  FUNCTION InsertChart(xpos   binary_integer,
                       ypos   binary_integer,
                       width  binary_integer,
                       height binary_integer,
                       range  varchar2,
                       type   varchar2) RETURN binary_integer IS
    charttype binary_integer := -4099;
  BEGIN
    ORDCOM.InitArg();
    i := ORDCOM.GetProperty(WorkSheetToken,
                            'ChartObjects',
                            0,
                            ChartObjectToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(xpos, 'I2');
    ORDCOM.SetArg(ypos, 'I2');
    ORDCOM.SetArg(width, 'I2');
    ORDCOM.SetArg(height, 'I2');
    i := ORDCOM.Invoke(ChartObjectToken, 'Add', 4, ChartObject1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    i := ORDCOM.GetProperty(ChartObject1, 'Chart', 0, Chart1Token);
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    ORDCOM.InitArg();
    ORDCOM.SetArg(RangeToken, 'DISPATCH');
    IF type = 'xlPie' THEN
      charttype := -4102;
    ELSIF type = 'xl3DBar' THEN
      charttype := -4099;
    ELSIF type = 'xlBar' THEN
      charttype := 2;
    ELSIF type = 'xl3dLine' THEN
      charttype := -4101;
    END IF;
    ORDCOM.SetArg(charttype, 'I4');
    i := ORDCOM.Invoke(Chart1Token, 'ChartWizard', 2, DummyToken);
    i := ORDCOM.DestroyObject(RangeToken);
    i := ORDCOM.DestroyObject(ChartObjectToken);
    i := ORDCOM.DestroyObject(ChartObject1);
    i := ORDCOM.DestroyObject(Chart1Token);
    RETURN i;
  END InsertChart;

  /******************************************************************************
  * Save the Excel File. WARNING: Do not specify a filename that already exist
  * since there is no graphical context, Oracle would not be able to pop
  * out a warning message for existing file. This causes Excel to hang
  *******************************************************************************/
  FUNCTION SaveExcelFile(filename varchar2) return binary_integer IS
  BEGIN
    dbms_output.put_line('Saving Excel file...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
 
    i := ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    RETURN i;
  END SaveExcelFile;

  /* Saubhik: This new function is added by me. This can overwrite an existing excel file. */
  FUNCTION SaveExcelFile(filename varchar2, overwrite VARCHAR2)
    return binary_integer IS
  BEGIN
    dbms_output.put_line('Saving Excel file...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
    IF overwrite = 'Y' THEN
      i := ORDCOM.SetProperty(applicationToken,
                              'DisplayAlerts',
                              FALSE,
                              'BOOL');
    END IF;
    i := ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    RETURN i;
  END SaveExcelFile;

  /******************************************************************************
  * Close the Excel spreadsheet and exit from it
  ******************************************************************************/
  FUNCTION ExitExcel return binary_integer is
  BEGIN
    dbms_output.put_line('Closing workbook and quitting...');
    ORDCOM.InitArg();
 
    ORDCOM.InitArg();
    ORDCOM.SetArg(FALSE, 'BOOL');
    dbms_output.put_line('Closing workbook...');
    i := ORDCOM.Invoke(WorkBookToken, 'Close', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(WorkBookToken);
    ORDCOM.InitArg();
    dbms_output.put_line('Closing workbooks...');
    i := ORDCOM.Invoke(WorkBooksToken, 'Close', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(WorkBooksToken);
    i := ORDCOM.Invoke(applicationToken, 'Quit', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
 
    i := ORDCOM.DestroyObject(WorkSheetToken);
    i := ORDCOM.DestroyObject(WorkSheetToken1);
 
    i := ORDCOM.DestroyObject(applicationToken);
    i := ORDCOM.DestroyObject(ChartObjectToken);
    i := ORDCOM.DestroyObject(Chart1Token);
    i := ORDCOM.DestroyObject(ChartObject1);
    i := ORDCOM.DestroyObject(dummyToken);
    RETURN i;
  END ExitExcel;

END ORDExcelSB;

To overwrite a excel file (save with same name) you have to call the newly written SaveExcelFile (an overloaded version of earlier function) with secnod parameter as 'Y'.


--Savig the file with same name name.
  result := Ordexcelsb.SaveExcelFile('c:\example3.xls','Y');