aspose file tools*
The moose likes Oracle/OAS and the fly likes PL/SQL iterating cusror Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "PL/SQL iterating cusror " Watch "PL/SQL iterating cusror " New topic
Author

PL/SQL iterating cusror

Murali Narayanaswamy
Greenhorn

Joined: May 29, 2007
Posts: 24
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
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38363
    
  23
Don't know any PL/SQL, but it looks as if you are returning a ddtsid_cursor in the first function and requiring a psirt_ddts_cursor in the procedure. Please check carefully.
Murali Narayanaswamy
Greenhorn

Joined: May 29, 2007
Posts: 24
Yes you are right, I need to get ddts_cursor from the first function.
The first function run in a different DB and the second procedure runs on different DB.

I require something similar to

Cursor objCursor = call function which return cursor

Iterate objCursor and perform some updates

Exits

-Thanks
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38363
    
  23
Don't know I am afraid. Maybe somebody else can help?
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi,

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.


Regards,
Hema_RG
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

"Hemavathy RG",
Please check your private messages regarding an important administrative matter.
-Ben


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Gene Hilpert
Ranch Hand

Joined: May 22, 2002
Posts: 51

Hemavathy RG is right on where to look.
There is nothing in the code that will tell the instance where to find the function. So it will look in the current instance and not find the function.

Look at database links.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PL/SQL iterating cusror