Getting error while compiling PL/SQL function

Question

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.

Data type of parameters, or returning data type of a function should be unconstrained data types. That is you do need to specify maximum size. In your case return varchar(20) should be simply return varchar2. Do not use varchar data type. Although varchar and varchar2 are synonymous and there is no difference between them right now, their behavior may change in the future - stick to varchar2. Moreover take into consideration the fact that your query may return no rows, or return more than one row, raising no_data_found or too_many_rows exception respectively. — Nick Krasnov
– Nick Krasnov, Commented Nov 27, 2013 at 6:43
What exactly have you changed? Post updated version of your code with corresponding error message. It's also very likely that your left semicolon after return ... - remove it. Otherwise your function should compile just fine. — Nick Krasnov
– Nick Krasnov, Commented Nov 27, 2013 at 7:30

halfbit · Accepted Answer · 2013-11-27 07:47:35Z

2

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;

edited Nov 27, 2013 at 7:47

answered Nov 27, 2013 at 6:26

halfbit

3,4641 gold badge23 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ram12393 Over a year ago

What is the major difference between varchar and varchar2?

halfbit Over a year ago

Currently, there is no difference. But according to orafaq.com/faq/… this may change in the future. So using VARCHAR you risk unexepected implications when migrating to future versions of ORACLE database.

halfbit Over a year ago

It works on SQL Fiddle (see edit). What version of Oracle are you using?

ram12393 Over a year ago

ya working but what is the difference between return varchar2(20) and return varchar2?

Armunin Over a year ago

The difference is, that varchar2(20) means, your varchar2 can have a maximum length of 20 BYTES or CHARS and without the (20) it's not restricted. In the definition of the return value of a function you can't restrict the maximum length. Oracle does not allow it.

Armunin · Accepted Answer · 2013-11-27 07:51:33Z

0

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;

edited Nov 27, 2013 at 7:51

answered Nov 27, 2013 at 7:39

Armunin

9967 silver badges18 bronze badges

2 Comments

ram12393 Over a year ago

it's works fine but what is the difference between return varchar2(20) and return varchar2?

Armunin Over a year ago

For that check the answer of @halfbit below. It's per definition by Oracle.

Ashish sinha · Accepted Answer · 2013-12-02 06:14:45Z

0

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.

answered Dec 2, 2013 at 6:14

Ashish sinha

1481 gold badge2 silver badges9 bronze badges

Comments

Tom Thomas · Accepted Answer · 2013-12-02 07:24:40Z

0

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;

answered Dec 2, 2013 at 7:24

Tom Thomas

6292 gold badges8 silver badges24 bronze badges

Collectives™ on Stack Overflow

Getting error while compiling PL/SQL function

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related