As Published In
Oracle Magazine
November/December 2000



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

By Tom Kyte Oracle Employee ACE


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
   l_data simpleArray := simpleArray();
   for i in 1 .. 10 loop
      l_data(l_data.count) := 'entry ' ||
    end loop;
    return l_data;

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


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

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

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

 // 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 " +

 System.out.println ("Array element is of type code " +

 System.out.println ("Array is of 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] + 
"'" );


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
 2 NAMED "MyTimestamp"
 3 AS
 4 import java.lang.String;
 5 import java.sql.Timestamp;
 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
return varchar2
   3 NAME 'MyTimestamp.getTimestamp() return
   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 /

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


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


, 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


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*;
  4 import*;
  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" );
  14     df2.setTimeZone(TimeZone.getDefault () );
  15     p_timezone[0] = df2.format(d);
  16 }
  17 }
  18 /

Java created.

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


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 /

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


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


where I have the following logic: 

column global_name new_value gname
set termout off
select lower(user) || '@' || global_name global_name from
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


, so that later when I refer to


, it has the value of


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


, just use: 

select lower(user) || '@' || instance global_name from 

From a Stored Procedure

Next Steps

 ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

 FOLLOW Tom on Twitter

 more Tom
 Oracle Database Concepts 11g Release 2 (11.2)
 Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions,Second Edition

 DOWNLOAD Oracle Database 11g Release 2

FOLLOW Oracle Database
 on Twitter
 on Facebook

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.


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


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*;
5 import java.sql.*;
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;
16   for(int i = 0; i < list.length; i++)
17   {
18     element = list[i];
20         VALUES (:element) };
21   }
22 }
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;



Tom Kyte's bio and article index

Send us your comments