FAQ: Calling stored procedures with record type parameters

[faq]

I have a function with a record type parameter, but I can't figure out how to call it. Is this possible?

 

[support]

This is not directly possible. Direct Oracle Access does not have record type variables, because Oracle does not publish information about this feature. One work around 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 define 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;