mORMot Open Source

yoanq

Member

Registered: 2013-01-03

Posts: 24

Problem with Oracle cursor over temporary table

Hi, ab.

I have PL/SQL block with a call to package method returning cursor over temporary table. When execute stmt.BoundCursor(1); Oracle return error associated to object not exists. What can I do? I suspect Oracle do commit after ExecutePrepared.

function TApplyRepository.PrintError (DBUser, DBPassword, DBDataBase, ErrorNumber: RawUTF8): TResponse;
var
  query: RawUTF8;
  stmt: ISQLDBStatement;
  cursor: ISQLDBRows;
  count: integer;
  RemoteProps: TSQLDBOracleConnectionProperties;
begin
  RemoteProps := TSQLDBOracleConnectionProperties.Create(DBDataBase, '', DBUser, DBPassword);
  query :=  'BEGIN STREAM_MONITOR.PRINT_TRANSACTION(P_CURSOR  => ?, LTXNID=> ?); END;';
  stmt := RemoteProps.NewThreadSafeStatementPrepared(query, false);
  stmt.BindCursor(1);
  stmt.BindTextS(2,ErrorNumber,paramIn);
  try
    stmt.ExecutePrepared;
    cursor := stmt.BoundCursor(1);
    Result.data := cursor.FetchAllAsJSON(true, @count);
    Result.count := count;
  finally
    cursor._Release;
    cursor := nil;
    stmt._Release;
    stmt := nil;
    RemoteProps.Destroy;
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,263

Website

Re: Problem with Oracle cursor over temporary table

Does it work for other kinds of cursors, e.g. not on temporary tables?

I do not use Oracle those days, so it is difficult for me to test it.

yoanq

Member

Registered: 2013-01-03

Posts: 24

Re: Problem with Oracle cursor over temporary table

Hi, ab.

Work perfect with others cursor. If I use StartTransaction and Commit after

  stmt.ExecutePrepared;
  cursor := stmt.BoundCursor(1);

work perfect. The problem is when execute PL/SQL block execute Commit by default.

Thank's ab.