Oracle Object-Relational – PLS-00122 error

Type declaration:

CREATE TYPE INTERVENTO_TY AS OBJECT(
  Testo VARCHAR2(20),
  Timestmp DATE
) NOT FINAL;

CREATE TYPE COMMENTO_TY UNDER INTERVENTO_TY(
  Ordine VARCHAR2(20)
);

CREATE TYPE POST_TY UNDER INTERVENTO_TY(
  Titolo VARCHAR2(20),
  MEMBER PROCEDURE AddCom(Ordine VARCHAR2, Testo VARCHAR2, Timestmp Date)
);

CREATE TYPE AUTORE_TY AS OBJECT(
  Nome VARCHAR2(20),
  Cognome VARCHAR2(20),
  IdAutore INT
);

CREATE TYPE MEDIA_TY AS OBJECT(
  Tipo VARCHAR2(20),
  Nome VARCHAR2(20),
  IdMedia INT,
  Titolo VARCHAR2(20)
);

CREATE TYPE SCRIVE_INTERVENTO AS TABLE OF REF INTERVENTO_TY;
CREATE TYPE DI_AUTORE AS TABLE OF REF AUTORE_TY;
ALTER TYPE INTERVENTO_TY ADD ATTRIBUTE DI DI_AUTORE CASCADE;
ALTER TYPE AUTORE_TY ADD ATTRIBUTE SCRIVE SCRIVE_INTERVENTO CASCADE;
CREATE TYPE IN_INTERVENTO AS TABLE OF REF INTERVENTO_TY;
CREATE TYPE CONTIENE_MEDIA AS TABLE OF REF MEDIA_TY;
ALTER TYPE INTERVENTO_TY ADD ATTRIBUTE CONTIENE CONTIENE_MEDIA CASCADE;
ALTER TYPE MEDIA_TY ADD ATTRIBUTE IN_INT IN_INTERVENTO CASCADE;
CREATE TYPE COMMENTATO_POST AS TABLE OF REF COMMENTO_TY;
ALTER TYPE COMMENTO_TY ADD ATTRIBUTE A REF POST_TY CASCADE;
ALTER TYPE POST_TY ADD ATTRIBUTE COMMENTATO COMMENTATO_POST CASCADE;

CREATE TABLE AUTORE_TAB OF AUTORE_TY
NESTED TABLE SCRIVE STORE AS SCRIVE_NESTED;

CREATE TABLE INTERVENTO_TAB OF INTERVENTO_TY
NESTED TABLE DI STORE AS DI_NESTED
NESTED TABLE CONTIENE STORE AS CONTIENE_NESTED;

CREATE TABLE MEDIA_TAB OF MEDIA_TY
NESTED TABLE IN_INT STORE AS IN_INT_NESTED;

I am trying to insert the REF of the newly created POST_TY into the nested table SCRIVE of AUTORE_TY but it gives me this error:

DECLARE
   post POST_TY;
   post_ref REF INTERVENTO_TY;
BEGIN   
   post := new POST_TY('title', SYSDATE, DI_AUTORE((SELECT REF(A) 
                                                    FROM AUTORE_TAB A 
                                                    WHERE IdAutore = 1)), 
                        CONTIENE_MEDIA(), 'text', COMMENTATO_POST()
                       );
   INSERT INTO INTERVENTO_TAB VALUES(post);
   
   SELECT REF(post) INTO post_ref 
   FROM DUAL;
   
   INSERT INTO TABLE(SELECT A.scrive
                     FROM AUTORE_TAB A
                     WHERE A.IdAutore = 1)
   VALUES(postref);
   
END;
/

ORA-06550: line 5, column 56:
PLS-00122: CURSOR as separator is allowed only with specific built-in functions
ORA-06550: line 0, column 0:
PLS-00801: internal error [56402]

Answer

You can solve it using:

DECLARE
  post_ref REF INTERVENTO_TY;
BEGIN   
  INSERT INTO INTERVENTO_TAB t
  VALUES(
    POST_TY(
      'title',
      SYSDATE,
      DI_AUTORE(
        (SELECT REF(A) FROM AUTORE_TAB A WHERE IdAutore = 1)
      ), 
      CONTIENE_MEDIA(),
      'text',
      COMMENTATO_POST()
    )
  )
  RETURNING REF(t) INTO post_ref;
   
  INSERT INTO TABLE(
    SELECT A.scrive FROM AUTORE_TAB A WHERE A.IdAutore = 1
  )
  VALUES( post_ref );
END;
/

I’m not exactly sure what the issue was but it did not appear to work populating the DI_AUTORE collection in the PL/SQL scope but if you move the creation into the INSERT statement which is an SQL (and not PL/SQL) statement then it works.

Similarly, you can’t create a REF to the PL/SQL post variable; you need to create it referencing a table row.

db<>fiddle here