Oracle: exception and error

Question

I have written this function in SQL and I can't figure out why but it keeps on returning an error in the exception section. I've checked the syntax, got documented but I had no luck, will anyone please help?

Error returned:

Error on line 83: PLS-00103: Found symbol "EXCEPTION" instead one of the following:
    begin case declare end exit for goto if loop mod null pragma raise return select update while with
    <<
    close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

create or replace function InsertPeriodoEvento
    (NomePeriodo in varchar,
    aInizio in integer,
    aFine in integer,
    NomeEvento1 in varchar,
    g1 in integer,
    m1 in integer,
    a1 in integer,
    NomeEvento2 in varchar,
    g2 in integer,
    m2 in integer,
    a2 in integer)
return integer is

id_Periodo number;

begin
savepoint prima_di_tutto;

insert into Periodo (Nome) values (NomePeriodo);

select Id into id_Periodo from Periodo where Nome=NomePeriodo;


if NomeEvento2 is null then
    begin
        insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
            values (NomeEvento1, g1, m2, a2, IdPeriodo);

        update Periodo set
            AnnoFine = aFine,
            IdEventoInizio = (select Id from Evento where Nome = NomeEvento1)
            where Periodo.Id = id_Periodo;
    end;
else
    if NomeEvento1 is null then
    begin

        insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
            values (NomeEvento2, g2, m2, a2, IdPeriodo);

        update Periodo set
            AnnoInizio = aInizio,
            IdEventoFine = (select Id from Evento where Nome = NomeEvento2)
            where Periodo.Id = id_Periodo;
    end;
else
    begin
        insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
            values (NomeEvento1, g1, m2, a2, IdPeriodo);


        insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
            values (NomeEvento2, g2, m2, a2, IdPeriodo);


        update Periodo set
            IdEventoInizio = (select Id from Evento where Nome = NomeEvento1),
            IdEventoFine = (select Id from Evento where Nome = NomeEvento2)
            where Periodo.Id = id_Periodo;
    end;
end if;

commit;


return 1;


EXCEPTION
when OTHERS then
    rollback to prima_di_tutto;
    return 0;

 end InsertPeriodoEvento;

you should have an elsif (not "else if") until the very last one. if() then ... elsif() then ... else() end if; — tbone, Jun 30 '11 at 10:42

schurik · Answer 1 · 2011-06-30T11:25:21.803

5

you miss another END IF;

            update Periodo set
                IdEventoInizio = (select Id from Evento where Nome = NomeEvento1),
                IdEventoFine = (select Id from Evento where Nome = NomeEvento2)
                where Periodo.Id = id_Periodo;
        end;
      end if;
--   ^^^^^^^
    end if;


    commit;


    return 1;


    EXCEPTION
    when OTHERS then
        rollback to prima_di_tutto;
        return 0;

     end InsertPeriodoEvento;

edited Jun 30 '11 at 11:25

answered Jun 30 '11 at 08:38

schurik

7,798
2
23
29

Well, I've tried this already but it returns me: `Error on line 66 PL/SQL: SQL Statement ignored.` – haunted85 Jun 30 '11 at 09:08
1

@haunted85: this is the next Error, check the SQL-Statment on line 66 could you execute it isolated in sqlplus? – schurik Jun 30 '11 at 09:36
@haunted85 I hope you didn't cut & paste of schurik'scode. He inserted simbol'^^^^^^' to let you understand where he inserted 'end if'. Obviously, the simbol'^' isn't a valid Character, neither a 'valid' lexical units! 1 http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/fundamentals.htm#CIHEGGJG 2 http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/fundamentals.htm#CIHJCJAD – zep Jun 30 '11 at 11:12
Why did you edit your code and commented '^^^^^^^', without post a note to your modification? – zep Jun 30 '11 at 11:36
1

@zep: thank you for the remark about the **^^^^^^**, a have edit my answer and commented this line. – schurik Jun 30 '11 at 11:44

score 5 · Accepted Answer · answered Jun 30 '11 at 12:37

Your second problem, after adding the END IF as described by @schurik, is that you have mixed up a column name with a variable name in your inserts:

    insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
        values (NomeEvento2, g2, m2, a2, IdPeriodo);

should be

    insert into Evento (Nome, Giorno, Mese, Anno, IdPeriodo)
        values (NomeEvento2, g2, m2, a2, id_Periodo);

IdPeriodo is a column in the table. id_Periodo is the variable that holds its value. This needs to be fixed in all four inserts into Evento.

On an unrelated note, you can consolidate these two lines:

insert into Periodo (Nome) values (NomePeriodo);

select Id into id_Periodo from Periodo where Nome=NomePeriodo;

into:

insert into Periodo (Nome) values (NomePeriodo) returning Id into id_Periodo;

Oracle: exception and error

2 Answers2