Our database layer is accessed exclusively via an API with stored procedure calls, and overwhelmingly REFCURSOR objects are returned to the caller with sets of data (API layer accessed via webservices/Java). Oracle 10.2. I'm porting our code from DB2 and SQL Server to Oracle.
In general, what's he most efficient way to return a dummy cursor object when the result sets are null? In this very simple case, with our business logic, it's quite reasonable for there to be no rows returned for this result set. The caller then goes and does <something else>. I'd prefer not to do a count just to see if there are rows and THEN have the same query run when the cursor is returned, that seems incredibly inefficient to me. Please no comments on packages vs stored procs, I do understand the benefit.
CREATE OR REPLACE PROCEDURE GETATTRIBS
(
myId IN NUMBER DEFAULT NULL ,
cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cv_1 FOR
SELECT attribID,
attributename,
attribValue,
l_type,
required,
userdefined,
vc_notes,
metadata,
company,
objectId,
className
FROM attrib
WHERE myId= <some passed in var>;
END;
As of now, this is what I'm dong, and I loathe, in effect, running this query twice.
CREATE OR REPLACE PROCEDURE GETATTRIBS
(
myId IN NUMBER DEFAULT NULL ,
cv_1 IN OUT SYS_REFCURSOR
)
AS
rowCount NUMBER;
BEGIN
SELECT count (*) INTO rowCount
FROM attrib
WHERE myId= <some passed in var>;
BEGIN
IF rowCount = 0 THEN
OPEN cv_1 FOR
SELECT * FROM DUAL WHERE 1=0;
ELSE
OPEN cv_1 FOR
SELECT attribID,
attributename,
attribValue,
l_type,
required,
userdefined,
vc_notes,
metadata,
company,
objectId,
className
FROM attrib
WHERE myId= <some passed in var>;
END IF;
END;
Edited by: kpw on Nov 7, 2008 2:18 PM
Edited by: kpw on Nov 7, 2008 2:19 PM