Solved

How to implement Ref cursor on DB2

Posted on 2007-11-29
4
2,470 Views
Last Modified: 2008-09-17
Hi all

I need to translate an Oracle functions and Stores procedures that uses Ref Cursor datatypes variables, for example:

CREATE OR REPLACE FUNCTION    "EXISTE" (Selecc in Varchar2, Proced in Varchar2, Condic in Varchar2)
RETURN BOOLEAN
IS

Type Registro is Ref Cursor;
retorno   Registro;
p_valor1  int;
f_strsql  varchar2(4000);
p_existe  boolean;

Begin

p_valor1 := 0;
f_strsql := 'Select ' || Selecc || ' From ' || Proced || ' Where ' || Condic;
Open retorno for f_strsql;
Loop
  Fetch retorno into p_valor1;
  Exit when retorno%notfound;
End Loop;

If p_valor1 = 1 Then
  p_existe := True;
Else
  p_existe := False;
End If;

Return p_existe;

End EXISTE;
/

How i can do this on DB2?

Thanks a lot,
regards
0
Comment
Question by:alexalar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 5

Accepted Solution

by:
ocgstyles earned 250 total points
ID: 20379414
Hi alexalar,

I'll start by noting I'm not familiar with Oracle's REF CURSOR, but from a quick read, it looks like a means to , in DB2's terms, return a table.  In addition to scalar values, DB2 functions can return tables.

Unfortunately, you are going to run into problems with that converting that function into DB2 function.  DB2 does not allow you to declare cursors in user-defined functions.  You can, however, use a FOR loop in a function to iterate through a result set.  Only problem though, is that you are using dynamic SQL, and you'll need a cursor for that.

With this sample Oracle function, I don't think you will get a direct replacement using DB2.  DB2 can produce the a similar result by using a stored procedure.  But a solution would be dependent on how your application is using the results and whether or not you can alter that.

Keith
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 20385282
hi
if you can' t define a cursor in a db2 user define function, you can create a stored procedure that will run the cursor you need, and call that stored procedure from a user defined function that will recieve the parameters and return value exactly like the code you wrote
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question