Passing Record Type to Stored Procedure

[Tom Miller]

Hi,

We would like to use a record type in passing data to our "Insert" and "Update" stored procedures instead of a huge parameter list of each and every field.

Does ODAC support this?

Thanks.

[Plash]

TOraStoredProc component does not supports record types. But if you call a stored procedure using PL/SQL block in SQL, SQLInsert or SQLUpdate properties, you can use a record type.

[Tom Miller]

TOraStoredProc component does not supports record types. But if you call a stored procedure using PL/SQL block in SQL, SQLInsert or SQLUpdate properties, you can use a record type.

Which component? Does this allow for a return value(s). Any chance this is a feature that will be implemented in the near future?

Thanks.

[Plash]

You can use TOraQuery or TOraSQL component. For example:

Code: Select all

  OraQuery.SQL.Clear;
  OraQuery.SQL.Add('DECLARE');
  OraQuery.SQL.Add('  rec mypack.myrec;');
  OraQuery.SQL.Add('BEGIN');
  OraQuery.SQL.Add('  rec.a := :a;');
  OraQuery.SQL.Add('  rec.b := :b;');
  OraQuery.SQL.Add('  :Result := mypack.myproc(rec);');
  OraQuery.SQL.Add('END;');

  OraQuery.ParamByName('a').AsInteger := 1;
  OraQuery.ParamByName('b').AsString := 'zzz';
  OraQuery.Execute;
  r := OraQuery.ParamByName('Result').AsInteger;

TOraStoredProc component does not support record types because OCI does not support parameters of such types.