Expert Oracle Database Architecture, 2nd Edition

You can now download "Expert Oracle Database Architecture, 2nd Edition" book free from here.

The author of book is Thomas Kyte who is the best expert in Oracle Database.
It covers the development up to version 11g.
You can download this book and enjoy by your time.

Regarding my review it is the best book can learn you more details about database kernel and database programming.






Thanks
Mahmoud A. El-Sayed

Get Sequence Next Value in OAF

I posted Get Sequence Next Value in ADF.
Today I will create function that returns sequence next value in OAF framework.

We pass sequence name to method and it returns next value of sequence

 public Number getSequenceValue(String sequenceName) {  
     Number sequenceValue;  
     if (sequenceName != null && !"".equanls(sequenceName)) {  
       OADBTransaction transaction = getOADBTransaction();  
       sequenceValue = transaction.getSequenceValue(sequenceName);  
     }  
     return sequenceValue;  
   }  

Thanks
Mahmoud A. El-Sayed

Avoid Null Pointer Exception Part 1

Null Pointer Exception is the most common and most annoying exception in Java.
In this post I want to avoid this undesired exception.

First let's create example that raise Null Pointer Exception

   private Boolean isFinished(String status) {  
     if (status.equalsIgnoreCase("Finish")) {  
       return Boolean.TRUE;  
     } else {  
       return Boolean.FALSE;  
     }  
   }  

In previous method if we pass the value of "status" variable as null it will raise Null Pointer Exception in below line

if (status.equalsIgnoreCase("Finish")) {

Generate Source Code Scripts of Database Objects

We use a lot of editors to display source code of Oracle Database objects like Toad, PLSQL Developer, Navigator and SQL Developer and others.

I have idea to do like this editors to generate source code of database objects (Table, View, Trigger, Functions, Package, Function, Procedure, ...... etc) using Oracle Database Data Dictionary views.

I can do this using using
1-ALL_SOURCE view which contains source code of package, package body, function, procedure, library, type, type body,java source only.
You can use others view to generate tables and indexes and constraint and database links and ..... etc, but at this post I only use ALL_SOURCE

2-DBMS_METADATA built-in package which contains procedure and functions that help me to get source code directly from database with less efforts.

I created MAHMOUD_SOURCE_CODE package with below procedures to get source code of database objects
1-GET_SOURCE_CODE1
   I use DBMS_METADATA.GET_DDL function
2-GET_SOURCE_CODE2 
   I use DBMS_METADATA procedure and functions to get source code
3-GET_SOURCE_CODE3 
   I use ALL_SOURCE view

Mahmoud_SOURCE_CODE package also containts
1- WRITE_CLOB_IN_FILE
    It saves CLOB variable in physical file at directory object
2-TO_CHAR
    It converts CLOB variable to VARCHAR2 variable

Discrimination between Odd and Even Records in Oracle Forms

In tabular block in Oracle Forms, I want to discriminate between odd records with different color from color of even records alternately so the block in run time look like below image.

The idea of this post is based on my previous post Highlighting Selected Records in Oracle Forms as I use visual attribute for every navigable items in block.

Highlighting Selected Records in Oracle Forms

I have tabular block in Oracle Forms, one of block columns is check-box.
The Requirement :
User needs when he checks Check-box, Form should highlight entire record background by another different color like this image

Print Java System Properties in PLSQL

I will present how to print System.getProperties() in Java and call it from PLSQL.
I can print output in DBMS Output Console or Java Console.

The steps of this practice as below
1-Create Java Class
2-Create wrapper Procedure and Function
3-Call Wrapper Procedure and Function From PLSQL

1-Create Java Class

I create two method in Java Class

a-printProperties method which return output of system properties as string
b-printPropertiesJavaConsole method which print output of system properties in Java console

 CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED SystemProperties  
   AS import java.util.Enumeration;  
 import java.util.Properties;  
 public class SystemProperties {  
   public static String printProperties() {  
     StringBuilder sb = new StringBuilder();  
     Properties properties = System.getProperties();  
     Enumeration properiesEnum = properties.propertyNames();  
     while (properiesEnum.hasMoreElements()) {  
       String propertyKey = (String)properiesEnum.nextElement();  
       sb.append(propertyKey + " = " + System.getProperty(propertyKey) + "\n");  
     }  
     return sb.toString();  
   }  
    public static void printPropertiesJavaConsole() {  
     System.out.println(printProperties());  
   }  
   public static void main(String[] args) {  
     System.out.println(printProperties());  
   }  
 }  
 /  

Timer in PLSQL and SQL

Sometime when developing code in PLSQL, we need to trace time of execution in run-time.
So I will create timer package to help us.
I will divide this post to two partition
1- Timer in PLSQL
    This will trace time of PLSQL code
2- Timer in SQL
    This will trace time of SQL code

Displaying Array as String in Java

Sometime in your code you want display array in string format.
Expected result is to display array collection as string (scalar data type) separated by comma.
Any array contains toString() method which returns informative only and doesn't contain any content of array.

For Example if I use toString() method with array
    public static void main(String[] args) {
        String[] str = new String[3];
        str[0] = "Mahmoud";
        str[1] = "Ahmed";
        str[2] = "El-Sayed";

        System.out.println(str.toString());
    } 

The output in console is
>>>>>>>>use toString() against array
[Ljava.lang.String;@9931f5

Sort String in PLSQL

I will develop PLSQL function which sorts string regarding ACII code of characters
This function uses a lot of intelligence to sort strings.

Idea of Sorting
I depend on create PLSQL table indexed by BINARY_INTEGER which its index mapped to ASCII code of every character in my string and I store in table number of occurrence per every character.

Execute Operating System Commands from PSLQL

I need to execute commands from my code written in PLSQL.
In Oracle Forms 10g, I use HOST procedure
I can execute this by three ways
1- Using Java Class
    Develop my own Java class to execute command, then create wrapped procedure for it in PLSQL

2- Using DBMS_SCHEDULER package
    This package is available in oracle from version 10g.
    To use this package you should run service OracleJobScheduler[SID] for example if my service name is ORCL the service will be OracleJobSchedulerORCL

3- Using DBMS_PIPE package

Ugly count(*)

I noticed at a lot of application that developers used count(*) in their code repeatably.

I don't encourage any developer to use count(*) as I called it "Ugly count(*)" as If you want to retrieve count of all result set regardless null values then use count(1)

I will explain why not using count(*)

Let's run below query against HR schema

select count(*) from employees;

It returns 108 and takes 73 msec to execute.

Then run below query against HR schema also

select count(1) from employees;

It returns 108 and takes 24 msec to execute.

I will try again to query count by primary key (EMPLOYEE_ID)

select count(EMPLOYEE_ID) from employees;

It return 108 and takes 25 msec to execute.

I will try again to query count by non primary key which have null value

select count(COMMISSION_PCT) from employees;

It return 36 and takes 17 msec to execute.

Note : Time of execution may differ in your machine.

Someone may ask question : Why count(*) take time more than count(1)?
The Answer : When I use * in select statement, Oracle internal treat it as Record Type. So it count against composite type and takes more time . While count(1) he counts against scalar type.

Conclusion

Always use count(1) if you need to count in whole result set.
Take care that count against column that has null values, He doesn't count null values.

Thanks
Mahmoud A. El-Sayed

Simulate Oracle Built-in Functions in Java

All PL/SQL developers always use below function  a lot anywhere in their codes

NVL , NVL2 , DECODE and COALESCE

Previous function aren't available directly in Java, So I will develop simulation to them in Java.
All functions return Object so you should cast it to your class in using.

 public class MahmoudUtils {  
   public static Object decode(Object[] args) throws Exception {  
     Object compareItem = args[0];  
     int maxIndex = args.length - 1;  
     int indx = 1;  
     if (compareItem != null) {  
       for (; indx < maxIndex; indx += 2) {  
         if (compareItem.equals(args[indx])) {  
           return args[indx + 1];  
         }  
       }  
     } else {  
       throw new Exception("MahmoudUtils.decode :: First Element in array is null value");  
     }  
     return indx == maxIndex ? args[indx] : null;  
   }  
   public static Object coalesce(Object[] args) {  
     for (int indx = 0; indx < args.length; indx++) {  
       if (args[indx] != null) {  
         return args[indx];  
       }  
     }  
     return null;  
   }  
   public static Object nvl(Object firstParameter, Object secondParameter) {  
     return (firstParameter != null ? firstParameter : secondParameter);  
   }  
   public static Object nvl2(Object firstParameter, Object secondParameter, Object thirdParameer) {  
     return (firstParameter != null ? secondParameter : thirdParameer);  
   }  
   public static void main(String[] args) throws Exception {  
     System.out.println("====Test decode");  
     System.out.println(MahmoudUtils.decode(new Object[] { "mah", 1, null, 3, 4, "mah", "Yes, Iam Mahmoud", 9 }));  
     System.out.println("====Test NVL");  
     System.out.println(MahmoudUtils.nvl(null, "First Parameter is null"));  
     System.out.println(MahmoudUtils.nvl("First Parameter is not null", "First Parameter is null"));  
     System.out.println("====Test coalesce");  
     System.out.println(MahmoudUtils.coalesce(new Object[] { null, 1, null, 3, 4, "mah", "Yes, Iam Mahmoud", 9 }));  
     System.out.println("====Test NVL2");  
     System.out.println(MahmoudUtils.nvl2("First Value is not null", "Not null value", "Null value"));  
   }  
 }  

Conclusion

You can develop all important Oracle built-in function that don't exists directly in Java.

Thanks