How to reference PL/SQL record type from another package-CodePudding

I have a package that declares a record type:

create or replace package schema_1.package_a as
  type r_user_type is record (
    name  varchar2(100),
    email varchar2(100)
  );
end;

How do I reference this record type from a package in a different schema in the same database? I have provided correct grants so that schema_2.package_b has EXECUTE privs to schema_1.package_a.

I have tried the following ways, and get a PLS-00206: %TYPE must be applied to a variable, column... error each time.

create or replace package body schema_2.package_b as
  procedure display_user_info
  is
    -- attempt 1
    r_user_info  schema_1.package_a.r_user_type%type;

    -- attempt 2
    r_user_info  schema_1.package_a.r_user_type%rowtype;

    -- attempt 3
    r_user_info_type  schema_1.package_a.r_user_type%type;
    r_user_info       r_user_info_type%type;
  
  begin
    select *
      into r_user_info
      from table(table_expression...);
  end;
end;

CodePudding user response：

%TYPE and %ROWTYPE are only used on a data object like a table or column or cursor to abstract it into a type. r_user_type is not a table/column, but a TYPE. So, when you reference r_user_type, you are already directly referring to a TYPE, and therefore it doesn't need the %TYPE or %ROWTYPE modifiers. Just remove them and you're good.

CodePudding user response：

You don't need %TYPE or %ROWTYPE, just use the type name:

create or replace package body schema_2.package_b as
  procedure display_user_info
  is
    r_user_info  schema_1.package_a.r_user_type;
  begin
    select *
    into   r_user_info
    from   table(table_expression...);
  end;
end;
/

However, you also probably need a WHERE filter on the SELECT statement (or some other form of limiting rows such as FETCH FIRST ROW ONLY) otherwise, if the table expression contains more than one element then you will get an exception.