Thursday, June 9, 2011

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