Function - oracle (PLS-00103: Encountered the symbol "")

Question

I'm trying to create a simple oracle function which loops over some records and then inserts records for each of those ..

CREATE OR REPLACE FUNCTION addNewRolesToAllGDP
    return NUMBER
    is dummy number;
BEGIN
    FOR applicationId IN (SELECT APPID
                          FROM GRPAPPLICATIONINSTANCES
                          where GRPAPPID = (select GRPAPPID
                                            from GRPAPPLICATIONS
                                            where GRPNAME = 'DIGITAL_OFFICE')
                            AND APPID in (select APPID from APPLICATIONS where REGEXP_LIKE(APPNAME, '[[:digit:]]')))
        LOOP
            INSERT INTO ROLES (ROLID, ROLNAME, APPID)
            VALUES (SEQROLES.nextval,
                    'INVENTORY_REQUESTER',
                    applicationId);
            INSERT INTO ROLES (ROLID, ROLNAME, APPID)
            VALUES (SEQROLES.nextval,
                    'INVENTORY_OWNER',
                    applicationId);
            INSERT INTO ROLES (ROLID, ROLNAME, APPID)
            VALUES (SEQROLES.nextval,
                    'INVENTORY_ADMIN',
                    applicationId);
        END LOOP;
    RETURN 1;
END;

alter function addNewRolesToAllGDP compile;

This statements gives me the following in USER_ERRORS:

PLS-00103: Encountered the symbol "" when expecting one of the following: ( return compress compiled wrapped

In the `INSERT` statements, you should use `applicationId.APPID` instead of `applicationId` — Aleksej, Jun 28 '19 at 14:25
@Aleksej That's one thing that was wrong probably. Still get the same error though. — GregD, Jun 28 '19 at 14:26
Compiles in [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f3ad05aa24cbd9378855be7f34d09d56). — Ponder Stibbons, Jun 28 '19 at 14:43
@PonderStibbons still getting 'ORA-06575: Package or function ADDNEWROLESTOALLGDP is in an invalid state' when trying to run with: 'select addNewRolesToAllGDP() from DUAL;' and the line is still there in USER_ERRORS — GregD, Jun 28 '19 at 14:45
Have you tried to call the function without `()`? Like this: `SELECT addNewRolesToAllGDP FROM DUAL;` — Paplusc, Jun 28 '19 at 14:51
You can try to compile it as standalone function, maybe error is thrown because it is part of package and previous lines causes error. — Ponder Stibbons, Jun 28 '19 at 14:57
@PonderStibbons if that's the case, then why is the error line named after my error? There are no other errors as far as I see. — GregD, Jun 28 '19 at 15:02
For example, when the closing semicolon in the previous line is missing, Oracle shows an error in the next. I don't know if this is the case here, but your function compiles for me, as you see in dbfiddle, so I guess that error is somewhere else. — Ponder Stibbons, Jun 28 '19 at 15:08
I used same @PonderStibbons dbfiddle in my oracle db and works like a charm — Milaci, Jun 28 '19 at 15:10
The weird behaviour made me switch tools. Was working in Intellij, which gave me this errors. Now switch to sqlDeveloper and there it compiles and executes.. Lol.. Still got another error though, but I will make another post for that one. — GregD, Jun 28 '19 at 15:13

Dave Costa · Answer 1 · 2019-06-28T15:12:31.017

I'm not sure if this is the problem, but I notice that you do not have a slash following the END statement of the function. In Oracle tools, without that slash, the statement does not actually get terminated, and the ALTER command is included as part of the same statement. This can cause weird syntax errors.

If this is the problem, fundamentally this question is a duplicate of oracle SQL plus how to end command in SQL file?.

Edited to add As others have said in comments, the same code seems to compile fine for me when I cut-and-paste it from your post. Based on the error and the position where it is reported, my best guess is that at the end of the first line your original source has some invisible character that is causing the parser error.

score 0 · Accepted Answer · answered Jun 28 '19 at 15:13

0

Used to run this in Intellij which gave me the stated behaviour. After running it from sqlDeveloper it compiled fine.

answered Jun 28 '19 at 15:13

GregD

1,884
2
28
55

score 0 · Answer 3 · answered May 11 '21 at 09:36

I got a similar error message for a missing comma in the procedure definition and got compiled after adding the comma.

Procedure definition before fix

PROCEDURE CREATE_WO(p_wo_type  IN NUMBER,
                          p_id     IN NUMBER,
                          p_do_commit        IN VARCHAR2 DEFAULT 'Y'
                          p_return_value     OUT VARCHAR2)
                          IS
    BEGIN   
      ...
   END;

Error Message

Cause: java.sql.SQLException: ORA-06550: line 8, column 9:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   ) , * & = - + < / > at in is mod remainder not rem =>
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

Procedure definition after fix, note the comma at the end of p_do_commit

PROCEDURE CREATE_WO(p_wo_type  IN NUMBER,
                          p_id     IN NUMBER,
                          p_do_commit        IN VARCHAR2 DEFAULT 'Y',
                          p_return_value     OUT VARCHAR2)
                          IS
    BEGIN   
      ...
   END;

Function - oracle (PLS-00103: Encountered the symbol "")

3 Answers3