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