cursor parameter returning record

[Pieter]

I'm using a package that has a cursor as an in/out param. The cursor is declared to return a record structure. In one of the other forums I read that the OCI doesn't support the usage of records. However does this mean that my setup won't work in combination with doa, or is there some way of fixing it. The big difference being that I get a record structure back instead of needing to pass one.
One thing I don't understand is that our java team (using jdbc) is capable of retrieving the information from the cursor. But the TOracleQuery that I assign to the variable doesn't contain anything. Is this because the component tries to get the field information, whereas the jdbc connection doesn't?

 

[jimpoe]

I just did this using a TOracleDataSet. I get back a cursor with row columns mapped to the record structure. Is this what you expected?

------------------
Jim Poe

 

[Pieter]

More details on the problem.
In my case the record type is defined in a package. For example:

create or replace package ArticlePkg as

type
tArticle is record
( ID Articles.ID%type
, CODE Articles.CODE%type
, NAME Articles.NAME%type
);

type
cArticles is ref cursor return tArticle;

procedure FindArticleCode
( ARTICLE_CR in out cArticle
, CODE in varchar
);

end ArticlePkg;

In Delphi I use a TOracleQuery with the SQL statement:
begin
ArticlePkg.FindArticleCodeARTICLES, :CODE);
end;

I set the articles variable to a run-time created TOracleQuery and execute the statement. The execution seams to go alright, but when I want to get information from the query assigned to the variable, it doesn't contain anything.
I've tried using a TOracleDataset as suggested but I can't get it to work. Also the TOracleDataset uses a TOracleQuery internally, so if it doesn't work with a query, I don't see how it could work with a dataset.

 

[AndreyB]

This is what help says:
"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.opencursorp_empcursor, _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.