problem with CAST MULTISET

I'm learning how to use CAST MULTISET.  The following resulted in an error ("ORA-00904 : Invalid identifier" for the syntax "candidates.experiences").  What would be the correct way to do this?
CREATE OR REPLACE TYPE t_experience AS OBJECT
(
    companyname    varchar2(20),
    position       varchar2(20),
    noofyears      number(2)
);
/
 
CREATE OR REPLACE TYPE t_experience_tbl AS TABLE OF t_experience; 
/
 
CREATE TABLE candidates
(
    name            varchar2(20),
    experiences     t_experience_tbl
) NESTED TABLE experiences STORE AS experiences_tab;
/
 
INSERT INTO candidates VALUES
(
    'John',
    t_experience_tbl
    (
        t_experience('A company','Software Engineer',3),
        t_experience('B company','System Analyst',2),
        t_experience('C company','Research Fellow',4)
     )
);
/
 
INSERT INTO candidates VALUES
(
    'Jack',
    t_experience_tbl
    (
        t_experience('E company','Hardware Engineer',3),
        t_experience('F company','Data Analyst',5),
        t_experience('G company','Database Admin',5)
     )
);
/
 
DECLARE
  l_tempTable t_experience_tbl;
 
BEGIN
  SELECT experiences INTO l_tempTable FROM candidates WHERE name = 'John';
  SELECT experiences MULTISET UNION l_tempTable INTO l_tempTable FROM candidates WHERE name = 'Jack';
 
  FOR i IN l_tempTable.FIRST .. l_tempTable.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(l_tempTable(i).companyname || ' ' || l_tempTable(i).position || ' ' || l_tempTable(i).noofyears);
  END LOOP;
 
  -- everything works fine up to this point --
  -- then I added this part to play with CAST MULTISET
  -- to see if I can get it to do the work of the few lines of code above
  
  SELECT CAST (MULTISET (SELECT * FROM TABLE(candidates.experiences)) AS t_experience_tbl) INTO l_tempTable FROM dual;
 
  FOR i IN l_tempTable.FIRST .. l_tempTable.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(l_tempTable(i).companyname || ' ' || l_tempTable(i).position || ' ' || l_tempTable(i).noofyears);
  END LOOP;
END;
/

Open in new window

LVL 4
n_fortynineAsked:
Who is Participating?
 
AndytwCommented:
If you want to use CAST( MULTISET(  ) ) then see the code below:
DECLARE
   l_tempTable t_experience_tbl;
 
BEGIN
   SELECT experiences
   INTO   l_tempTable
   FROM   candidates
   WHERE  NAME = 'John';
   SELECT experiences MULTISET
   UNION l_tempTable
   INTO   l_tempTable
   FROM   candidates
   WHERE  NAME = 'Jack';
 
   FOR i IN l_tempTable.FIRST .. l_tempTable.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(l_tempTable(i).companyname || ' ' || l_tempTable(i).position || ' ' || l_tempTable(i).noofyears);
   END LOOP;
 
   -- everything works fine up to this point --
   -- then I added this part to play with CAST MULTISET
   -- to see if I can get it to do the work of the few lines of code above
 
   /*SELECT CAST(MULTISET (SELECT *
                FROM   TABLE(candidates.experiences)) AS t_experience_tbl)
   INTO   l_tempTable
   FROM   dual;*/
   SELECT CAST(MULTISET (SELECT e.companyname,
                    e.position,
                    e.noofyears
             FROM   candidates c,
                    TABLE(c.experiences) e
             WHERE  c.NAME IN ('John', 'Jack')) AS t_experience_tbl) experiences
   into l_tempTable          
   FROM   dual;
   
 
   FOR i IN l_tempTable.FIRST .. l_tempTable.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(l_tempTable(i).companyname || ' ' || l_tempTable(i).position || ' ' || l_tempTable(i).noofyears);
   END LOOP;
END;
/

Open in new window

0
 
AndytwCommented:
However, I think the following is a lot more readable:

SELECT t_experience(e.companyname, e.position, e.noofyears)
BULK COLLECT INTO l_tempTable
FROM   candidates c,
             TABLE(c.experiences) e
WHERE  c.NAME IN ('John', 'Jack');
0
 
n_fortynineAuthor Commented:
Ah, thanks for pointing out the correct syntax.  If you don't mind me asking, what is the purpose of CAST MULTISET then, when BULK COLLECT will work just as well?  (Should I make a separate question?)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.