CODEWITHSUNDEEP
sqloracle-databaseplsqlprocedure
Stoufa
Stoufa

Reputation: 21

(statement ignored) and (missing expression) errors

I'm a beginner in Databases world, my task was to write a procedure which adds a record in a nested table, this is the .sql file I wrote:

    CREATE OR REPLACE PROCEDURE TT1_AJOUTE_PERSON_DANS_ALBUM (
        numPersonnage   IN NUMBER,
        numAlbum        IN NUMBER
    ) IS
        tmpPersonnages TT1_personnages_ntab_type;
    BEGIN
        SELECT personnages
        INTO tmpPersonnages
        FROM TT1_Album
        WHERE (num = numAlbum);
        
        -- si la table imbriqué 'nested table' personnes est NULL on la crée
        IF tmpPersonnages IS NULL THEN
            UPDATE TT1_Album
            SET personnages = NEW TT1_personnages_ntab_type()
            WHERE (num = numAlbum);
        END IF;
        
        -- on va ajouter dans la tableau imbriqué 'nested table' (personnages)
        -- de la table des albums (TT1_Album)
        -- identifié par le numéro d'album passé en paramétre
        INSERT INTO TABLE (
            SELECT a.personnages
            FROM TT1_Album a
            WHERE (a.num = numAlbum)
        ) VALUES (
            -- appel du constructeur
            -- on utilise REF() pour récupérer l'OID de la ligne sélectionée
            -- de la table des personnages (TT1_personnage)
            -- le personnage est identifié par le numéro de personne passé en paramétre
            SELECT REF(p)
            FROM TT1_personnage p
            WHERE (p.num = numPersonnage)
        );
    END;
    /

when I run the .sql file I get a warning message (Procedure created with compilation errors.) and when I run (show errors procedure TT1_AJOUTE_PERSON_DANS_ALBUM;) I get the errors (statement ignored) and (missing expression)

Errors for PROCEDURE TT1_AJOUTE_PERSON_DANS_ALBUM:

LINE/COL ERROR
-------- -----------------------------------------------
22/2     PL/SQL: SQL Statement ignored
31/3     PL/SQL: ORA-00936: missing expression

I already googled about this issue and most of the times it is happening because of a typo, but I don't think it is my case ! these are the descriptions of the tables (TT1_Album) and (TT1_personnage)

SQL> desc TT1_Album;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------

  NUM                                       NOT NULL NUMBER
 TITRE                                     NOT NULL VARCHAR2(40)
 ANNEE                                     NOT NULL NUMBER
 PERSONNAGES                                        TT1_PERSONNAGES_NTAB_TYPE
SQL> desc TT1_personnage;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

  NUM                                       NOT NULL NUMBER
 NOM                                                VARCHAR2(20)
 PRENOM                                             VARCHAR2(20)
 PROFESSION                                         VARCHAR2(20)
 SEXE                                               CHAR(1)
 GENRE                                              VARCHAR2(7)

Upvotes: 0

Views: 238

Answers (1)

Adam Silenko
Adam Silenko

Reputation: 3108

se this syntax of insert:

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

for your example:

INSERT INTO TT1_Album (personnages, num)
SELECT REF(p), numAlbum           
FROM TT1_personnage p
WHERE p.num = numPersonnage;

Upvotes: 0

Related Questions