Assigning TOracleQuery to TOracleDataSet

[ixie]

Hi,

I have a problem that I am trying to solve. I have a Oracle Package call that returns REF CURSOR.

We are utilizing DeclareVariable and SetComplexVariable to assign ref cursor to TOracleQuery and that is fine.

Now I need to assign that OracleQuery to TOracleDataSet and don't know how to do it.

I read about TOracleDataSet.ExternalCursor and tought that that is the solution but if I just assign OracleDataSet.ExternalCursor := OracleQuery that holds function result of type REF CURSOR; and try to say OracleDataSet.Active := true; I am getting Invalid SQL statement

What is solution here?

Thanks

Irfan

 

[Marco Kalter]

For a TOracleDataSet you can simply call the procedure/function and use a cursor variable (type = otCursor) for the cursor parameter or return value. For example:

begin
  MyPackage.CursorProcedure(:par1, :par2, :cursor_variable);
end;

The TOracleDataSet will detect the cursor variable and will automatically fetch the result set.

See also the PkgAPpply demo project.

 

[ixie]

OK, but how to use this with package call that is a function that returns a result as REF CURSOR.

In oracle I have something like this:

FUNCTION GET_CUSTOMERS_BY_NAME(P_NAME varchar2) RETURNS SYS_REFCURSOR;

?

EDITED

No need to reply I have figured it out:

begin
:function_result := GET_CUSTOMERS_BY_NAMEP_NAME );
end;

declare function_result variable as cursor and open it...

It works !