| Returing some thing like a "result set" from a Stored Procedure [message #372471] |
Wed, 14 February 2001 08:15  |
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 #372476 is a reply to message #372471] |
Wed, 14 February 2001 12:06  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
This is usually done using a ref cursor. see below...
CREATE TABLE MY_TAB (
MY_TAB_PK NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(64) NOT NULL,
CREATE_DATE DATE NOT NULL);
INSERT INTO MY_TAB VALUES (991, 'Description 1', SYSDATE);
INSERT INTO MY_TAB VALUES (992, 'Description 2', SYSDATE - 1);
INSERT INTO MY_TAB VALUES (993, 'Description 3', SYSDATE - 2);
INSERT INTO MY_TAB VALUES (994, 'Description 4', SYSDATE - 3);
COMMIT;
/*
|| Package Spec!
*/
CREATE OR REPLACE PACKAGE my_pkg
AS
TYPE t_test_rec IS RECORD(
my_tab_pk MY_TAB.my_tab_pk%TYPE,
description MY_TAB.description%TYPE,
create_date MY_TAB.create_date%TYPE
);
TYPE t_test_cur IS REF CURSOR
RETURN t_test_rec;
FUNCTION my_query (v_test_cv IN OUT t_test_cur)
RETURN NUMBER;
END my_pkg;
/
/*
|| Package Body!
*/
CREATE OR REPLACE PACKAGE BODY my_pkg
AS
/*Select all Records from the Table*/
FUNCTION my_query (v_test_cv IN OUT t_test_cur)
RETURN NUMBER
AS
BEGIN -- just a silly if condition to demonstrate!
IF TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) < 30
THEN -- Return query 1 !
DBMS_OUTPUT.put_line ('------- ss < 30, query 1 results ---------');
OPEN v_test_cv FOR
SELECT my_tab_pk, description, create_date
FROM MY_TAB
ORDER BY 1 ASC; -- Sort Ascending!
ELSE -- Return query 2 !
DBMS_OUTPUT.put_line ('------- ss >= 30, query 2 results --------');
OPEN v_test_cv FOR
SELECT my_tab_pk, description, create_date
FROM MY_TAB
ORDER BY 1 DESC; -- Sort Descending!
END IF;
RETURN 0;
END my_query;
END my_pkg;
/
/*
|| Test the Package and Function to return multiple rows!
*/
set serveroutput on
DECLARE
retval NUMBER;
TYPE t_test_rec IS RECORD(
my_tab_pk MY_TAB.my_tab_pk%TYPE,
description MY_TAB.description%TYPE,
create_date MY_TAB.create_date%TYPE
);
c1rec t_test_rec;
v_test_cv my_pkg.t_test_cur -- Cursor Variable passed out of Function!
-- Defined as per ref cursor in Function!
BEGIN
retval := my_pkg.my_query (v_test_cv);
LOOP
FETCH v_test_cv INTO c1rec;
EXIT WHEN v_test_cv%NOTFOUND;
DBMS_OUTPUT.put_line (
c1rec.my_tab_pk ||
', ' ||
c1rec.description ||
', ' ||
c1rec.create_date
);
END LOOP;
END;
/
--========================== Second Example ============================
http://www.classicity.com/oracle/htdocs/forums/ClsyForumID125/10.html
create or replace package types as
type sqlcur is REF cursor;
end;
/
create or replace function test return types.sqlcur as
c1 types.sqlcur;
begin
open c1 for select loc from dept;
return c1;
end;
/
create or replace procedure calltest as
c1 types.sqlcur;
val varchar2(20);
begin
c1 := test;
loop
fetch c1 into val;
exit when c1%NOTFOUND;
dbms_output.put_line(val);
end loop;
end;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
