CODEWITHSUNDEEP
plsqloracle10gora-00918
dexter
dexter

Reputation: 1457

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 ;
/

Upvotes: 0

Views: 1114

Answers (1)

Codo
Codo

Reputation: 78865

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:

  • The SELECT statement seems to be a full outer join which is rarely what you want.
  • The SELECT statement with the INTO clause will fail if it returns more than one row.
  • What's the purpose of the FOR loop if you overwrite the local variables on each iteration and don't do anything with them.

Upvotes: 1

Related Questions