CODEWITHSUNDEEP
oracle-databaseplsqlsyntax-error
user2958666
user2958666

Reputation: 11

Unable to execute stored procedure

I'm trying to execute a ^stored procedure within a main stored procedure. But it's not working.

Error(11,9): PLS-00103: Encountered the symbol "FN_UPD" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "FN_UPD" to continue.

The code:

create or replace
procedure FN_READ

is
   cursor C_1 is select distinct(COLUMN1) from TB_SOURCE;
   cursor C_2 is select distinct(COLUMN2) from TB_SOURCE;
   cursor C_3 is select distinct(COLUMN3) from TB_SOURCE;
BEGIN

    for f in C_1
    LOOP
   EXEC FN_UPD('A', 'A');

    end LOOP;


    for f in C_2
    LOOP
   EXEC PROCEDURE FN_UPD(f.COLUMN2, 'M')
    end LOOP;

    for f in C_3
    LOOP
    EXEC FN_UPD(f.COLUMN3, 'O')

    end LOOP;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-    '||SQLERRM);
END;

Upvotes: 1

Views: 153

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40489

Replace

 EXEC FN_UPD(f.COLUMN3, 'O')

and

 EXEC PROCEDURE FN_UPD(f.COLUMN2, 'M')

with

FN_UPD(f.COLUMN3, 'O');

and

FN_UPD(f.COLUMN2, 'M');

respectively.

Note the trailing ;.

So, your procedure becomes

create or replace
procedure FN_READ

is
   cursor C_1 is select distinct(COLUMN1) from TB_SOURCE;
   cursor C_2 is select distinct(COLUMN2) from TB_SOURCE;
   cursor C_3 is select distinct(COLUMN3) from TB_SOURCE;
BEGIN

    for f in C_1 LOOP
        FN_UPD('A', 'A');
    end LOOP;


    for f in C_2 LOOP
        FN_UPD(f.COLUMN2, 'M')
    end LOOP;

    for f in C_3 LOOP
         FN_UPD(f.COLUMN3, 'O')
    end LOOP;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-    '||SQLERRM);
END;

Upvotes: 1

Sebas
Sebas

Reputation: 21522

EXEC (and the same applies to EXEC PROCEDURE) does not work in PL/SQL, use the procedure name directly:

BEGIN
    FN_UPD('A', 'A');
END;
/

Upvotes: 2

Related Questions