2001-07-26 15:30:02

자바에서 pl/sql연동및 벡터로 리턴받기

Bookmark Fixed font Go to End

Doc ID: Note:73092.1
Subject: Using PL/SQL Units to Execute Dynamic SQL in JDBC Program
Content Type: TEXT/PLAIN
Creation Date: 07-JUL-1999
Last Revision Date: 15-AUG-2000
Language: USAENG


This article contains information on the use of PL/SQL units to execute
Dynamic SQL from a JDBC program.


JDBC provides APIs for executing Dynamic SQL using PreparedStatement.
For example:

PreparedStatement pstmt;
pstmt=conn.prepareStatement("select empno from emp where deptno=?");
ResultSet c1;
while ( ())
{System.out.println (c1.getInt(1));}


Another option of executing Dynamic SQL from JDBC is included below
and is applicable only when connecting to Oracle8i. In this case, the
procedure uses a PL/SQL procedure which returns a REF CURSOR.

A REF CURSOR is a pointer to a cursor. Stored procedures can return
user-defined types, or cursor variables, of the REF CURSOR category.
This output is equivalent to a database cursor or a JDBC result set.
A REF CURSOR essentially encapsulates the results of a query.

Note: Opening a REF CURSOR for a statement present in a variable
is only supported with Oracle8i.

Example of REF CURSOR

create or replace package test_ref_cursor as
type gc is REF CURSOR;
procedure test_ref_cursor(v1 OUT gc, v2 in varchar2);

create or replace package body test_ref_cursor as
procedure test_ref_cursor(v1 OUT gc, v2 in varchar2) as
open v1 for v2;


The advantages of using the above are the following:

o Code Reusability

The same package procedure could be used for other
Java and non-Java applications.

o Load Balancing.

How to use REF CURSOR in a JDBC Program

To use REF CURSOR in a JDBC program, perform the following steps:

1. Use a JDBC callable statement to call a stored procedure:

CallableStatement ocstmt;
cstmt = conn.prepareCall("begin test_ref_cursor.test_ref_cursor(?,?); end;");

2. Register the REF CURSOR output parameter for the CallableStatement
as OracleTypes.CURSOR.

Note: OracleTypes class is present under oracle.jdbc.driver, so import

ocstmt.registerOutParameter (1, OracleTypes.CURSOR);

3. Execute the CallableStatement:

ocstmt.execute ();

4. Cast the CallableStatement to an OracleCallableStatement object to use
the getCursor() method. The getCursor() method is an Oracle extension to
the standard JDBC API, and returns the REF CURSOR into a ResultSet object.

Note: You must import oracle.jdbc.driver.OracleCallableStatement to
use OracleCallableStatement object.

OracleCallableStatement tstmt;
tstmt = (OracleCallableStatement)ocstmt;

5. Use the getCursor() method of OracleCallableStatement to retrieve the
REF CURSOR into a JDBC ResultSet variable.

ResultSet cursor;
cursor = tstmt.getCursor (1);

6. Use the resultset to retrieve the values.


For example, the program below retrieves data from the above mentioned
package procedure. The procedure takes an input argument which is the
select statement to execute. The package procedure opens the REF CUSROR
for the select statement passed. This REF CUSROR is retrieved in the
JDBC program into a ResultSet.

package test;
import java.sql.*;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
/* Could also use import oracle.jdbc.driver.*; instead of the last two above imports */

public class test {

public static void main(String[] args) {
test vTest = new test();


void call_stmt()
try {

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci8:@orcl8i", "scott", "tiger");
CallableStatement ocstmt;
ocstmt = conn.prepareCall
("begin test_ref_cursor.test_ref_cursor(?,?); end;");
ocstmt.setString(2,"select ename from emp");
ocstmt.registerOutParameter (1, OracleTypes.CURSOR);
ocstmt.execute ();
OracleCallableStatement tstmt;
tstmt = (OracleCallableStatement)ocstmt;
ResultSet cursor;
cursor = tstmt.getCursor (1);
// Use the cursor like a normal ResultSet
while ( ())
{System.out.println (cursor.getString (1));}
catch(Exception e)


  • 댓글 0

  • 로그인을 하시면 댓글을 등록할 수 있습니다.