Recycle Bin in Database

Oracle introduced in database 10g new feature called "Recycle Bin" to store the dropped database objects.
If any table is dropped then any associated object to this table such as indexes, constraints and any other dependent object are renamed with a prefix of bin$$.

Use of Recycle Bin

If user drop an important object accidentally, and he want to get it again.
With Recycle Bin feature user can easily restore the dropped objects.

Enable and Disable Recycle Bin

You can use the below query to distinguish which Recycle Bin is enabled or no

 SELECT Value FROM V$parameter WHERE Name = 'recyclebin';  

It will return on or off
on means that Recycle Bin is enabled and off is disabled.

You can enable and disable Recycle Bin per session and system, there fore you can use the below scripts to enable and disable Recycle Bin per session or system.

 ALTER SYSTEM SET recyclebin = ON;  
   
 ALTER SESSION SET recyclebin = ON;  
   
 ALTER SYSTEM SET recyclebin = OFF;  
   
 ALTER SESSION SET recyclebin = OFF;  

WITH Clause in SELECT statement

I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITH clause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Find Unused Columns in Oracle Database

Sometimes during development of new systems, you may add new columns to tables and then you don't use it and forget dropping it.

So you want to know which these columns to drop.
Usually unused columns have NULL value, So I created a function to return array of column names in my schema have NULL value.

I created GET_NULL_COLUMNS function returns VARRAY of varchar2
It has only one parameter (IN_TABLE_NAME)
If I pass a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.

OAF : Upload Excel File to Database

I want to allow user to upload excel file in database from OAF page.

Suppose that excel file contains below columns

EmpNo

EmpName

Job

Suppose also that I have Entity Object named XxxEmpEO and I created View Object XxxEmpVO based on previous entity object which has below attributes

EmpNo

EmpName

Job

Scenario

I added new item in page of type messageFileUpload ["uploadExcelFile" ] and Button ["uploadButton"].
If user click a button, I will upload excel file that is entered in messageFileUpload item to Entity Object and then commit changes to database.

Reset Sequence Value

I have old table already have a lot of data, I take decision to create new sequence to use it for getting serials in primary key of table to it.

For example : table SCOTT.EMP has EMPNO primary key and I want to create new sequence EMPNO_SEQ to store NEXTVAL of sequence in EMPNO column.

CREATE SEQUENCE SCOTT.EMPNO_SEQ
   START WITH 1
   MAXVALUE 9999999
   MINVALUE 0
   CACHE 25;

Oooooops, EMPNO column already has stored data that is maximum than sequence next value.
So I take decision to create generic procedure to reset sequence next value to maximum value of primary key in any table.

I created RESET_SEQUENCE procedure which takes three parameters
a-IN_SEQUENCE_NAME : name of sequence that I will use.
b-IN_TABLE_NAME : name of table which I will store sequence next value in its column
c-IN_COLUMN_NAME : name of column, If it is null I will get column which is primary key

Handle Business Days

Introduction
In real business life we consider days as business days only, we exclude holidays and weekends from our calendar days.
So today I will produce solution of how to work with business days.

At first public holidays are different from country to others so I will create table to store every public holidays.

 CREATE TABLE HOLIDAYS (HOLIDAY_DATE DATE, DESCR VARCHAR2 (100));  
   
 ALTER TABLE HOLIDAYS ADD (  
  CONSTRAINT HOLIDAYS_PK  
  PRIMARY KEY  
  (HOLIDAY_DATE));  

I should get weekends days also so I will get using below query
Note that at my country weekend is Fridays and Saturday.

   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION 
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  

The previous query contains whole weekends from 5th January 1900 to 18th August 2091.

Now I will create database view for whole holidays( Public holidays and weekends)

 CREATE OR REPLACE VIEW HOLIDAYS_VIEW  
 (HOLIDAY_DATE)  
 AS   
 SELECT HOLIDAY_DATE FROM HOLIDAYS  
 UNION  
   SELECT TO_DATE ('05-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999  
 UNION  
   SELECT TO_DATE ('06-01-1900', 'DD-MM-YYYY') + ( (LEVEL - 1) * 7)  
    FROM DUAL  
 CONNECT BY LEVEL <= 9999;  
   

Interchange the Values of Columns

Sometimes you want to interchange the values of two columns in specific database table.

Suppose that I have EMP table with column EMPNO, ENAME, JOB, and by wrong the value in ENAME column  is the value of JOB column and vice versa.
To correct the data I should interchange the values of ENAME and JOB.

I have three solutions for doing this
1- Add temporary column to table
2- Rename columns
3-DML statement

ADF : Get Key from Resource Bundle

Resource bundles contain locale-specific objects.
In this way, you can write program code that is largely independent of the user's locale isolating most, if not all, of the locale-specific information in resource bundles.

This allows you to write programs that can:
    a- be easily localized, or translated, into different languages
    b- handle multiple locales at once
    c- be easily modified later to support even more locales

Generate Random Password in Oracle

In my application I need to generate password for users first time after registration.
The generated password must has some criteria which system administrator will configure it.
1- Character should be UPPERCASE                              =====> Abbreviation [U]
2- Character should be LOWERCASE               =====> Abbreviation [L]
3- Character should be NUMBER                  =====> Abbreviation [N]
4- Character should be any character                      =====> Abbreviation [A]
5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]

So I thought to create dynamic function "RANDOM_PASSWORD" to return random password regarding to previous criteria.
The the system administrator will pass criteria per every character in password text to function and it will return random password
For example :-
first character should be UPPERCASE               ======> U
second character should be LOWERCASE          ======> L
third character should be NUMBER                   ======>N
forth character should be any character             ======>A
fifth character should be NON-ALPHANUMERIC  ======>S
sixth character should be Number                    ======> N   

This will generate string "ULNASN" regarding to abbreviation.

Play with NULL Value

NULL is big problem in whole programming language, Today I will write about NULL and  what's the problems we face in code and tips to play with NULL values.

Calling any method or variable of Class has NULL value raise exception in other programming language like C++, Java, C# ,.... etc but in PLSQL it doesn't raise any exception.
If I use NULL in any calculation or logical condition, the output will be NULL.

I wrote before about Three-Valued Logic and the problem  in three-valued logic in PLSQL is NULL value.

Lets see example work with NULL before begin illustration.

CREATE OR REPLACE PROCEDURE MY_NAME (IN_NAME VARCHAR2)
IS
BEGIN
   IF IN_NAME != 'Mahmoud'
   THEN
      DBMS_OUTPUT.PUT_LINE ('My name is not Mahmoud');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My name is Mahmoud');
   END IF;
END;

BEGIN
   MY_NAME (NULL);
END;

If I run previous code it will print

 My name is Mahmoud  

Playing with LOB Data Types

Lobs are the most difficult data type to store and retrieve in oracle database.

In this article, I am going to discuss extensively how to manipulate LOBs in Oracle database.

LOBs that are stored in the database itself like BLOB,CLOB,NCLOB.

BFILE which is stored outside the database as Operating System files.

BFILEs act as a pointer and store the location of the external OS files in database tables.

Max Function vs Databse Sequence

In database design we always use sequence numbers for primary keys(unique values) and We can implement this by database sequences or getting max value in the column using MAX function.
I called this approach "Sequence VS MAX()".
I will explain every approach first then and list drawbacks for every one.

Create View with Parameter

From the title of this post you guess that oracle give us capability to create view with parameter, but this is wrong, don't think good of oracle to give you this capability as straight forward.

I have workaround to do this capability by the following techniques

1-virtual private database context
2-global package variable
3-Lookup Tables

Insert only One Record in Table

Sometimes you have table in your system that has only one record like Configuration, Setup, Settings tables.
You want to prevent users from inserting more than one record in this table, To do this you can use the below INDEX .

CREATE UNIQUE INDEX ONE_ROW_INDEX
   ON TABLE_NAME (1);

If you try to insert more than one record in previous table you will get below exception

ORA-00001: unique constraint (SCHEMA.ONE_ROW_ALLOWED) violated

Thanks
Mahmoud A. El-Sayed

Dates in Oracle Database

Introduction

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY(You can change it). Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999  .

Oracle database supports simple date(True Date) and time(Date and Time) which stores in standard internal format.
Oracle supports a set of built ins function for manipulating date and time.

Date Format

As we mentioned before that oracle stores Dates internal in numeric format, but in displaying dates it displays it in different formats.
The default date format is "DD-MON-YY", the conversion of formats is done by TO_CHAR function that has below syntax

 TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');  

Date : is date value
NLS_PARAMETERS : is an optional and determine different NLS parameters in conversion.
DATE_FORMAT: It contains different format from below table

Insight Code in Toad

In our daily programming  life we write repeating code more times so we want a tool to help us in doing our tasks.
I usually use toad insight so I will illustrate how can TOAD help us in this issue.

I have previous post about doing code template in Toad  "Toad Code Template" you can read it from here.

In this post I will explain how to use code insight in TOAD.
In your code editor when writing for example package name and type dot ".", the editor should display whole members of packages subprograms (function, procedures,types and global variables) like below

The previous drop-down list is displayed after specific period after typing dot "." or immediately by pressing CTRL+T keys

Store Physical XML Files in Database Table

I have XML files stores as physical files and I want to store these file on database table.

Scenario

1- Create a table for storing XML files
2- Create Directory object in database to refer to path of XML files
3- Use my previous post List Contents of Directory to list all files in directory
4- Read every XML file and store it on database table

Encrypt and Decrypt Passwords in Database

Sometimes we store passwords in database table regarding to business requirement.
If we store password as plain text in table, Everyone who have access to database can read password easily. That's mean big security hole.

So I decided to develop package for encrypting and decrypting password.
I used DBMS_OBFUSCATION_TOOLKIT, DBMS_CRYPTO built-ins package to help me doing encryption and decryption.

I developed MAHMOUD_ENCRYPT_DECRYPT package which contains four functions (ENCRYPT1, ENCRYPT2, DECRYPT1, DECRYPT2) .

ENCRYPT1 and  DECRYPT1 functions use DBMS_OBFUSCATION_TOOLKIT package.
ENCRYPT2 and  DECRYPT2 functions use DBMS_CRYPTO package.

Commit After n Updates

If you have table has a millions of rows and you want to update whole rows in table like below statement

UPDATE TABLE_NAME

SET COLUMN_NAME='XXXXXX';

COMMIT;

It will raise an exception because of limited size of UNDO/ROLLBACK log file.
ORA-1555 errors, contact your DBA to increase the undo/ rollback segments. 

To solve this problem by code, you can commit after n updates to ignore overloading redo log file.

Java JDBC VS Java in Database

We can use Java code directly in PLSQL to manipulate database (insert data , update , do transaction .... etc) , and also we can write java code to manipulate database through JDBC connection.

When you have two solutions to do the same task, you should wait and choose the best solution.
So in my post today I will illustrate the difference between embedded java in PLSQL and Java run through JDBC connection.

For our demo I will create java code to delete and insert record in SCOTT.EMP table.
I will write code to run from PLSQL and write the same code to run through JDBC connection.

I created below Java class contains dmlOperation() method which do delete and insert record in SCOTT.EMP table and track time used for finishing transaction.