As Published In November/December 2000

TECHNOLOGY: Ask Tom

Java Tips, JDBC, PL/SQL Stored Procedures, and Custom Prompts

Working with Java is simpler now that it's a native part of the Oracle database. Here are some interesting questions I've received about using Java. 

I want to return a result set from a function. I saw your example on how to return it as a ref-cursor, but I need to return it as an array and then retrieve the array in Java. Do you have an example of this?

To return an array instead of a result set from a stored procedure, you'll need to use a nested table type in SQL to represent an array for the stored procedure and then use some Oracle extensions to handle the array in the Java code.

To start with, create database objects such as: 

 create or replace type SimpleArray
 as table of varchar2(30)
 /
 create or replace function getSimpleArray return SimpleArray
   as
       l_data simpleArray := simpleArray();
   begin
       for i in 1 .. 10 loop
           l_data.extend;
           l_data(l_data.count) := 'entry ' ||
  i;
       end loop;
       return l_data;
   end;
  /  

You end up with an array of varchar2(30)—like an array of strings, and a function that returns that array type to the caller. The Java code to retrieve and process this array would then look like Listing 1.

Code Listing 1:Java code to retrieve and process an array 

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class Array
{

public static void main(String args[]) throws Exception
{

 DriverManager.registerDriver(new 
oracle.jdbc.driver.OracleDriver());

 Connection conn = DriverManager.getConnection
           ("jdbc:oracle:thin:@aria:1521:ora8i", "scott", "tiger");

 OracleCallableStatement stmt = 
(OracleCallableStatement)conn.prepareCall
                  ( "begin ? := getSimpleArray; end;" );

 // The name we use below, SIMPLEARRAY, matches the name of 
our 
 // type we defined in SQL above
 stmt.registerOutParameter( 1, OracleTypes.ARRAY, 
"SIMPLEARRAY" );
 stmt.executeUpdate();

 // Now we can get the array object back and print out some 
meta data about it
 ARRAY simpleArray = stmt.getARRAY(1);

 System.out.println ("Array is of type " +
            simpleArray.getSQLTypeName());

 System.out.println ("Array element is of type code " +
            simpleArray.getBaseType());

 System.out.println ("Array is of length " +
            simpleArray.length());

 // Lastly, we'll print out the entire contents of our array
 String[] values = (String[])simpleArray.getArray();

 for( int i = 0; i < values.length; i++ )
    System.out.println( "row " + i + " = '" + values[i] + 
"'" );

 stmt.close();
 conn.close();
}
}

When executed, this routine will print: 

$ java Array
Array is of type SCOTT.SIMPLEARRAY
Array element is of type code 1
Array is of length 10
row 0 = 'entry 1'
...
row 9 = 'entry 10'

You can use this method to send and receive arrays of data back and forth between Java and a stored procedure, using JDBC. For more information, see "Working with Arrays" in the Oracle JDBC manual. 

Getting the Time Down to the Millisecond

Our application needs to get a date value including millisecond in PL/SQL packages, but the date datatype in Oracle doesn't support millisecond. Do you have some ideas to solve this kind of problem?

This is a frequently asked question. Because the Oracle date datatype can measure time only down to seconds and Java date-time datatypes have much finer granularity, you can write a small Java stored procedure that gets the time with the scale you want. The stored procedure can look like this: 

ops$tkyte@DEV8I.WORLD> CREATE or replace
 
JAVA SOURCE
 
 2 NAMED "MyTimestamp"
 
 3 AS
 
 4 import java.lang.String;
 
 5 import java.sql.Timestamp;
 
 6 
 
 7 public class MyTimestamp
 
 8 {
 
 9     public static String getTimestamp()
 
 10     {
 
 11       return (new
 
 12   Timestamp(System.currentTimeMill is())). toString();
 
 13     }
 
 14 };
 
 15 /
 
Java created.

That's a very simple Java routine to get the current system clock and format it into a string for you. This function will return that string, and all you need now is a small wrapper function: 

ops$tkyte@DEV8I.WORLD> create or replace
function 
my_timestamp 
return varchar2
  2 AS LANGUAGE JAVA
   3 NAME 'MyTimestamp.getTimestamp() return
 java.lang.String';
   4 /
Function created.

To tell SQL how to interface with this Java routine and that we are ready to go: 

ops$tkyte@DEV8I.WORLD> select my_timestamp,
 
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
  
  2           from dual
 
  3 /
 
MY_TIMESTAMP              TO_CHAR(SYSDATE,'YY
  ----------------------------------------------
  2000-06-22 13:47:53.376      2000-06-22 13:47:53

You cannot store this time in an Oracle

date type

, but you can store it in a

string

or in a number (e.g.: if you stored the number 20000622134753376, it would be sortable, and you could do range comparisons and so on).

Getting a Result Set Without Using OracleTypes.CURSOR

I tried to retrieve a refcursor from a stored procedure, using OracleTypes. CURSOR, and it works. But to do this I have to load the oracle.jdbc.driver, which is not agreeable. I need to use java.sql and retrieve multiple rows, using a stored procedure. Is there a way to retrieve multiple records by calling a stored procedure from a Java program without using the OracleTypes. CURSOR and not loading the oracle.jdbc.driver?

Basically you need a method to get a result set in JDBC without using any Oracle extensions. Instead of calling a stored procedure to get a query result, use a query to call a stored procedure, and that procedure then becomes the result set.

Here is a small example. You must create an object type in SQL to represent your result set type. For example, if I wanted to be able to return a result set with X and Y of type

number

, I would do the following: 

ops$tkyte@DEV8I.WORLD> create or replace
 type myScalarType
  2 as object ( x int, y int )
  3 /
Type created.
ops$tkyte@DEV8I.WORLD> create or replace
  type myTableType
  2 as table of myScalarType;
  3 /
Type created.

Now I can create a function that returns that type. 

ops$tkyte@DEV8I.WORLD> create or replace
  2 function demo_proc2( p_rows_to_make_up in number )
  3 return myTableType
  4 as
  5     l_data       myTableType :=  myTableType();
  6 begin
  7     for i in 1 .. p_rows_to_make_up
  8     loop
  9         l_data.extend;
  10          l_data(i) := myScalarType( i, i );
  11      end loop;
  12      return l_data;
  13 end;
  14 /

Function created.

Next, I select * from the stored procedure: 

ops$tkyte@DEV8I.WORLD> select *
  2    from the ( select cast( demo_proc2(5)
as mytableType )
  3          from dual );
  X        Y
    ------  ------
       1        1
        2        2
       3        3
       4        4
       5        5
ops$tkyte@DEV8I.WORLD>

In this fashion, Java never calls a stored procedure but instead invokes the above Select statement, which calls the stored procedure and gives you a result set. This "trick" of selecting from a stored procedure has many other useful applications as well. For another use of this feature, see "In List Question with Bind Variables". 

Determining the Time Zone Setting of a Server

How do I figure out the time zone setting currently used by my server?

Using a tiny bit of Java, this is rather easy. It could look like this: 

ops$tkyte@8i> create or replace and compile
java source named "TZ"
  2 as
  3 import java.net.*;
  4 import java.io.*;
  5 import java.util.*;
  6 import java.text.*;
  7 public class TZ
  8 {
  9 static public void java_get_timezone(  String[] p_timezone )
  10 {
  11 Date d = new Date();
  12 DateFormat df2 = new SimpleDateFormat( "z" );
  13
 
  14     df2.setTimeZone(TimeZone.getDefault () );
  15     p_timezone[0] = df2.format(d);
  16 }
  17 }
  18 /
Java created.
ops$tkyte@8i>
  ops$tkyte@8i> create or replace
   2 procedure get_timezone( p_timezone  out varchar2 )
   3 as language java
   4 name 'TZ.java_get_timezone(
  java.lang.String[] )';
   5 /
Procedure created.

You now have a procedure

get_timezone

that when executed will return the three-character time zone that is in effect for our server. An example of this is: 

ops$tkyte@8i> declare
  2         tz varchar(25);
  3 begin
  4         get_timezone( tz );
  5         dbms_output.put_line( tz );
  6 end;
  7 /
EDT
PL/SQL procedure successfully completed.

On time, every time! 

Sleeping for Less Than a Second

The C command of sleep() has a parameter of less than one second. Does the Oracle database have an equivalent?

The builtin

dbms_lock.sleep

command has a granularity of 1 second. That is the finest level of granularity provided. Java can take it down to milliseconds if need be, if you're using Oracle8i. For example: 

create or replace
procedure micro_sleep( p_sleep in number )
as language java
name 'java.lang.Thread.sleep( long )';
/
Procedure created.

All I did here was map to the existing sleep Java function. I didn't actually write any Java at all, I just called an existing Java routine. Here is an example:

ops$tkyte@DEV8I.WORLD> declare
   2    l_start  number;
   3 begin
   4    for i in 1 .. 10
   5    loop
   6       l_start := dbms_utility.get_time;
   7       micro_sleep(100);
   8       dbms_output.put_line(
   9     to_char(round((dbms_utility.get_time-l_start)/100,2))
   10          || ' hsecs' );
   11    end loop;
   12 end;
   13 /
 .11 hsecs
 .....
 .11 hsecs
PL/SQL procedure successfully completed. 

My Personal SQL Prompt

I notice in Oracle Magazine that you have your SQL prompt set to display "user@instance". Please tell me how you accomplish this. I can get it to display the username OR the instance name but not both. Thanks!

The answer is in my

login.sql

where I have the following logic: 

column global_name new_value gname
set termout off
select lower(user) || '@' || global_name global_name from
 
global_name;
set termout on
set sqlprompt '&gname> '

I concatenate together the elements I want, and that's my prompt. The column

global_name new_value gname

is the trick here. It lets us take the result of a query and puts it into a macro

variable.

, so that later when I refer to

&gname

, it has the value of

global_name

which we just selected. If you want the SID instead of the

global_name

, just use: 

select lower(user) || '@' || instance global_name from 
v$thread 

From a Stored Procedure

How do I read files from a certain directory with PL/SQL without knowing the exact name? My program must interface with another system that puts files in a directory on the server.

UTL_FILE

only reads a file when you know the name of the file, but I don't know the name in advance. Is it possible to use wildcards (eg. '*') in the name of the file? The platform is Windows NT and we have the Jserver option.

Oracle7.3 added the

UTL_FILE

built in package allowing PL/SQL to read and write files in the server file system. One capability this package does not have however, is the ability to query a directory and see what files are available to be read. The good news is we can do this using Java relatively easily.

The interface I developed uses a global temporary table which will "lose" its rows every time you commit. You call a stored procedure by providing a DIRECTORY to scan. I put a list of all of the files that are in that directory into a temp table. If you want to "filter" the files (eg: only interested in *.txt files), use "select * from dir_list where filename like '%.txt'" to do so.

The implementation is: 

ops$tkyte@8i> GRANT JAVAUSERPRIV to ops$tkyte
             2 /

Grant succeeded.

That grant must be given to the owner of the procedure. This grant allows them access to the file system. 

ops$tkyte@8i> create global temporary table DIR_LIST
             2 ( filename varchar2(255) )
              3 on commit delete rows
             4 /
 

Table created.

This is the temporary table where we will place results. I chose a temporary table over returning an array of objects simply for ease of coding. This implementation also gives us greater flexibility as I can sort and use the where clause on the temporary table easily. For the Java code, se e Listing 2.

Code Listing 2: 

ops$tkyte@8i> create or replace
             2   and compile java source named "DirList"
             3 as
             4 import java.io.*;
             5 import java.sql.*;
             6 
             7 public class DirList
             8 {
             9 public static void getList(String directory)
             10           throws SQLException
             11 {
             12   File path = new File( directory );
             13   String[] list = path.list();
             14   String element;
             15 
             16   for(int i = 0; i < list.length; i++)
             17   {
             18     element = list[i];
             19     #sql { INSERT INTO DIR_LIST (FILENAME)
             20         VALUES (:element) };
             21   }
             22 }
             23 
             24 }
             25 /
            
Java created.
ops$tkyte@8i> create or replace
             2 procedure get_dir_list( p_directory in varchar2 )
             3 as language java
             4 name 'DirList.getList( java.lang.String )';
             5 /
            
Procedure created.

I used SQLJ over JDBC in this example because it just made it so much easier (its almost always easier to use SQLJ when coding Java stored procedures—no connection hassles, less lines of code). Here's how it works: 

ops$tkyte@8i> exec get_dir_list( '/tmp' );
 

PL/SQL procedure successfully completed.
ops$tkyte@8i> select * from dir_list where rownum < 5;
FILENAME
 -------------------------------------
 data.dat
 .rpc_door
 .pcmcia
 ps_data

Tom Kyte's bio and article index

Send us your comments