stored-proceduresplsqlrowtype

How to call a procedure with a rowtype literal as parameter in PL/SQL?

Lets say I have a table and a procedure that accepts one argument of the tables rowtype:

CREATE TABLE t (a NUMBER, b NUMBER);

CREATE PROCEDURE p (x t%ROWTYPE) IS
BEGIN
  NULL;
END;

Can I call that procedure using a rowtype literal, that is without explicitly creating a rowtype variable (or at least not explicitly listing and assigning every field of it)? The two following approaches both generate the below error:

p(1, 2);

p((1, 2));

PLS-00306: wrong number or types of arguments in call to 'P'

Solution

  • You could also construct the record from a cursor loop:

    for r in (
    select 1, 2 from dual
)
loop
    p(r);
end loop;

    Unfortunately PL/SQL records are just simple structures and don't come with constructors as object types do. (I wish they did.)