Wednesday, October 25, 2006

Returning data from anonymous PL/SQL block

The example here demonstrates the use of an anonymous PL/SQL block to return data to a calling Java program. It also shows how to use nested tables in PL/SQL, and the use of auto-generated column values. This requires Java 5 and Oracle 10g Release 2. In order to use the example, on the database
  1. Create a trigger on the database table that inserts a value into the new row, or use the following piece of code before the insert statement and insert the seqval variable in the id column.
    select req_seq.nextval into seqval from dual;
    Here req_seq is a sequence holding the req_id sequence.
  2. Create two new types as shown below
    create or replace TYPE RQ_ROW AS TABLE OF VARCHAR(500);
    create or replace TYPE RQ_LIST AS TABLE OF RQ_ROW;
    create or replace TYPE RESULTS_LIST AS TABLE OF RESULT_LIST;
    create or replace TYPE RESULT_LIST AS VARRAY OF NUMBER;

    RQ_ROW represents a row in the table. RQ_LIST represents a set of rows. RESULT_LIST is a two element list where the first element represents the index and the second element is the autogenerated column value. This was used since nested tables do not guarantee the order of elements. RESULTS_LIST is used to return the autogenerated keys back to Java.
Once the database is setup use the following PL/SQL block to insert rows into the table

DECLARE
rqResults RESULTS_LIST;
rqRow RQ_ROW;
rqList RQ_LIST;
p Number;
q Number;

BEGIN

rqList := ?;
rqresults := results_list();
q := rqList.COUNT;
FOR k IN 1..q LOOP
INSERT INTO rquisitions(RQ_CD, RQ_STATUS)
VALUES (rqList(k)(1), rqList(k)(2)) RETURNING rq_id INTO p;
rqResults.EXTEND;
rqResults(k) := RESULT_LIST(k,p);
END LOOP;

? := rqResults;

END;
rqList is the input parameter here. Note the ? := rqResults; This represents the output parameter. The following code snippet shows the implementation of this in a Java program.

private String sql = "DECLARE " +
"rqResults RESULTS_LIST; " +
"rqRow RQ_ROW; " +
"rqList RQ_LIST; " +
"p Number; " +
"q Number;" +
"BEGIN " +
"rqList := ?; " +
"rqresults := results_list(); " +
"q := rqList.COUNT; " +
"FOR k IN 1..q LOOP " +
"INSERT INTO rquisitions(RQ_CD, RQ_STATUS) VALUES " +
"(rqList(k)(1), rqList(k)(2)) RETURNING rq_id INTO p; " +
"rqResults.EXTEND; " +
"rqResults(k) := RESULT_LIST(k,p);" +
"END LOOP; " +
"? := rqResults;" +
"END;";


public void load() {
Connection connection = getConnection();
String RQ_COLS[] = { "req_id" };
try {
String data[][] = {{"ab", "cd"}, {"ef", "gh"}, {"ij", "kl"}};

ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("SCOTT.RQ_LIST", connection);
Array array = new ARRAY(descriptor, connection, data);
CallableStatement cstmt = null;


cstmt = connection.prepareCall(sql);
cstmt.setArray(1, array);

cstmt.registerOutParameter(2, Types.ARRAY, "SCOTT.RESULTS_LIST");

cstmt.executeUpdate();

// Print the result set.

Array arr = cstmt.getArray(2);

ResultSet rSet = arr.getResultSet();
long[] values = new long [6];
while (rSet.next()) {

Array arr1 = (Array)rSet.getArray(2);
ResultSet rrSet = arr1.getResultSet();

while(rrSet.next()) {

int index = rrSet.getInt(2);
if(rrSet.next()) {
long value = rrSet.getLong(2);
values[index] = value;
}
}
}

for(int i = 1; i < values.length; i ++) {
System.out.println(i + ", " + values[i]);
}


cstmt.close();
connection.close();

} catch (SQLException e) {
e.printStackTrace();
}

Note the use of a callable statement and that the out parameter is an array. It is actually an array of arrays. The java.sql.Array class provides a getResultSet() method that can be used to browse the array elements.

This code was tested on Java 5.0 with Oracle 10g Release 2.

References:

3 comments:

  1. I have a .sql file like following:

    execute xxx_package.xxx_procedure;

    declare
    begin

    end;
    /

    declare
    begin
    end;
    /

    The .sql script is generated dynamically. I need to run this script through JDBC.

    How should I do it? I try to read the whole script into a string and callablestatement.execute() it. It does not work. Any suggestions? Thanks.

    ReplyDelete
  2. Hi Abhi ,,, I did in the same way as u did,,, The below is the code snippet ,,,

    private static String sql = "BEGIN " + "insert into test values (id 4); "
    + "END;";

    public static void main(String[] args) throws ClassNotFoundException,
    SQLException, Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@//10.0.3.183:1521/test";
    Connection conn = DriverManager.getConnection(url, "test", "test");
    Statement stmt = conn.createStatement();
    CallableStatement cstmt = null;
    try {
    cstmt = conn.prepareCall(sql);
    cstmt.executeUpdate();

    } catch (Exception e) {
    e.printStackTrace();
    }
    stmt.close();
    }

    This is the exception :
    java.sql.SQLException: ORA-06550: line 1, column 35:
    PL/SQL: ORA-00917: missing comma
    ORA-06550: line 1, column 7:
    PL/SQL: SQL Statement ignored

    Could u plz help me out on this. mail me to srinivas.eruvanti@gssamerica.com
    Thank you ,,,

    ReplyDelete
  3. Hi Abhi,,,
    I got my mistake ,,, there is an error in PL/SQL block (insert stmt),,, its wrking nw ,,,
    Thank you very much for the useful information in the blog

    ReplyDelete

Popular Posts