Returning a JDBC result set from an Oracle stored procedure

Returning a JDBC result set from an Oracle stored procedure

Bruce P. Blackshaw

Introduction

You would think that returning a JDBC result set from an Oracle PL/SQL stored procedure would be quite straightforward. Unfortunately, it's not as easy as it sounds. But reading this article should tell you all you need to know. Any contributions or corrections welcome - please email me.

There are three basic steps. First, you have to write the stored procedure - which in Oracle is a bit tricky if you're not familiar with PL/SQL. Testing it using the Oracle command-line tool sqlplus is also quirky. And calling it via JDBC isn't exactly standard either.

Writing the stored procedure

If you are used to Sybase or SQLServer, returning a result set from a stored procedure is easy. Just finish the procedure with a "select x,y,z from my_table", selecting whatever columns you wish to return.

Not so for Oracle. You need to use cursors.

A cursor is a work area for a SQL statement that returns one or more rows. It allows you to fetch rows from the result set one by one. Cursors aren't particularly difficult to use, but to return a result set from a PL/SQL stored procedure, you must use a cursor variable. Cursor variables are basically pointers to cursors, and you use them to pass around references to cursors, such as a parameter to a stored procedure. The PL/SQL type of a cursor variable is REF CURSOR.

To use cursor variables, you must define a REF CURSOR type. This type can be strong or weak. Strong types are tied to a specific set of a table's columns. This means a strong REF CURSOR type can only be used with queries (i.e. cursors) returning those columns. Weak types can refer to any cursor, and so are much more flexible. They do, however, sacrifice type safety.

The easiest way forward is to define a weakly typed REF CURSOR in a PL/SQL package. Packages are used in PL/SQL for partitioning functionality. Below, a weak REF CURSOR type called REF_CURSOR is defined in a PL/SQL package called types.

CREATE OR REPLACE PACKAGE types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

This definition can now be used in all stored procedures to declare a variable of type REF CURSOR.

We can now write a stored procedure that returns a REF CURSOR, that in JDBC we can process as a result set.

Assume we start from a table defined as below.

CREATE TABLE STOCK_PRICES(
    RIC VARCHAR(6) PRIMARY KEY,
    PRICE NUMBER(7,2),
    UPDATED DATE )

Here we have a table of stock prices, with the RIC (Reuters Instrument Code) as the primary key.  We define a PL/SQL function that simply declares a cursor that returns all columns for stocks below a certain price.

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
    RETURN types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR
        SELECT ric,price,updated FROM stock_prices
        WHERE price < v_price;

    RETURN stock_cursor;
END;

These can all be created via the sqlplus command-line tool. Download the SQL script, and start up sqlplus.

C:\>sqlplus username/password

Once in sqlplus, run the script. If you start up sqlplus in the directory the SQL script is in, you can run it using the @ command:

SQL>@stock_prices

You should get an encouraging list of messages such as "1 row created", "Package created", "Function created". If instead you get error messages such as "insufficient privileges", your user must be given additional database privileges. The predefined Oracle roles CONNECT and RESOURCE should be what you need. Login as the system user and run the below:

SQL>GRANT CONNECT, RESOURCE TO username;

Testing the stored procedure

To test the stored procedure, we can either execute it from within an environment that allows you to execute stored procedures and supply their parameters, such as SQL Navigator or Toad, or we can run it from within sqlplus.

Executing a stored procedure from within sqlplus is straightforward once you know how.

Firstly, start up sqlplus and declare a sqlplus variable of type refcursor. This declares a sqlplus variable of type refcursor (a sqlplus type), called "results":

SQL> var results refcursor

Next, execute the procedure, supplying a number as a parameter, and assigning the result into our variable, "results". Note the unusual syntax.

SQL> exec :results := sp_get_stocks(20.0)

PL/SQL procedure successfully completed.

Finally, use the sqlplus print statement to print out the result set

SQL> print results

RIC PRICE UPDATED
------ --------- ---------
AMZN 15.5 21-OCT-01
SUNW 16.25 21-OCT-01
ORCL 14.5 21-OCT-01

If this works successfully, similar to the above, the stored procedure (well, function) is working correctly.

Calling the stored procedure from Java

There are some Oracle-specific tricks to calling the above stored procedure from Java.

The query string can be in the Oracle format or the standard JDBC callable statement syntax. If you are using the Oracle syntax, the query string is:

begin ? := sp_get_stocks(?); end;

If you are using the JDBC syntax, the query string is:

{ call ? := sp_get_stocks(?) }

In each case the spacing is important - the query will not work otherwise.

Secondly, the out parameter must be registered to be of type OracleTypes.CURSOR.

And finally, use getObject() to retrieve the result set.

The code (without error handling) is illustrated below. A more complete code sample can be downloaded from here.

String query = "begin ? := sp_get_stocks(?); end;";

CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setFloat(2, price);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) {
    System.out.println(rs.getString(1) + "\t" +
        rs.getFloat(2) + "\t" +
        rs.getDate(3).toString());
}

Hopefully, this code will be all you need to get started on calling your own Oracle stored procedures via JDBC.

References

1. Oracle JDBC FAQ
2. Oracle JDBC How-To Documents

참조 :
http://www.idevelopment.info/data/Programming/java/jdbc/PLSQL_and_JDBC/RefCursorExample.java

--TABLE 만들기
--시퀀스 생성
CREATE SEQUENCE tt1_seq start with 1
/
--테이블 생성
CREATE TABLE tt1
(
 temp_id   NUMBER not null primary key,
 data1   VARCHAR2(50),
 data2   VARCHAR2(50),
 create_time TIMESTAMP
)
/
--트리거 생성
CREATE OR REPLACE TRIGGER tt1_ti
   before insert on tt1
   for each row
   declare
    l_temp_id number;
   begin
    select tt1_seq.nextval
      into l_temp_id
      from dual;
   :new.temp_id := l_temp_id;
   end;
/


-- 프로시저 패키지 생성
-- 프로시저 단독으로 사용가능 하지만 원활한 관리를 위해 패키지구성을 통해 활용한다.
CREATE OR REPLACE PACKAGE DATA_PROCESSES IS
 PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2,
    l_vdata2  IN VARCHAR2);
 PROCEDURE delete_tt1(l_nid  IN NUMBER);
 PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2,  l_vdata2  IN VARCHAR2);
END DATA_PROCESSES;
/
-- 프로시저 바디 생성
CREATE OR REPLACE PACKAGE BODY DATA_PROCESSES AS
    -- 삽입 프로시저 생성
     PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2, l_vdata2  IN VARCHAR2)    
     IS  l_stmt_str VARCHAR2(200);    
     BEGIN
        SAVEPOINT start_transaction;  ---- 세이브 포인트 지정
        l_stmt_str := 'INSERT INTO tt1 (data1,  data2, create_time) ' || 'VALUES(:l_vdata1, :l_vdata2, SYSDATE)';
        EXECUTE IMMEDIATE l_stmt_str   USING l_vdata1, l_vdata2;
    
     EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query:' ||    SQLERRM ||'.  Please try again later.');
     END;
    
    -- 삭제 프로시저 생성
     PROCEDURE delete_tt1(l_nid  IN NUMBER)
     IS  l_stmt_str VARCHAR2(200);
     BEGIN
        SAVEPOINT start_transaction; ---- 세이브 포인트 지정
        l_stmt_str := 'DELETE FROM tt1 WHERE temp_id = :1';
        EXECUTE IMMEDIATE l_stmt_str USING l_nid;
     EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query: '|| SQLERRM ||'.  Please try again later.');
     END;
    
    -- 업데이트 프로시저 생성
    /*
        변수 입력받는것과 USING 구문만 효력을 발생한다. sql문에서는 using문에 입력된 순서대로 변수값을 매칭 시킨다.
        l_stmt_str :=에서 :1 :2 :3 이나 :3 :1 :2나 같은 결과를 초래한다.
    */
    PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2, l_vdata2  IN VARCHAR2)
    IS    l_stmt_str VARCHAR2(200);
    BEGIN
        SAVEPOINT start_transaction;  ---- 세이브 포인트 지정
        l_stmt_str := 'UPDATE tt1 SET data1=:3, data2=:1 WHERE temp_id = :2';
    EXECUTE IMMEDIATE l_stmt_str USING  l_vdata1, l_vdata2, l_nid ;
    
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query: '|| SQLERRM || '.  Please try again later.');
    END;
END DATA_PROCESSES;


[TEST]
exec DATA_PROCESSES.insert_tt1('data_clm1','data_clm2');
exec DATA_PROCESSES.insert_tt1('data_clm3','data_clm4');
select * from tt1;
exec DATA_PROCESSES.delete_tt1(5);
select * from tt1;
exec DATA_PROCESSES.update_tt1(4,'data1111','data2222');
select * from tt1;

[추천유틸]
원할한 테스트를 위해서 DB관련 유틸을 사용해야 하는데... 개인적으로는 간단편하게 사용할 수 있는
Benthic Software에서 만든 Golden(디비 컨트롤유틸)과 PLEdit와 (프로시져 생성 전용) 추천한다.
음.. 쉐어웨어 인데.. google에서 검색하면 암흑버전 뭐 손쉽게 얻을 수 있고 -_-;;
무엇보다 토드, 오렌지 ... 과 비교하여 볼때 경량형 프로램인지라 좋은거 같음.