From: | Charles Christiansen <charleschr(at)yahoo(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Returning multiple cursors/resultsets from PostgreSQL procedure |
Date: | 2003-04-28 12:34:55 |
Message-ID: | 20030428123455.74198.qmail@web21209.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
Please forgive me if this has been answered a million
times before - I took a look around the FAQs and
postings but didn't see quite the same question.
I have an Oracle DB that I'd like to try converting to
PostgreSQL. I have a J2EE application which calls
procedures in the Oracle database using
CallableStatements. Some of my procedures use ref
cursors as out parameters, and some have multiple ref
cursor out parameters. Here's an example procedure (a
bad but simple example):
CREATE OR REPLACE PACKAGE FOO
AS
TYPE FooCursor IS REF CURSOR;
PROCEDURE SELECT_FOO (
foo_id IN VARCHAR2,
foo_cursor1 OUT FooCursor,
foo_cursor2 OUT FooCursor);
END FOO;
CREATE OR REPLACE PACKAGE BODY FOO
AS
PROCEDURE SELECT_FOO (
foo_id IN VARCHAR2,
foo_cursor1 OUT FooCursor,
foo_cursor2 OUT FooCursor)
AS
BEGIN
OPEN foo_cursor1 FOR
SELECT * FROM foo1;
OPEN foo_cursor2 FOR
SELECT * FROM foo2;
END SELECT_FOO;
END FOO;
And in the J2EE app code, I have something like this:
cstmt = conn.prepareCall("{ call FOO.SELECT_FOO
(?,?,?) }");
cstmt.setString(1,id);
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
cstmt.registerOutParameter(3,OracleTypes.CURSOR);
cstmt.execute();
rs1 = (ResultSet)cstmt.getObject(2);
// do rs stuff & close
rs2 = (ResultSet)cstmt.getObject(3);
// do rs stuff & close
My question is: Using Postgre pgPL/SQL and the
PostgreSQL JDBC driver, is it possible to do this?
Thanks for any help you can provide.
Charles.
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Elby Vaz | 2003-04-28 13:07:32 | [ how to unsubscribe me? ] |
Previous Message | Aaron Payne | 2003-04-28 11:52:08 | Re: finding records not listed in a column, Postgresql |