Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . (9i)
PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317479] Thu, 01 May 2008 09:19 Go to next message
jyothsna1217
Messages: 9
Registered: February 2008
Location: hyderabad
Junior Member

Hi

create function fun1(n number)
return number is
begin
x number;
x:=n*12;
return x;
exception
when others then
dbms_output.put_line('err');
end;
/

ng: Function created with compilation errors.


Why i'm getting error
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317487 is a reply to message #317479] Thu, 01 May 2008 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Declarations must be in the declaration section before BEGIN and not in code section after BEGIN.

Regards
Michel
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317503 is a reply to message #317487] Thu, 01 May 2008 13:10 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, you might want to get rid of that useless WHEN OTHERS exception because the only result it may produce is to successfully hide real reason of an error.
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317671 is a reply to message #317479] Fri, 02 May 2008 07:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Additionally, unless you are calling this from SQL*Plus, dbms_output has no meaning in a function.
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317681 is a reply to message #317671] Fri, 02 May 2008 08:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you were feeling particularly masochistic, you could get the front end to fetch the output back from DBMS_OUTPUT and display it for you.

This particular Dbms_Output has no useful meaning at all, I'll give you that.
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317728 is a reply to message #317479] Fri, 02 May 2008 16:17 Go to previous messageGo to next message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Try this.
  create function fun1 (n number)
         return number is
  begin
    return n*12;
  end;


As you see you don't the variable x. But if you do need to create a variable, it must be created before the begin.

Using when others is is a bad habit. If you have to, then create an errors table that you insert something meaningful.

IE
ERRORS
username varchar2(30),
create_date date,
function varchar2(65),
parameters varchar2(65),
error varchar2(65)

Then insert into the table

insert into errors values
 (user,
  sysdate,
  'fun1',
  n,
  sqlerrm);
commit;


But this may not be what your really want to do because if you are in the middle of a transaction it will do a partial commit of the transaction. So, use the errors table solution with extreme care.

-Rob
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317729 is a reply to message #317728] Fri, 02 May 2008 16:22 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
For such a "logging" purposes, you might research what PRAGMA AUTONOMOUS TRANSACTION does; it will allow you to commit insert in a separate transaction without affecting possible changes in the main transaction.
Re: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . [message #317735 is a reply to message #317729] Fri, 02 May 2008 17:19 Go to previous message
rlockard
Messages: 19
Registered: April 2008
Location: Baltimore, MD
Junior Member
Littlefoot wrote on Fri, 02 May 2008 17:22
For such a "logging" purposes, you might research what PRAGMA AUTONOMOUS TRANSACTION does; it will allow you to commit insert in a separate transaction without affecting possible changes in the main transaction.


Good point, I did not think of that.

-Rob
Previous Topic: Spool a report file in the Background
Next Topic: Help: Histogram using Width_Bucket Function
Goto Forum:
  


Current Time: Tue Dec 06 10:20:54 CST 2016

Total time taken to generate the page: 0.19793 seconds