plsqloracle10gora-00918

procedure error ORA-00918: column ambiguously defined

Hey can anyone help with this errors:

9/10 PLS-00428: an INTO clause is expected in this SELECT statement

This appear when trying to prints out details based on a single parameter. This is the code I have been using

 CREATE OR REPLACE PROCEDURE PRINT_ACTOR_QUOTES (ID_actor CHAR)
 AS
 first_name CHAR(30);
 last_name CHAR(30);
 title CHAR(40);
 year NUMBER;
 role CHAR(40);
quote CHAR(255);
CURSOR print_cursor IS
SELECT a.actorID, a.firstname, a.lastname, m.title as title, m.year as year ,  
 r.rolename as role1 , q.quotechar as quote1 FROM movie m, role r, quote q, rolequote  
rq, actor a WHERE
a.actorID = r.actorID AND
m.movieID = r.movieID AND
rq.quoteID = q.quoteID AND
rq.roleID = r.roleID;
BEGIN  
FOR row IN print_cursor LOOP
SELECT AQ.QUOTES, A.FIRSTNAME, A.LASTNAME 
FROM ACTOR_QUOTES AQ, ACTOR A
Where row.actorID = ID_actor;
END LOOP; 
END PRINT_ACTOR_QUOTES ;
/
Solution

  • I'm assuming that the relevant error is

    PLS-00428: an INTO clause is expected in this SELECT statement

    (and not the one you mention in the title of your question).

    The problem is the SELECT statement in your FOR loop. In PL/SQL, you cannot just select data without doing anything with it. So you need an INTO statement to store the retrieved data in a PL/SQL variable:

    SELECT AQ.QUOTES, A.FIRSTNAME, A.LASTNAME
  INTO quote, first_name, last_name
FROM ACTOR_QUOTES AQ, ACTOR A
Where row.actorID = ID_actor;

    Then I can spot several additional problems: