| Returing some thing like a "result set" from a Stored Procedure [message #372470] |
Wed, 14 February 2001 08:14  |
WHale
Messages: 2
Registered: February 2001
|
Junior Member |
|
|
Hello,
I am currently porting some SQL server stored procs to Oracle 8i.
I am trying to get a Stored procedure to return many rows of data, ie like a normal select statement.
The sql server stored proc looks like: ( that is pass in a variable then use that variable to run a select
statement, but unlike oracle a return object is not
specified )
******************
CREATE PROCEDURE sp_sel_viewtablelist
@type int
AS
SELECT * FROM tblViewTables
WHERE VT_VI_Code = @type
ORDER BY VT_Sort;
******************
I want to call this Stored Proc from a client with Java code as follows (note: the Calling code cant change because needs to work with
SQL server and Oracle)
*******************
strCall = "{call sp_sel_viewtablelist(?)}";
Connection jdbcConn = ....
CallableStatement objSP = jdbcConn.prepareCall(strCall);
objSP.setInt(1, viewcode);
ResultSet rs = objSP.executeQuery();
while (rs.next())
//now loop through
***********************
I dont mind coding the Oracle Stored Proc in PLSQL or in Java.
Any help would be appreciated.
Thanks in advance
Nick
|
|
|
|
| Re: Returing some thing like a "result set" from a Stored Procedure [message #372489 is a reply to message #372470] |
Thu, 15 February 2001 03:45  |
amarpatgiri
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
How about something like this - Please read the notes that follow :-)
---------------- start of func
CREATE OR REPLACE FUNCTION GetResponseInstance
(mctInstanceID IN NUMBER)
RETURN CLOBType_TAB IS
i BINARY_INTEGER := 0;
CLOB_TAB CLOBType_TAB;
CURSOR C1 IS
SELECT datagram
FROM responseInstance_Object
WHERE ctInstanceID = mctInstanceID;
--x number; needed for testing purposes
BEGIN
--dbms_output.put_line(idTAB.last);
CLOB_TAB := CLOBTYPE_TAB();
CLOB_TAB.EXTEND;
OPEN C1;
LOOP
i := i + 1;
FETCH C1 INTO CLOB_TAB(i);
EXIT WHEN C1%NOTFOUND;
CLOB_TAB.EXTEND;
END LOOP;
CLOSE C1;
RETURN CLOB_TAB;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END GetResponseInstance;
---------------- end of func
Note:
definition of CLOBType_TAB is:
CREATE OR REPLACE TYPE CLOBType_TAB AS TABLE OF CLOB;
You can create a PL/SQL table of any Object Type to reflect your resultset.
Hope this helps.
-amar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
