DMBS_SQL.to_refcursor equivalent in Oracle 10g

Question

I have a coworker who came across DBMS_SQL.to_refcursor which would be a great solution for him to pass back the refcursor he needs, however we are running Oracle 10g and this feature is only available in 11g.

Is there an easy equivalent to this in Oracle 10g?

We have developed an alternate way of coding our solution but it would be easier to use the bind variables in the DBMS_SQL but we don't want this package to become overly difficult to manage and such since it will be passed on to other programmers to maintain.

Answer 1

James, I am in the same boat. What was your alternative solution?

Answer 2

This link lists the various dynamic SQL alternatives available, including the DBMS_SQL.to_refcursor. I mostly tend to use the OPEN ... FOR notation:

L_CURSOR SYS_REFCURSOR;
L_QUERY  VARCHAR2(5000) DEFAULT '...'

BEGIN

   FOR I IN 0 .. (TRUNC(LENGTH(L_QUERY) / 255)) LOOP
     DBMS_OUTPUT.PUT_LINE(SUBSTR(L_QUERY, I * 255 + 1, 255));
   END LOOP;

   OPEN L_CURSOR FOR L_QUERY;
   RETURN L_CURSOR;

END;

Update:

unfortunately in my case this will not work because I am binding varying number of variables. That's the reason I used DBMS_SQL package to bind variables programatically not with using syntax.

I suggest using context variables in that case - they've been supported since 9i.