Complexe variable (Cursor).

[helmis]

Hello

the following procedure must receive a cursor in entry parameter and store it in a table named Person.

CREATE TABLE Person
(
  FIRST_NAME VARCHAR2(250),
  LAST_NAME  VARCHAR2(250),
  COMPANY    VARCHAR2(250)
)

type tcur is ref cursor;

 procedure so_p_save_Personne(grd_cur  in tcur)is

 v_first_name varchar2(250);
 v_last_name  varchar2(250);
 v_company  varchar2(250);
 begin
 open grd_cur;
 loop
 fetch grd_cur into a,b,c ;

 insert into Personne
   (first_name, last_name, company)
 values
   (v_first_name, v_last_name, v_company);

 exit when grd_cur%notfound;
  end loop;
 on exception rollback;
 commit;

 end;

.

i'm sending a oracledataset as parametre(complexe variable) from delphi.

The problem is how to declare the cursor "grd_cur" in Pl/sql developper which is received from a oracledataset.


if it is not clear thank you let me know

Thanks

 

[Marco Kalter]

Is this a PL/SQL Developer question or a Direct Oracle Access question? Can you explain in some more detail what the problem is?

 

[Andrey Maximenko. Ukraine]

into Personne (first_name, last_name, company)
select v_first_name, v_last_name, v_company
from

 

[helmis]

Thanks.

excuse my bad english

I use Doa & develop PLSQL.

the question is
when I have a cursor as variable in Delphi and that I must send the cursor as parameter to a procedure oracle.
what should I declare in the stored procedure as a parameter input to receive the delphi cursor.

if it a cursor in oracle too, can you give me an exemple of code ?

 

[helmis]

i find it in the help of DOA

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.