Retrieving data via "ref cursor" from a oracle stored
procedure/function
lazarus / 16-Databases

----------------------------------------------------------------------
Helios · 2016-12-18 12:24

Hi there,
I would like to use oracle stored procedure/funktion using a
Oracle type ref cursor.
I searched in this forum and, of cause, did it with google.
But nothing did exact match my conditions or answerd my questions.

I use Window XP, Lazarus 1.6.2 an Oracle XE 11g (11.2.0.2.0)

The stored function looks like this:

CREATE OR REPLACE PACKAGE types AS
  TYPE OracleCursor IS REF CURSOR;
END types;
/

CREATE OR REPLACE FUNCTION get_empno_func(maximum IN NUMBER)
RETURN types.OracleCursor
IS
  cur types.OracleCursor;

BEGIN
  OPEN cur FOR
    SELECT  *
    FROM    emp
    WHERE   empno < maximum;

  RETURN cur;

END get_empno_func;
/

From an Oracle sql command line I can call
   select get_empno_func(8000) from dual;
and will get my data (Oracle typical scott.emp table content;-).

Using Lazarus like this:
...
procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text:= 'select get_empno_func(8000) from dual';
  DBConnection.Connected:= True;
  SQLTransaction1.Active:= True;
  SQLQuery1.Open;
end;
...

will result into an "EDatabaseError" Exception
"Missing (compatible) underlying dataset, can not open"

Do I have to "cast" the returning value? Where (in the Lazarus/Free
Pascal source code) do I have
to do the casting. Is it somewhere in the TDataSet?
As you can see, I'm not a proffessional in Lazarus/Free Pascal
programming but I would like to
become one, because I love the Pascal language and the Lazarus IDE;-)

Using
  SQLQuery1.SQL.Text:= 'select * from emp';
will, of cause, work fine!

Java and the Oracle (Thin) client and also OraTcl supportes such kind
of "ref cursor" calls and it works fine.

I feel, it is just one little step, to get it to run with Lazarus.
So any hint is more than wellcome.

Kind regards

Arne

----------------------------------------------------------------------
Thaddy · 2016-12-18 12:37

The SQL is governed by the database driver. So yes, this should work.
There is no explicit FPC code involved in the query. FPC /Lazarus just
processes a  result set.
Without a simple example project, we will never be able to reproduce
the issue.
So plz add that.

----------------------------------------------------------------------
Helios · 2016-12-18 13:12

Hi Thaddy, thanks for the quick reply. Here is the example project.
This is the "running version", it reads all values from emp.
Please change the SQL Statement to
'select get_empno_func(8000) from dual'
then you get the error I'm struggeling with:-)

Kind regards

Arne