Stored Procedure error ORA-06550

Question

I'm getting compile errors with this code using sqlplus.

My errors are:

Warning: Procedure created with compilation errors.

BEGIN point_triangle; END;

Error at line 1: ORA-06550: Line 1, column 7:
PLS-00905: object POINT_TRIANGLE is invalid
ORA-06550: line 1, column 7:
PL/SQL Statement ignored

Whenever I type show errors, it tells me there are no errors.

Here is the code.

create or replace procedure point_triangle
AS
A VARCHAR2(30);
B VARCHAR2(30);
C INT;
BEGIN
FOR thisteam in (select P.FIRSTNAME into A from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)
                (select P.LASTNAME into B from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)
                (select SUM(P.PTS) into C from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC);
LOOP
    dbms_output.put_line(A|| ' ' || B || ':' || C);
END LOOP;

END;
/

it is suppose to put all the players into A and B with their points of their career on that team into the C. I know the queries work, just not in the procedure.

No, I used it earlier in my code like that for a function. Where it first shows up is where it's declared. — user3002669, Nov 19 '13 at 03:00
this cursor is invalid, the into statement should disappear and instead a FETCH statement should appear afterwards. — Sebas, Nov 19 '13 at 05:50

score 3 · Accepted Answer · answered Nov 19 '13 at 06:03

3

create or replace procedure point_triangle
AS
BEGIN
FOR thisteam in (select FIRSTNAME,LASTNAME,SUM(PTS)  from PLAYERREGULARSEASON  where TEAM    = 'IND' group by FIRSTNAME, LASTNAME order by SUM(PTS) DESC)

LOOP
dbms_output.put_line(thisteam.FIRSTNAME|| ' ' || thisteam.LASTNAME || ':' || thisteam.PTS);
END LOOP;

END;
/

answered Nov 19 '13 at 06:03

The Hungry Dictator

3,444
5
37
53

Would there be any way I could print out two different FIRSTNAMES, LASTNAME, and PTS in the same line? Like move through the list in the same iteration of the loop. – user3002669 Nov 19 '13 at 20:41

Sergey N Lukin · Answer 2 · 2013-11-19T06:05:32.293

0

Could you try this one:

create or replace 
procedure point_triangle
IS
BEGIN
  FOR thisteam in (select P.FIRSTNAME,P.LASTNAME, SUM(P.PTS) S from PLAYERREGULARSEASON P  where P.TEAM = 'IND'  group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)
  LOOP
    dbms_output.put_line(thisteam.FIRSTNAME|| ' ' || thisteam.LASTNAME  || ':' || thisteam.S);
  END LOOP;

END;

edited Nov 19 '13 at 06:05

answered Nov 19 '13 at 05:57

Sergey N Lukin

575
2
16

score 0 · Answer 3 · answered Apr 04 '22 at 16:37

There is a semi colon at the end of the query after the closing braces, remove it and it will work:

FOR thisteam in (select P.FIRSTNAME into A from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)
            (select P.LASTNAME into B from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)
            (select SUM(P.PTS) into C from PLAYERREGULARSEASON P where P.TEAM = 'IND' group by P.FIRSTNAME, P.LASTNAME order by SUM(P.PTS) DESC)**;**

Stored Procedure error ORA-06550

3 Answers3