da-soft - Data Access Technologies

Additional

dbCAP

AnyDAC

ThinDAC

NCOCI8

Topic: VArray and Procedure

Forum List » NCOCI8 » General English

Leonid
User

Junior Boarder

Posts: 12

Karma: 0

VArray and Procedure

Posted: 2000/08/01 15:40

Hello

I need a help to build a query on server part based on some number (uknown number) of criteria which is supplied from client part.

For example: user enters some filter criteria, client program sends it to server, where query based on that criteria is build and returns the result to client.

I decided to use the following procedure:
ServerPartProcedure (FIRST IN TFIRST, SECOND IN TSECOND, ret_cur OUT TRet_Cur);
type Tret_cur is REF CURSOR;
type TFIRST is VARRAY(20) of varchar2(100);
type TSECOND is VARRAY(20) of number;

Then I create a TOCIStoredProc... And got the followin error:NOE7/VAR - Bad or undefined variable [:FIRST] - type is 0.
So, I think it cannot understand VArray parameters.

What advice would you give me in this situation ?

Thank you,
Leonid Asanov

Please, send a copy to alv@pskov.teia.org

Diman

Admin

Posts: 1495

Karma: 19

RE: VArray and Procedure

Posted: 2000/08/01 18:56

Hi Leonid !

NCOCI8 does not support VARRAY data type. So, you should use PL/SQL TABLE types. For example:

create or replace package TestPackage is
......
type Number_Table is table of number index by binary_integer;
......
end;

Leonid
User

Junior Boarder

Posts: 12

Karma: 0

RE: VArray and Procedure

Posted: 2000/08/02 14:17

Thank you for assistance.

But I still having troubles. Ok, I changed the type of parameters to "table of.." But TOCIStorecProc still shows me the same error.

Could you give some more hints, or better I really appreciate example of using Collections with TOCIStorecProc.
I don't really understand how I should supply parameters of those types.

Thank you,
Leonid

Diman

Admin

Posts: 1495

Karma: 19

RE: VArray and Procedure

Posted: 2000/08/04 09:30

Hi Leonid !

NCOCI8 does not support VARRAY, collection and other objected data types. As i sayd, you should use PL/SQL tables. This is a example, that reverse array (PL/SQL table) of strings.

create or replace package test_tab is
type TOCIVC2Tbl is table of varchar2(50) index by binary_integer;
procedure TansformArray(ATable in out TOCIVC2Tbl);
end;
/
create or replace package body test_tab is
procedure TansformArray(ATable in out TOCIVC2Tbl) is
begin
for i in ATable.First .. ATable.Last loop
ATable(ATable.First + (ATable.Last - i)) := ATable(i);
end loop;
end;
end;
/

TForm1 = class(TForm)
OCIDatabase1: TOCIDatabase;
OCIStoredProc1: TOCIStoredProc;
Memo1: TMemo;
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
begin
OCIStoredProc1.Params[0].ArrayLen := Memo1.Lines.Count;
for i := 0 to Memo1.Lines.Count - 1 do
OCIStoredProc1.Params[0].AsStrings[i] := Memo1.Lines[i];
OCIStoredProc1.ExecProc;
Memo1.Lines.Clear;
for i := 0 to OCIStoredProc1.Params[0].ArrayLen - 1 do
Memo1.Lines.Add(OCIStoredProc1.Params[0].AsStrings[i]);
end;

Leonid
User

Junior Boarder

Posts: 12

Karma: 0

RE: VArray and Procedure

Posted: 2000/08/04 14:33

I found the demos at last. It helped me with the most of my troubles.
But I still experience the problem with the parameter of [PL/SQL table of number] type.
That method like you wrote
Params[0].AsFloat[0]:=3;
doen't work.
Sure I can create an collection object and assign it (as demo program does). But I'd like to understand bult-in logic of OCIStorecProc of working with PL/SQL tables.
Now I feel I don't undestand it.

Could you just write me a line (or a couple of lines) how to assign array of the float values to OCIStoredProc.Param[0] ?
Params[0].AsType[0]:=something; doesn't work...

Thank you !
Leonid

Leonid
User

Junior Boarder

Posts: 12

Karma: 0

RE: VArray and Procedure

Posted: 2000/08/04 15:29

Beg pardon,

I noticed the difference between AsFloat and AsFloats..

Excuse me.

Diman

Admin

Posts: 1495

Karma: 19

RE: VArray and Procedure

Posted: 2000/08/04 19:44

You welcome in any time and with any questions !
Yes, you are right. For work with PL/SQL tables you should use not AsType but AsTypes[AIndex] property. Really AsType is equal to AsTypes[0].

Forum List » NCOCI8 » General English