How to select a record defined in a package in sql query

Question

to test the code: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=b02671a25f9d7949e0b55ca59084ecd1

If I define a record as an object, I can call it in a sql statement this way objectname(field1, field2) But If I define a record inside package as a record. I can't do that anymore.

create TYPE arguments_r IS  object
(
    q    integer,
    b    INTEGER
);

CREATE FUNCTION f (p IN arguments_r) RETURN INTEGER
IS
BEGIN
    RETURN 1;
END;
/
select arguments_r(1,1) from dual  -- not printed but exists nevertheless. THe following statement prove it.

select f(arguments_r(1,1)) from dual --print the expected result

---

CREATE PACKAGE pck
as

TYPE arguments_r IS  record
(
    q    integer,
    b    INTEGER
);

FUNCTION f (p IN pck.arguments_r) RETURN INTEGER;

end;

CREATE PACKAGE body pck 
as

FUNCTION f (p IN pck.arguments_r) RETURN INTEGER
is 
begin
 return 1;
end;

END;

select pck.arguments_r(1,1) from dual  -- ORA-06553: PLS-306: wrong number or types of arguments in call to 'ARGUMENTS_R'

Answer 1

[TL;DR] In general, you cannot.

A record is a PL/SQL ONLY data type and it CANNOT be used in SQL. If you want to use the data in SQL then you will need to put it into an SQL data type such as an Object.

There are ways of achieving what you want via PIPELINED functions (which implicitly convert a RECORD to an OBJECT so, technically you never use a PL/SQL data type in the SQL scope but it certainly looks like you do) but the implementation is convoluted and if I ever did a code review on code that tried to use records the way I show below then I would fail it in the review and tell the author to just use an SQL OBJECT.

---

Given the setup:

CREATE PACKAGE pck as
  TYPE args_r IS  record (
    q    integer,
    b    INTEGER
  );

  TYPE args_t IS TABLE OF args_r;
  
  FUNCTION f RETURN args_t PIPELINED;
  FUNCTION f2 RETURN args_t;

  FUNCTION create_arg(q INTEGER, b INTEGER) RETURN args_t PIPELINED;
  FUNCTION create_arg2(q INTEGER, b INTEGER) RETURN args_t;
  FUNCTION g(args IN args_r) RETURN INTEGER;
END;
/

CREATE PACKAGE BODY pck as
  FUNCTION f RETURN args_t PIPELINED
  IS 
  BEGIN
    PIPE ROW (args_r(1,1));
    PIPE ROW (args_r(2,2));
  END;

  FUNCTION f2 RETURN args_t
  IS 
  BEGIN
    RETURN args_t(args_r(1,1), args_r(2,2));
  END;

  FUNCTION create_arg(q INTEGER, b INTEGER) RETURN args_t PIPELINED
  IS
  BEGIN
    PIPE ROW (args_r(q, b));
  END;

  FUNCTION create_arg2(q INTEGER, b INTEGER) RETURN args_t
  IS
  BEGIN
    RETURN args_t(args_r(q, b));
  END;

  FUNCTION g(args IN args_r) RETURN INTEGER
  IS
  BEGIN
    RETURN args.q;
  END;
END;
/

From PL/SQL to SQL via a PIPELINED function:

If you want to pass PL/SQL records from a PL/SQL function into an SQL query then it will "work" (see the comment below for clarification of why) if you use a PIPELINED function then:

SELECT *
FROM   TABLE(pck.f());

Outputs:

Q	B
1	1
2	2

BUT that is not because you can use PL/SQL records in SQL it is because a PIPELINED function is intended to be used in the SQL scope so Oracle has implicitly created a duplicate OBJECT data type (and TABLE OF ... data type) and mapped the PL/SQL record to the SQL object and despite what the signature of the function says, it is not returning records and returning Objects instead.

From PL/SQL to SQL via a function returning a collection:

If you try to do exactly the same thing with a non-PIPELINED function:

SELECT *
FROM   TABLE(pck.f2());

Then you get the error:

ORA-00902: invalid datatype

Because no such implicit conversion has been applied and you CANNOT use PL/SQL record types in SQL.

From SQL to PL/SQL:

Going the other way, as per the question, and trying to create records in the SQL scope and pass them to a PL/SQL function then it CANNOT work because it is impossible to create PL/SQL records in the SQL scope.

The query:

SELECT pck.g(pck.args_r(1,1))
FROM   DUAL;

Fails with the error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'ARGS_R'

From PL/SQL to SQL back to PL/SQL via a PIPELINED function:

If you create the record in PL/SQL and return it via a PIPELINED function then it "works":

SELECT pck.g((SELECT VALUE(t) FROM TABLE(pck.create_arg(1,1)) t))
FROM   DUAL;

and outputs:

PCK.G((SELECTVALUE(T)FROMTABLE(PCK.CREATE_ARG(1,1))T))
1

From PL/SQL to SQL back to PL/SQL via a function returning a collection:

However, if you use a non-PIPELINED function:

SELECT pck.g((SELECT VALUE(t) FROM TABLE(pck.create_arg2(1,1)) t))
FROM   DUAL;

Then it raises the exception:

ORA-00902: invalid datatype

db<>fiddle here

Conclusion:

Just define an OBJECT data type; do not try to use convoluted methods to ram PL/SQL only data types into an SQL scope where they are not meant to be used.