CODEWITHSUNDEEP
oracle-databasefunctionplsqlsyntax-error
ram12393
ram12393

Reputation: 1258

Getting error while compiling PL/SQL function

CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR(20);
 IS 
   e_name VARCHAR(20); 
 BEGIN 
    SELECT emp_name INTO e_name
    FROM employees WHERE emp_no = '5';
    RETURN e_name;
 END employer_details_func;

While compiling the above program I'm getting this error

Error(2,18): PLS-00103: Encountered the symbol "(" when expecting one of the following: . @ % ; is authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache

UPDATE:

CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR2(20);
 IS 
   e_name VARCHAR2(20); 
 BEGIN 
    SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';
    RETURN e_name;
 END employer_details_func;

Error:

Error(2,19): PLS-00103: Encountered the symbol "(" when expecting one of the following:     . @ % ; is authid as cluster order using external character    deterministic parallel_enable pipelined aggregate    result_cache

What is wrong with my code? Please let me know.

Upvotes: 0

Views: 8659

Answers (4)

Tom Thomas
Tom Thomas

Reputation: 629

you can also try like this

CREATE OR REPLACE FUNCTION employer_details_func

RETURN employees.e_name%TYPE

IS

e_name employees.e_name%TYPE;

BEGIN 

SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';

RETURN e_name;

END employer_details_func;

Upvotes: 0

Ashish sinha
Ashish sinha

Reputation: 148

CREATE OR REPLACE

FUNCTION employer_details_func

RETURN VARCHAR2

IS

e_name VARCHAR2(20);

BEGIN 

SELECT last_name INTO e_name FROM employees WHERE employee_id ='100';

RETURN e_name;

END employer_details_func;

You cannot mention the size of the return type its managed by oracle engine.

Upvotes: 0

Armunin
Armunin

Reputation: 996

This should fix it:

CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR2
 IS 
   e_name VARCHAR2(20); 
 BEGIN 
    SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';
    RETURN e_name;
 END employer_details_func;

Upvotes: 0

halfbit
halfbit

Reputation: 3464

Use RETURN VARCHAR instead of RETURN VARCHAR(20);.

Oracle documentation at http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm says RETURN Clause ... The datatype cannot specify a length, precision, or scale ...

Edit

I double checked it on SQL Fiddle with this code and it seems to work (returns bob):

CREATE TABLE employees(emp_name VARCHAR2(20), emp_no VARCHAR2(20))
/
CREATE OR REPLACE FUNCTION employer_details_func
   RETURN VARCHAR
 IS 
   e_name VARCHAR(20); 
 BEGIN 
   SELECT emp_name INTO e_name FROM employees WHERE emp_no ='5';
   RETURN e_name;
 END employer_details_func;
/

insert into employees values('bob','5');
select employer_details_func() from dual;

Upvotes: 2

Related Questions