This week's giveaway is in the Spring forum.
We're giving away four copies of Learn Spring Security (video course) and have Eugen Paraschiv on-line!
See this thread for details.
Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PL/SQL iterating cusror

 
Murali Narayanaswamy
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48363
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48363
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know I am afraid. Maybe somebody else can help?
 
Hemavathy Ramamoorthy Gopal
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Hemavathy RG",
Please check your private messages regarding an important administrative matter.
-Ben
 
Gene Hilpert
Ranch Hand
Posts: 51
Eclipse IDE Hibernate Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic