Cascading Truncate

In pre-Oracle 12c , we cannot truncate table , if the table has referential integrity with its child table.
First we would need to truncate table the child table , and then the parent . If we attempt to truncate parent table , then we would get "ORA-02266" . 

However , in Oracle 12c , we could truncate the table , if the referential integrity is set to "on delete cascade" . The cascade of the truncate can go up to multiple levels ( children , grand children and so on .. )  . 

Needless to mention that this needs to be done with extreme caution , as "TRUNCATE" cannot rolled back in Oracle ( In SQL Server , it could be rolled back if placed in a transaction . That beats the definition of a DDL .. In any case , that is a subject of a different post) . 

Comments welcome.

Here is an example in Oracle 11g.

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> Create table emp as select * from scott.emp ;
Table created.
 
SQL> Create table dept  as select * from scott.dept ;
Table created.

SQL> alter table dept add constraint pk_dept primary key ( deptno) ;
Table altered.

SQL> alter table emp add constraint pk_emp primary key ( empno) ;
Table altered.

SQL> alter table emp add constraint fk_emp_dept foreign key ( deptno )  references dept(deptno) on delete cascade;
 
Table altered.

SQL> truncate table dept cascade;
truncate table dept cascade
                    *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword


SQL> truncate table dept ;
truncate table dept
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Here is from Oracle 12c.

SQL> select * from v$version ;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

SQL> drop table emp purge ;
Table dropped.

SQL> drop table dept purge ;
Table dropped.

SQL> Create table emp as select * from scott.emp ;
Table created.

SQL> Create table dept  as select * from scott.dept ;
Table created.

SQL> alter table dept add constraint pk_dept primary key ( deptno) ;
Table altered.

SQL> alter table emp add constraint pk_emp primary key ( empno) ;
Table altered.

SQL> alter table emp add constraint fk_emp_dept foreign key ( deptno )  references dept(deptno) on delete cascade;
Table altered.

SQL> truncate table dept cascade;
Table truncated.

dbms_output in SQL Developer.

In sqlplus , you can shorten some of the SET options and it works the same as if the full command is issued. 

For example , to print the output from dbms_output , you can either use SET SERVEROUT ON or SET SERVEROUTPUT ON. 

SQL> set serverout on
SQL> begin
  2     dbms_output.put_line ('hello Zahir');
  3  end;
  4  /
hello Zahir

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> begin
  2     dbms_output.put_line ('hello Zahir');
  3  end;
  4  /
hello Zahir

PL/SQL procedure successfully completed.


But in SQL developer it expects the full text of the command . If you the first set of commands ( aka SET SERVEROUT ON) , you would get a warning message .

line 1: SQLPLUS Command Skipped: set serverout on
anonymous block completed

If you issue the latter , it works fine.

Encryption in Oracle database.

There are few ways to enforce security in oracle database . Virtual Private Database ( VPD)  , Oracle Label Security  (OLS) , Transparent Data Encyption , Data Redaction , Vault , and so on.  These options can be used to enforce security in the database ; however for some privileged users  , the data is  visible in the clear text.  To mitigate this issue , we can store the sensitive information encrypted.  We can use dbms_crypto ( in 10g and later ) or dbms_obfuscation_toolkit  ( in pre-10g  releases ) .


Here is  the simple example .
Let us create two functions . Ideally , I would prefer all the stored procedures and functions in a package . This is for illustrations only.

For this to work , the developer has to granted  execute privilege on dbms_crypto from a user with SYSDBA privilege .

SQL> show user
USER is "ZAHIR"
SQL> CREATE OR REPLACE FUNCTION encrypt_constant
  2    RETURN INTEGER
  3  AS
  4  BEGIN
  5    RETURN DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
  6  END ;
  7  /

Function created.

SQL> CREATE OR REPLACE FUNCTION rtn_key ( p_key in varchar2)
  2  return raw
  3  as
  4  begin
  5  return utl_raw.cast_to_raw(rpad(p_key,32));
  6  end ;
  7  /

Function created.


The above functions are created to set the encryption algorithm  and to return the raw from varchar.

SQL> drop table t_users purge
  2  /

Table dropped.

SQL> CREATE TABLE t_users

  2    (

  3      usid     INT ,

  4      lname    VARCHAR2(50) ,

  5      clear_password VARCHAR2(20) ,

  6      encrypted_password raw(32)

  7    )

  8    /

Table created.

Now , let us use the two functions to use it in our insert statement.

SQL> INSERT

  2  INTO t_users

  3    (

  4      usid ,

  5      lname ,

  6      clear_password ,

  7      encrypted_password

  8    )

  9    VALUES

 10    (

 11      1 ,

 12      'Zahir Mohideen' ,

 13      'password' ,

 14      DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW ('password', 'AL32UTF8'), typ => encrypt_constant , KEY => rtn_key(TO_CHAR(1)) )

 15    ) ;

1 row created.

SQL> set linesize 1000

SQL> select clear_password , encrypted_password from t_users;

CLEAR_PASSWORD       ENCRYPTED_PASSWORD

-------------------- ----------------------------------------------------------------

password             A798E993760F0AD22E3AAD0814A98A15

Now , as you can see , the password is stored in clear text format in "CLEAR_PASSWORD" column and the password is stored in encrypted format in "ENCRYPTED_PASSWORD" column . Ideally , any security minded professional would prefer to the sensitive information to be encrypted. 

To decrypt ,  we can use dbms_crypto.decrypt procedure , as seen below.

SQL>  SElect UTL_I18N.RAW_TO_CHAR  (

  2   DBMS_CRYPTO.DECRYPT ( src => encrypted_password ,  typ => encrypt_constant ,  key =>  rtn_key(TO_CHAR(usid)) )

  3   ) pwd from t_users ;

PWD

-------------------------

password

SQL>

So , here is our simple example to encrypt and decrypt using one of Oracle supplied packages. 

Comments welcome.