Oracle RECORDS and table of records type-CodePudding

I have a record type defined with two fields.

TYPE record is RECORD (
 a NUMBER,
  b VARCHAR2(20)
);

and I have defined a type which is a table of above record type mentioned.

TYPE recordTable is TABLE of record;

I declare a variable in the script

recordVar recordTable;

In plsql script I successfully populate variable recordVar declared above with some values.

Post this I want to join recordVar with one of the existing table.

How do I achieve this??

If I do a select * from recordVar. I get an error saying table or view doesn't exist.

CodePudding user response：

You cannot, a RECORD is a PL/SQL data type and cannot be used in SQL statements.

DECLARE
  TYPE recordType is RECORD ( a NUMBER, b VARCHAR2(20) );
  TYPE recordTable is TABLE of recordType;
  
  v_recs recordTable;
BEGIN
  v_recs.EXTEND(2);
  v_recs(1).a := 23;
  v_recs(1).b := 'ABC';
  v_recs(2).a := 42;
  v_recs(2).b := 'DEF';
  
  FOR i IN (SELECT * FROM TABLE(v_recs))
  LOOP
    DBMS_OUTPUT.PUT_LINE(i.a || ', ' || i.b);
  END LOOP;
END;
/

Outputs the errors:

ORA-06550: line 13, column 33:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 13, column 27:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 13, column 13:
PL/SQL: SQL Statement ignored

If you want to do something similar, then declare it as an OBJECT in the SQL scope:

CREATE TYPE objectType is OBJECT( a NUMBER, b VARCHAR2(20) );
CREATE TYPE objectTable is TABLE of objectType;

Then you can use it in PL/SQL and SQL via a table collection expression:

DECLARE
  v_objs objectTable := objectTable();
BEGIN
  v_objs.EXTEND(2);
  v_objs(1) := objectType(23, 'ABC');
  v_objs(2) := objectType(42, 'DEF');

  FOR i IN (SELECT * FROM TABLE(v_objs))
  LOOP
    DBMS_OUTPUT.PUT_LINE(i.a || ', ' || i.b);
  END LOOP;
END;
/

Which outputs:

23, ABC
42, DEF

Then, if you want to join it with another table you can do:

CREATE TABLE table_name (id, value) AS
SELECT 23, 'UVW' FROM DUAL UNION ALL
SELECT 42, 'XYZ' FROM DUAL;

DECLARE
  v_objs objectTable := objectTable();
BEGIN
  v_objs.EXTEND(2);
  v_objs(1) := objectType(23, 'ABC');
  v_objs(2) := objectType(42, 'DEF');

  FOR i IN (SELECT o.a, o.b, t.value
            FROM   TABLE(v_objs) o
                   INNER JOIN table_name t
                   ON o.a = t.id)
  LOOP
    DBMS_OUTPUT.PUT_LINE(i.a || ', ' || i.b || ', ' || i.value);
  END LOOP;
END;
/

Outputs:

23, ABC, UVW
42, DEF, XYZ

db<>fiddle here