Devart Forums

Posted: **Mon 28 Feb 2011 14:20**

Hello,
I'm working with a Delphi 7 project and I need to read data from a sql data type

TYPE tab_of_data as table of rec_of_data;
TYPE rec_of_data as object (id1 number, id2  number);

I've to submit to a stored procs a nested table containing some records, but I don't know what's the correct code to fill at run time the TOraNestTable object.

In a previous simpler version (where tab_of_data was a tab of integer) I've used

Code: Select all

pb := TOraNestTable.Create(nil);
pb.CreateObject(OraSession.OCISvcCtx, 'TAB_OF_DATA');
OraSQL.Params.ParamByName('tab_object').AsTable := pb;
OraSQL.ParamByName('tab_object').AsTable.ItemAsInteger[j] := value;

Suggestions?

Posted: **Tue 01 Mar 2011 09:10**

Hello,

To work with Nested tables you can use the following code:

Code: Select all

CREATE TYPE rec_of_data as object (id1 number, id2  number);

CREATE TYPE tab_of_data as table of rec_of_data; 

CREATE TABLE TABLE_OBJ
(
f_id NUMBER,
f_obj tab_of_data
)
NESTED TABLE f_obj STORE AS f_obj_tab;

Code: Select all

var
  OraQuery: TOraQuery;
  OraNestedTable: TOraNestedTable;
  i: integer;
begin
  OraQuery:= TOraQuery.Create(nil);
  OraNestedTable:= TOraNestedTable.Create(nil);

  OraQuery.Session := OraSession1;
  OraQuery.SQL.Text:='SELECT * FROM TABLE_OBJ';
  OraQuery.KeyFields := 'F_ID';
  OraQuery.ObjectView:= true;
  OraQuery.Open;

  OraNestedTable.DataSetField:=TDataSetField(OraQuery.FieldByName('F_OBJ'));
  OraQuery.Append;
  OraQuery.FieldByName('F_ID').AsInteger:= 1;


  for i:= 1 to 10 do
  begin
    OraNestedTable.Append;
    OraNestedTable.FieldByName('ID1').AsInteger := i;
    OraNestedTable.FieldByName('ID2').AsInteger := 1+i;
    OraNestedTable.Post
  end;
  OraQuery.Post;

Posted: **Tue 01 Mar 2011 11:03**

There is a way to work with TORASQL in place of TOraQuery?
I need that kind of object to exec the stored procedure (and retrieve a dataset from it) and TOraSQL doesn't have Field property.

Posted: **Tue 01 Mar 2011 14:31**

This is a sample to show working with nested tables as parameters of stored procedures (the types and the table are from the previous sample):

Code: Select all

CREATE OR REPLACE PROCEDURE sp_get_obj(ID IN NUMBER, tab OUT tab_of_data)
AS
BEGIN
 SELECT F_OBJ 
 INTO tab 
  FROM table_obj
  WHERE f_id = ID;
END;

CREATE OR REPLACE PROCEDURE sp_set_obj(ID IN NUMBER, tab IN tab_of_data)
AS
BEGIN
 INSERT INTO table_obj VALUES(ID, tab);
END;

Code: Select all

procedure TForm1.get_;
var
  OraNestedTable: TOraNestedTable;
  i: integer;
begin
  OraStoredProc1.ParamByName('TAB').AsTable.CreateObject(OraSession1.OCISvcCtx,'tab_of_data');
  OraStoredProc1.ParamByName('ID').AsInteger := 1;
  OraStoredProc1.Execute;
  OraNestedTable:= TOraNestedTable.Create(nil);
  OraNestedTable.Table := OraStoredProc1.ParamByName('TAB').AsTable;

  OraNestedTable.Open;
  while not OraNestedTable.Eof do
  begin
    for i:=0 to OraNestedTable.FieldCount -1 do
      ShowMessage(OraNestedTable.FieldByName('id1').AsString);
      ShowMessage(OraNestedTable.FieldByName('id2').AsString);
    OraNestedTable.next;
  end;

end;

procedure TForm1.set_;
var
  OraNestedTable: TOraNestedTable;
  i: integer;
begin
  OraNestedTable:= TOraNestedTable.Create(nil);
  OraNestedTAble.Table :=  TOraNestTable.Create(TOraType.create(OraSession1.OCISvcCtx,'tab_of_data'));
  OraNestedTable.Open;
  for i:= 1 to 10 do
  begin
    OraNestedTable.Append;
    OraNestedTable.FieldByName('ID1').AsInteger := i;
    OraNestedTable.FieldByName('ID2').AsInteger := 1+i;
    OraNestedTable.Post
  end;
  OraStoredProc2.ParamByName('ID').AsInteger :=1;
  OraStoredProc2.ParamByName('TAB').AsTable:= OraNestedTAble.Table;
  OraStoredProc2.Execute;
end;

Posted: **Tue 01 Mar 2011 15:44**

Works great! Thanks!

Posted: **Tue 01 Mar 2011 15:53**

Hello,

It is good to see that this problem was solved. If any other questions come up, please contact us.

Devart Forums

OraNestedTable and sql data type

OraNestedTable and sql data type