CODEWITHSUNDEEP
sqloracle-sqldeveloperpackage
fire frost
fire frost

Reputation: 437

Symbol error while creating a package SQL developer

My goal here is to create a package for a sql function that I created. I'm using the EMP and DEPT tables (https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html)

Here's the function (that works) :

create or replace function PRENOM_EMP (nom in EMPLOYEES.LAST_NAME%type)
return EMPLOYEES.FIRST_NAME%type
is prenom EMPLOYEES.FIRST_NAME%type;
begin
    select FIRST_NAME
    into prenom
    FROM EMPLOYEES
    WHERE last_name = nom;
return prenom;

EXCEPTION
    when too_many_rows then
    return ('l ordre sql ramene plus d une ligne');

end;

Select PRENOM_EMP ('King') from dual; /* Example of use */

This is how I try to put this function in a package :

create or replace package PKG_EMPLOYES as

function PRENOM_EMP (nom in EMPLOYEES.LAST_NAME%type)
    return EMPLOYEES.FIRST_NAME%type IS
    prenom EMPLOYEES.FIRST_NAME%type;
begin
    select FIRST_NAME
    into prenom
    FROM EMPLOYEES
    WHERE last_name = nom;
return prenom;

EXCEPTION
    when too_many_rows then
    return ('l ordre sql ramene plus d une ligne');

end PRENOM_EMP;

end PKG_EMPLOYES;

the error occurs at the 5th line

prenom EMPLOYEES.FIRST_NAME%type;

and SQL developer says : "Erreur(34,4): PLS-00103: Encountered the symbol "PRENOM" when expecting one of the following: language "

I followed the example of how to use packages from this website (https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6006.htm) but I can't find a solution.

Upvotes: 0

Views: 84

Answers (1)

Kris Rice
Kris Rice

Reputation: 3410

You have to break it into a package spec and package body.

So..

Spec:

create or replace package PKG_EMPLOYES as

function PRENOM_EMP (nom in EMPLOYEES.LAST_NAME%type)
    return EMPLOYEES.FIRST_NAME%type;

end PKG_EMPLOYES;
/

And body:

create or replace package body PKG_EMPLOYES as

function PRENOM_EMP (nom in EMPLOYEES.LAST_NAME%type)
    return EMPLOYEES.FIRST_NAME%type IS
    prenom EMPLOYEES.FIRST_NAME%type;
begin
    select FIRST_NAME
    into prenom
    FROM EMPLOYEES
    WHERE last_name = nom;
return prenom;

EXCEPTION
    when too_many_rows then
    return ('l ordre sql ramene plus d une ligne');

end PRENOM_EMP;

end PKG_EMPLOYES;
/

Upvotes: 1

Related Questions