Multiple Column PL/SQL Tables as Parameters

[Philae_infor]

Is it possible to use pl/sql procedures or functions using multiple column pl/sql tables as parameters with different data-types ?

example: pl/sql table with empno,hire_date and salary

 

[Marco Kalter]

No, this is not possible. Only PL/SQL Tables of scalar data types are supported. The only way to call such a stored procedure is by converting between several PL/SQL Tables of a scalar data type and a single PL/SQL Table of a record data type in a PL/SQL Block. This PL/SQL Block now only uses bind variables with PL/SQL Tables that can be used by Direct Oracle Access.

If we have a procedure Upd in package Employee that takes a PL/SQL Table of emp%rowtype records (defined by the t_emptable type in this package), then the PL/SQL Block would look something like this:

declare
  emptable employee.t_emptable;
  i binary_integer := 1;
begin
  loop
    emptable(i).empno := :empno(i);
    emptable(i).hiredate := :hiredate(i);
    emptable(i).sal := :sal(i);
    i := i + 1;
  end loop;
exception
  when no_data_found then Employee.Upd(emptable);
end;

This code loops through the 3 scalar PL/SQL Tables and copies the values to the PL/SQL table of records, until we get a no_data_found exception. At this point we have reached the end of the table, and we can call Employee.Upd with the PL/SQL Table of records as parameter.

We can't use PL/SQL Table functions like First, Next, Count, and so on, because these functions cannot operate on bind variables. That