Table of record to "out" parameters of a stored procedure

[nguthans]

Hello Core Lab !!

I use your "Vers 5.70.1.33" components with Delphi 7.

I wrote a stored procédure who have a table of records to "out" parameters.
I must recover this table of record in my Delphi program.
But, i don't now when this is possible.

I am obliged of used a table of recording because the data are built in the stored procedure. These data have different types (varchar2, number...) and are recovered with cursor from another procedures provided by a company.

Here the package and procedure code :

Code: Select all

create or replace package PACK_MED_THERIAQUE_NICO is

  -- Author  : NGUTHANS
  -- Created : 06/07/2006 11:58:03
  -- Purpose : 
  
  -- Public type declarations
  TYPE GET_SUB_INFORec IS RECORD 
  (
    CODE_SUB NUMBER(6),
    TYPE_SUB VARCHAR(1),
    LIB_SUB VARCHAR(40)
  );
    
  TYPE GET_SUB_INFOCur IS REF CURSOR RETURN GET_SUB_INFORec;
  type T_SUB_INFO is TABLE OF GET_SUB_INFORec index by binary_integer;
  
  -- Public constant declarations
  -- constant  := ;

  -- Public variable declarations
  -- ;

  -- Public function and procedure declarations
  --function ( ) return ;

end PACK_MED_THERIAQUE_NICO;

Code: Select all

create or replace procedure PROC_TEST(
code_spe IN VARCHAR2,
tableau OUT PACK_MED_THERIAQUE_NICO.T_SUB_INFO)
is
begin

/* Déclaration */
declare

resultSPE PACK_THERIAQUE_API.GET_THE_SUB_SPECur;
ligneSPE PACK_THERIAQUE_API.GET_THE_SUB_SPERec;

resultSPEID PACK_THERIAQUE_API.GET_THE_SUB_IDCur;
ligneSPEID PACK_THERIAQUE_API.GET_THE_SUB_IDRec;



--TabEngSubInfos PACK_MED_THERIAQUE_NICO.T_SUB_INFO;

type_rech NUMBER(1);
cmpt NUMBER;

/*Corps de la procédure */
  begin
       type_rech := 3;
       PACK_THERIAQUE_API.GET_THE_SUB_SPE(resultSPE, code_spe, type_rech);
       cmpt := 0;
       LOOP
       FETCH resultSPE INTO ligneSPE;
       EXIT WHEN resultSPE%NOTFOUND;
       tableau(cmpt).CODE_SUB := ligneSPE.CODESUBST;
       tableau(cmpt).TYPE_SUB := ligneSPE.TYPSUBST;
       PACK_THERIAQUE_API.get_the_sub_id(resultSPEID, ligneSPE.CODESUBST, ligneSPE.TYPSUBST);
       FETCH resultSPEID INTO ligneSPEID;
       tableau(cmpt).LIB_SUB := ligneSPEID.SAC_NOM;
       cmpt := cmpt+1;
       END LOOP;
       
       
       
         
  end;
  
  
end PROC_TEST;

Here is my "False" Delphi code :

Code: Select all

procedure TForm1.BitBtn2Click(Sender: TObject);
begin

   MemoAtaFdm2.Clear;
   with OraStoredProc2 do
   begin
     //ParamByName('TABLEAU').AsTable.CreateObject(OraSession1.OCISvcCtx, 'TODACNestedTable');
     ParamByName('CODE_SPE').AsString := EntierFdm1.Text;
     OraNestedTable1.Table := ParamByName('TABLEAU').AsTable;
     OraNestedTable1.Open;
     Execute;
   end;




   {OraStoredProc2.ParamByName('code_spe').AsString := EntierFdm1.Text;
   //OraStoredProc2.ParamByName('type_rech').AsInteger := 3;
   with OraStoredProc2 do
   begin
     Execute;
     while not Eof do
     begin
      MemoAtaFdm2.lines.Add('-  ' + FieldByName('TYPSUBST').AsString + ' : ' + FieldByName('CODESUBST').AsString);
      Next;
     end;
   end;}
end;

Thanks for help and sorry for my bad english !!

[Plash]

ODAC doesn't have features to work with PL/SQL collections and records. You can use nested table to return data from stored procedure. For example:

Code: Select all

CREATE TYPE OBJ_SUB_INFO AS OBJECT
(
    CODE_SUB NUMBER(6),
    TYPE_SUB VARCHAR(1),
    LIB_SUB VARCHAR(40)
);

CREATE TYPE TAB_SUB_INFO AS TABLE OF OBJ_SUB_INFO;

CREATE OR REPLACE PROCEDURE PROC_TEST(code_spe IN VARCHAR2, tableau OUT TAB_SUB_INFO) 
IS
BEGIN
...
END;
/

Use the following Delphi code to access data returned from stored procedure

Code: Select all

var
  ObjType: TOraType;
begin
  OraStoredProc2.ParamByName('CODE_SPE').AsString := EntierFdm1.Text;

  ObjType := TOraType.Create(OraSession1.OCISvcCtx, 'TAB_SUB_INFO');
  try
    OraStoredProc2.ParamByName('TABLEAU').AsTable.ObjectType := ObjType;
  finally
    ObjType.Free;
  end;
  OraStoredProc2.Execute;
  OraNestedTable1.Table := OraStoredProc2.ParamByName('TABLEAU').AsTable;
  OraNestedTable1.Open;
end;