SetComplexVariable for in cursor?

[NoSnipeLimit]

Currently I have a proc:

CREATE OR REPLACE PROCEDURE CDR.Test(C_CUR IN SYS_REFCURSOR) IS
   v_rowid test.text%TYPE;
   v_test  test.text%TYPE;
   v_test2 test.text2%TYPE;
   v_test3 test.num%TYPE;

BEGIN
--  LOOP
--    FETCH C_cur INTO v_rowid, v_test, v_test2, v_test3;
--      EXIT WHEN C_cur%NOTFOUND;
--  END LOOP;
  null;
END Test;
/

This will be expanded on later, however i believe this is the correct way to get an IN cursor so one can loop through the dataset.

In delphi, i'm now trying to populate this set as follow:

var
  lQuery : TOracleQuery;
  lQuery2 : TOracleQuery;
begin
  lQuery  := TOracleQuery.Create(nil);
  lQuery2 := TOracleQuery.Create(nil);
  try
    lQuery.Session := OracleSession1[0];
    lQuery2.Session := OracleSession1[0];
    lQuery2.sql.Text := 'SELECT D.rowid ROW_ID, D.* FROM CDR.ANTON_TEST D';
    lquery2.Execute;

    lQuery.SQL.Text := 'BEGIN execute test(:MyCurs); END;';
    lQuery.DeclareVariable('MyCurs', otCursor);
    lQuery.SetComplexVariable('MyCurs', lQuery2);
    lQuery.Execute;
  Finally
    FreeAndNil(lQuery);
  end;

Is this the correct way to pass a dataset to an oracle proc?

 

[Marco Kalter]

Yes, this is indeed correct. The User's Guide describes this as follows:

Using a cursor variable as a TOracleQuery
Because a cursor variable is equivalent to a TOracleQuery with a select statement, DOA implements the cursor variable type as a TOracleQuery. To use a cursor variable, you need at least two TOracleQuery components: one with a PL/SQL block to call the procedure that opens the cursor, and one for the cursor itself:

begin
  with Query1 do
  begin
    Clear;
    SQL.Add('begin');
    SQL.Add('  employee.opencursor(:p_empcursor, :p_order)');
    SQL.Add('end;');
    DeclareVariable('p_empcursor', otCursor);
    DeclareVariable('p_order', otString);
    SetComplexVariable('p_empcursor', CursorQuery);
    SetVariable('p_order', 'ename');
    Execute;
  end;
  with CursorQuery do
  begin
    Execute;
    while not Eof do
    begin
      Memo.Items.Add(Field('ename'));
      Next;
    end;
  end;
end;

The packaged procedure employee.opencursor might look like this:
type t_empcursor is ref cursor return emp%rowtype;

procedure getcursor(p_empcursor in out t_empcursor, p_order in varchar2) is
begin
  if p_order = 'ename' then
    open p_empcursor for select * from emp order by ename;
  elsif p_order = 'empno'
    open p_empcursor for select * from emp order by empno;
  else
    open p_empcursor for select * from emp;
  end if;
end;

In this example, Query1 calls the packaged function employee.opencursor to open a cursor to select all employees in a certain order. The CursorQuery is assigned to the p_empcursor variable. You need to use the SetComplexVariable method for this. Next, all rows are fetched and the employee names are displayed in a memo.

 

[NoSnipeLimit]

Using the cursor as an output parameter works perfectly, however I wish to use the cursor as an Input parameter.

 

[Marco Kalter]

No, that will not be possible. You will have to pass the SQL text instead of a cursor.

 

[NoSnipeLimit]

Ah ok, thank you.