• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PL/SQL iterating cusror

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 79180
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Don't know I am afraid. Maybe somebody else can help?
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 13411
Firefox Browser VI Editor Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Hemavathy RG",
Please check your private messages regarding an important administrative matter.
-Ben
 
Ranch Hand
Posts: 51
Hibernate Eclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic