does doa support pl/sql record types

[Dilshad Marikar]

Hi

Does DOA support pl/sql record types, or collections of pl/sql record types as stored procedure parameters?

Thanks

 

[Marco Kalter]

PL/SQL Record types and PL/SQL Tables of Record types are not supported by Oracle Net, so they cannot be used directly in Direct Oracle Access.

Collection objects (varrays and nested tables) are supported through the TOracleObject class, and may provide a workaround.

Another workaround for PL/SQL Record types is to create a small PL/SQL block that has only input and/or output variables of a scalar data type, that are converted to/from a locally defined record variable that is passed to the procedure:

declare
    dr dept%rowtype;
  begin
    -- Copy from scalar variables if the record is an input parameter
    dr.deptno := :deptno;
    dr.dname := :dname;
    dr.loc := :loc;
    -- Call the procedure with the local record
    myproc(dr);
    -- Copy to scalar variables if the record is an output parameter
    :deptno := dr.deptno;
    :dname := dr.dname;
    :loc := dr.loc;
  end;