PL/SQL Function Error - PLS-00103: Encountered the symbol "IS"

Question

I am trying to create a simple PL/SQL function but its returning error -> PLS-00103: Encountered the symbol "IS". I am not sure whats wrong here, so could anybody out there please help. I followed the following steps -

Step1 - Logged in as sysdba using - sqlplus / as sysdba

Step2 - Created a file name test.sql with following code under C:\ -

CREATE OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR(20);
IS 
  emp_name VARCHAR(20); 
BEGIN 
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = '100';
RETURN emp_name;
END;
/

Step3 - Ran the following commands -

SQL>@C:\test.sql
Warning: Function created with compilation errors.
SQL> show error function Func;
Errors for FUNCTION FUNC:
LINE/COL ERROR
5/1      PLS-00103: Encountered the symbol "IS"
SQL>

Don't know why am I getting this weird error. Any help is highly appreciated.

When you login AS SYSDBA you connect to the SYS account. Please please please do not do this. SYS is a very powerful account because it owns the data dictionary. Consequently it is very easy to corrupt the database working as SYS if we don't understand what we;re doing. No offense, but the tenor of your question (a trivial syntax error) suggests this is your situation. Use SYS to create an application user, then use that to create schema objects. — APC, Nov 04 '12 at 13:59

score 1 · Answer 1 · edited Nov 04 '12 at 13:53

1

Create your function as follows and try

CREATE OR REPLACE FUNCTION employer_details_func
    RETURN VARCHAR2
IS
    emp_name   VARCHAR2 (20);
BEGIN
    SELECT  first_name
      INTO  emp_name
      FROM  emp_tbl
     WHERE  empID = '100';

    RETURN emp_name;
END;
/

edited Nov 04 '12 at 13:53

APC

144,005
19
170
281

answered Nov 04 '12 at 05:13

Jacob

14,463
65
207
320

still the same error :( - 5/1 PLS-00103: Encountered the symbol "IS" – anujin Nov 04 '12 at 05:17
1

Can you try executing your function from sql plus as `select employer_details_func from dual;` – Jacob Nov 04 '12 at 05:21
Polppan - +1 for you :) - With the above statment, I am able to see the output - `SQL> select employer_details_func from dual; EMPLOYER_DETAILS_FUNC ---------------------- sita` All the functions are stored in dual? Not sure why the weird warning during compilation? – anujin Nov 04 '12 at 05:30
1

dual is kind of a dummy table where you can execute function using select statement. More info [here](http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle). When I executed your function from script, it compiled without any errors. – Jacob Nov 04 '12 at 05:37
May be some software problem then. Anyways, you helped me a lot by suggesting to run it :). Thanks a bunch buddy. ++ for your suggestions. – anujin Nov 04 '12 at 05:39
@anujin And another point is I guess you creating this function as `sys` user under the same schema? Is not? If so any special reason for that? It is not a good practice to create your objects under `sys`. You should be doing that in other schemas, not in `SYS, SYSTEM` etc – Jacob Nov 04 '12 at 06:01
With Oracle we should always use VARCHAR2 not VARCHAR. – APC Nov 04 '12 at 13:54

PL/SQL Function Error - PLS-00103: Encountered the symbol "IS"

1 Answers1