posted 15 years ago
I have PL/SQL function which returns a cursor.
CREATE OR REPLACE FUNCTION Get_Ddtsids_For_Branches(v_release_branch_name IN VARCHAR2)
RETURN sys_refcursor
AS
TYPE ddtsid_cursor IS REF CURSOR;
id_cursor ddtsid_cursor;
BEGIN
OPEN id_cursor FOR
SELECT DISTINCT spd.PSIRT_DDTS_ID PSIRT_DDTS
FROM SHR_PSIRT_INCIDENT spi,
SHR_PSIRT_DDTS spd,
SHR_PSIRT_DDTS_RELEASES spdr,
shr_branch_name sbn,
shr_release_number srn
WHERE sbn.BRANCH_NAME IN ( v_release_branch_name )
AND sbn.BRANCH_NAME_ID = srn.BRANCH_NAME_ID
AND spdr.RELEASE_NUMBER_ID = srn.RELEASE_NUMBER_ID
AND spi.PSIRT_INCIDENT_ID = spdr.PSIRT_INCIDENT_ID
AND spi.PSIRT_INCIDENT_ID = spd.PSIRT_INCIDENT_ID
AND spd.PSIRT_DDTS_ID = spdr.PSIRT_DDTS_ID
AND spi.ADM_FLAG = 'V'
AND spdr.ADM_FLAG = 'V' AND spd.ADM_FLAG = 'V'
AND sbn.ADM_FLAG = 'V';
RETURN id_cursor;
END;
/
I have another procedure in a different DB. In that procedure I need to call the above function, get the cusror and iterate it to perform biz logic.
TYPE psirt_ddts_cursor IS REF CURSOR;
BEGIN
psirt_ddts_cursor := psirt.Get_Ddtsids_For_Branches(v_release_branch_name);
FOR X IN psirt_ddts_cursor
LOOP
-- perform biz logic
END LOOP;
When I compile the above procedure, I get compilation error like left hand side assignment is not possible.
My requirement is how to get cursor from a different function and how to iterate it.
Thanks much
Murali