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;
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.
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.
I request you to go through Definer and Invoker concepts in oracle, which will help you in accessing the procedures in database B eventhough it is created in database A. (That is acessing an object from another database). Regarding cursor problem no idea.