compilation errors for procedures in sqlplus

Question

Now Here's the edit ... its the complete database with the actual code.

PROMPT Creation des tables
DROP TABLE GroupeCours
/
DROP TABLE SessionUQAM
/
DROP TABLE Professeur
/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'
/

CREATE TABLE SessionUQAM
(codeSession    INTEGER     NOT NULL,
 dateDebut  DATE        NOT NULL,
 dateFin    DATE        NOT NULL
)
/

CREATE TABLE Professeur
(codeProfesseur     CHAR(5) NOT NULL,
 nom            VARCHAR(10) NOT NULL,
 prenom     VARCHAR(10) NOT NULL
 )
/

CREATE TABLE GroupeCours
(sigle      CHAR(7)     NOT NULL,
 noGroupe   INTEGER     NOT NULL,
 codeSession    INTEGER     NOT NULL,
 maxInscriptions    INTEGER     NOT NULL,
 codeProfesseur     CHAR(5) NOT NULL
)
/

INSERT INTO SessionUQAM
VALUES(32003,'3/09/2003','17/12/2003')
/
INSERT INTO SessionUQAM
VALUES(12004,'8/01/2004','2/05/2004')
/

INSERT INTO Professeur
VALUES('TREJ4','Tremblay','Jean')
/
INSERT INTO Professeur
VALUES('DEVL2','De Vinci','Leonard')
/
INSERT INTO Professeur
VALUES('PASB1','Pascal','Blaise')
/
INSERT INTO Professeur
VALUES('GOLA1','Goldberg','Adele')
/
INSERT INTO Professeur
VALUES('KNUD1','Knuth','Donald')
/
INSERT INTO Professeur
VALUES('GALE9','Galois','Evariste')
/
INSERT INTO Professeur
VALUES('CASI0','Casse','Illa')
/
INSERT INTO Professeur
VALUES('SAUV5','Sauve','Andre')
/

INSERT INTO Professeur
VALUES('loool','Sauve','Andre')
 /


INSERT INTO GroupeCours
VALUES('INF1110',20,32003,100,'loool')
/
INSERT INTO GroupeCours
VALUES('INF1110',30,32003,100,'PASB1')
/
INSERT INTO GroupeCours
VALUES('INF1130',10,32003,100,'PASB1')
/
INSERT INTO GroupeCours
VALUES('INF1130',30,32003,100,'GALE9')
/
INSERT INTO GroupeCours
VALUES('INF2110',10,32003,100,'TREJ4')
/
INSERT INTO GroupeCours
VALUES('INF3123',20,32003,50,'GOLA1')
/
INSERT INTO GroupeCours
VALUES('INF3123',30,32003,50,'GOLA1')
/
INSERT INTO GroupeCours
VALUES('INF3180',30,32003,50,'DEVL2')
/
INSERT INTO GroupeCours
VALUES('INF3180',40,32003,50,'DEVL2')
/
INSERT INTO GroupeCours
VALUES('INF5180',10,32003,50,'KNUD1')
/
INSERT INTO GroupeCours
VALUES('INF5180',40,32003,50,'KNUD1')
 /
INSERT INTO GroupeCours
VALUES('INF1110',20,12004,100,'TREJ4')
/
INSERT INTO GroupeCours
VALUES('INF1110',30,12004,100,'TREJ4')
/
INSERT INTO GroupeCours
VALUES('INF2110',10,12004,100,'PASB1')
/
INSERT INTO GroupeCours
VALUES('INF2110',40,12004,100,'PASB1')
/
INSERT INTO GroupeCours
VALUES('INF3123',20,12004,50,'GOLA1')
/
INSERT INTO GroupeCours
VALUES('INF3123',30,12004,50,'GOLA1')
/
INSERT INTO GroupeCours
VALUES('INF3180',10,12004,50,'DEVL2')
/
INSERT INTO GroupeCours
VALUES('INF3180',30,12004,50,'DEVL2')
/
INSERT INTO GroupeCours
VALUES('INF5180',10,12004,50,'DEVL2')
/
INSERT INTO GroupeCours
VALUES('INF5180',40,12004,50,'GALE9')
/

COMMIT
/

Now here my code

CREATE OR REPLACE PROCEDURE TacheEnseignement(codeProfesseur VARCHAR2) 
AS
    sig VARCHAR2(7);
    noGr INTEGER;
    codeS INTEGER;
    nomP VARCHAR2(10);
    prenomP VARCHAR2(10);
    dateD DATE;
    dateF DATE;
    CURSOR ligneGroupe(unCodeProf VARCHAR2) IS
        SELECT sigle, noGroupe, codeSession
        FROM GroupeCours
        WHERE codeProfesseur = unCodeProf ;
BEGIN
    OPEN ligneGroupe(codeProfesseur);
    LOOP
        FETCH ligneGroupe INTO sig, noGr, codeS;
        EXIT WHEN ligneGroupe%NOTFOUND;
        SELECT nom, prenom INTO nomP, prenomP FROM Professeur WHERE codeProfesseur=codeProfesseur;
        SELECT dateDebut, dateFin INTO dateD, dateF FROM SessionUQAM WHERE codeSession=codeS;
        IF ( nomP IS NULL OR prenomP IS NULL OR dateD IS NULL OR dateF IS NULL ) THEN
            DBMS_OUTPUT.PUT_LINE('The teacher that has ' + codeProfesseur + ' as code is not in the database');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Code professeur :' || codeProfesseur);
            DBMS_OUTPUT.PUT_LINE('Nom :' || nomP);
            DBMS_OUTPUT.PUT_LINE('Prenom :' || prenomP);
            DBMS_OUTPUT.PUT_LINE('Sigle :' || sig);
        END IF;
    END LOOP;
    CLOSE ligneGroupe;


END TacheEnseignement;

I am still trying to execute it by:

EXECUTE TacheEnseignement('GOLA1');

Answer 1

My take at this would be (based on initial answer from Barbaros and the question edits):

CREATE OR REPLACE PROCEDURE TacheEnseignement(codeProfesseur IN Professeur.codeProfesseur%TYPE) AS
    profNom Professeur.nom%TYPE;
    profPrenom Professeur.prenom%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Code professeur: ' || codeProfesseur);
    BEGIN
        SELECT p.nom, p.prenom
          INTO profNom, profPrenom
          FROM Professeur p
         WHERE p.codeProfesseur = TacheEnseignement.codeProfesseur;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('The teacher that has ' || codeProfesseur || ' as code is not in the database');
            RETURN;
    END;
    DBMS_OUTPUT.PUT_LINE('Nom: ' || profNom);
    DBMS_OUTPUT.PUT_LINE('Prenom: ' || profPrenom);
    DBMS_OUTPUT.PUT_LINE('Cours:');
    FOR foundGroup IN (
        SELECT DISTINCT c.sigle, c.noGroupe, s.dateDebut, s.dateFin, c.codeSession
          FROM GroupeCours c
          LEFT OUTER JOIN SessionUQAM s
            ON s.codeSession = c.codeSession
         WHERE c.codeProfesseur = TacheEnseignement.codeProfesseur
        ORDER BY 1, 2, 3, 4, 5
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('- Sigle: ' || foundGroup.sigle);
        DBMS_OUTPUT.PUT_LINE('  No groupe: ' || foundGroup.noGroupe);
        DBMS_OUTPUT.PUT('  Code session: ' || foundGroup.codeSession);
        IF foundGroup.dateDebut IS NOT NULL THEN
            DBMS_OUTPUT.PUT(' (');
            DBMS_OUTPUT.PUT(TO_CHAR(foundGroup.dateDebut, 'YYYY"-"MM"-"DD'));
            DBMS_OUTPUT.PUT(' - ');
            DBMS_OUTPUT.PUT(TO_CHAR(foundGroup.dateFin, 'YYYY"-"MM"-"DD'));
            DBMS_OUTPUT.PUT(')');
        END IF;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;
END TacheEnseignement;
/

And a link to Oracle Live SQL script for all this (since SQL Fiddle does not have a simple way of seeing the DBMS_OUTPUT).

Answer 2

• First of all keywords such as session can not be used as a table name.
• Concatenation operation is not + but || in Oracle.
• A parameter for a Procedure with length precision can not be defined.

• Don't use deprecated data types Char or varchar but use varchar2 instead.

• Each DDL(create table in this case) or DML(insert into in this case) statements should be ended with semi-colons after parentheses.

So, the following series of commands may be used :

SQL> set serveroutput on;
SQL> CREATE TABLE Professor
 (codeProfessor     varCHAR2(5) NOT NULL,
  Lastname          VARCHAR2(10) NOT NULL,
  Firstname     VARCHAR2(10) NOT NULL
 );

SQL> CREATE TABLE Session_
  (codeSession  INTEGER     NOT NULL,
   dateBegin    DATE        NOT NULL,
   dateEnd  DATE        NOT NULL
  );

SQL> CREATE TABLE GroupClass
 (classId       varCHAR2(7)     NOT NULL,
  noGroup   INTEGER     NOT NULL,
  codeSession   INTEGER     NOT NULL,
  maxInscriptions   INTEGER     NOT NULL,
  codeProfessor     varCHAR2(5) NOT NULL
 );

SQL> INSERT INTO GroupClass
VALUES('INF5180',40,32003,50,'RANDO');

SQL> INSERT INTO Session_
VALUES(32003,date'2003-09-03',date'2003-12-17');

SQL> INSERT INTO Professor
VALUES('RANDO','Random','Random');

SQL> CREATE OR REPLACE PROCEDURE Teaching(i_codeProfessor varchar2)
AS
classid VARCHAR2(7);
noGr INTEGER;
codeS INTEGER;
lName VARCHAR2(10);
fName VARCHAR2(10);
dateB DATE;
dateE DATE;
CURSOR lineGroup(oneCodeProf varchar2) IS
    SELECT classID, noGroup, codeSession
    FROM GroupClass
    WHERE codeProfessor = oneCodeProf ;
BEGIN
    OPEN lineGroup(i_codeProfessor);
    LOOP
        FETCH lineGroup INTO classid, noGr, codeS;
        EXIT WHEN lineGroup%NOTFOUND;
        SELECT Lastname, Firstname INTO lName, fName FROM Professor WHERE codeProfessor=i_codeProfessor;
        SELECT dateBegin, dateEnd INTO dateB, dateE FROM Session_ WHERE codeSession=codeS;
        IF ( lName IS NULL OR fName IS NULL OR dateB IS NULL OR dateE IS NULL ) THEN
            DBMS_OUTPUT.PUT_LINE('The teacher that has ' || i_codeProfessor||' as code is not in the database');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Code professor :' || i_codeProfessor);
            DBMS_OUTPUT.PUT_LINE('Last name :' || lName);
            DBMS_OUTPUT.PUT_LINE('First name :' || fName);
            DBMS_OUTPUT.PUT_LINE('Class ID :' || classid);
        END IF;
    END LOOP;
    CLOSE lineGroup;


END Teaching;
/
SQL> EXECUTE Teaching('RANDO');

Code professor :RANDO
Last name :Random
First name :Random
Class ID :INF5180

PL/SQL procedure successfully completed

EDIT ( with respect to your last edit ) :

SQL> SET SERVEROUTPUT ON;
SQL> CREATE OR REPLACE PROCEDURE TacheEnseignement(i_codeProfessor VARCHAR2)
AS
sig VARCHAR2(7);
noGr INTEGER;
codeS INTEGER;
nomP VARCHAR2(10);
prenomP VARCHAR2(10);
dateD DATE;
dateF DATE;
CURSOR ligneGroupe(unCodeProf VARCHAR2) IS
    SELECT sigle, noGroupe, codeSession
    FROM GroupeCours
    WHERE codeProfesseur = unCodeProf ;
BEGIN
    FOR c in ligneGroupe(i_codeProfessor)
    LOOP
         sig := c.sigle;
         noGr:= c.nogroupe;
         codeS:= c.codesession;
    END LOOP;    

    FOR d in ( SELECT nom, prenom FROM Professeur WHERE codeProfesseur=i_codeProfessor)
    LOOP
      nomP := d.nom; 
      prenomP := d.prenom;  
    END LOOP;   

    FOR e in ( SELECT dateDebut, dateFin FROM SessionUQAM WHERE codeSession=codeS )
    LOOP
      dateD := e.datedebut; 
      dateF := e.datefin;  
    END LOOP;


    IF ( nomP IS NULL OR prenomP IS NULL OR dateD IS NULL OR dateF IS NULL ) THEN
        DBMS_OUTPUT.PUT_LINE('The teacher that has '||i_codeProfessor||' as code is not in the database');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Code professeur :' || i_codeProfessor);
        DBMS_OUTPUT.PUT_LINE('Nom :' || nomP);
        DBMS_OUTPUT.PUT_LINE('Prenom :' || prenomP);
        DBMS_OUTPUT.PUT_LINE('Sigle :' || sig);
    END IF;
END TacheEnseignement;
/

SQL> EXECUTE TacheEnseignement('GOLA1');

Code professeur :GOLA1
Nom :Goldberg
Prenom :Adele
Sigle :INF3123

PL/SQL procedure successfully completed

You can get a result without ORA-01422 by using FOR LOOP .. END LOOP statements as in the previous one. But, has no sense, without any logical rule unless refined with extra restrictions.

P.S. : In the second case do not close the cursor, since it's implicit. We call implicit cursors. Implicit cursors are automatically created and destroyed by the Oracle server.