0

So I just created this trigger for my tables:

CREATE OR REPLACE 
PROCEDURE POPULATE_ACTOR_QUOTES (id_actor char)
AS
   CURSOR quote_recs IS
      SELECT m.title, 
             m.year, 
             r.roleName,
             q.quotechar 
        from quote q, 
             role r, 
             rolequote rq,  
             actor a, 
             movie m
       where rq.quoteID = q.quoteID
         AND rq.roleID = r.roleID
         AND r.actorID = a.actorID
         AND r.movieID = m.movieID
         AND a.actorID = id_actor;
BEGIN
   FOR row IN quote_recs 
   LOOP
      INSERT INTO table(
      SELECT quotes
        FROM actor_quotes aq
       WHERE aq.actorId = id_actor)
      VALUES(
         ACTOR_QUOTE_TYPE(row.title, row.year, row.roleName, row.quotechar)
      );
   end loop;
END POPULATE_ACTOR_QUOTES;
/

I now want to unest the table QUOTES for any ACTORID by using a query similar that unnests the QUOTES table and lists all four data items inside it.

This was the original table schema

CREATE TABLE ACTOR_QUOTES (
   ACTORID CHAR(5),
   QUOTES  AQ_NT
)  
NESTED TABLE QUOTES STORE AS ACTOR_QUOTES_NT
/
skaffman
  • 398,947
  • 96
  • 818
  • 769
dexter
  • 1,347
  • 2
  • 9
  • 7

1 Answers1

1

You can do the same using TABLE function 

  SELECT REC.*

  FROM ACTOR_QUOTES A,TABLE(A.QUOTES) REC

For reference please check

http://psoug.org/reference/nested_tab.html

psaraj12
  • 4,772
  • 2
  • 21
  • 30