Home » SQL & PL/SQL » SQL & PL/SQL » Encountered the symbol "end-of-file" when expecting one of the following (Oracle 11.1.0.6.0)
Encountered the symbol "end-of-file" when expecting one of the following [message #358554] Tue, 11 November 2008 20:00 Go to next message
tj7723
Messages: 1
Registered: November 2008
Junior Member
Hi,

I am getting 2 error messages in the the following code, can any one help me out. Thanks in advance.

the first is

7/13 encountered the symbol ';' when expecting one of the following:
. into bulk

19/4 Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map.


create or replace trigger P_Check
  before insert on PERSONNEL
  for each row
declare
Test varchar2(6);
 CURSOR Cnets is select PLANET.NAME from PLANET;
    rec Cnets%rowtype;
begin
	open Cnets;
	fetch Cnets;
			while Cnets%found loop
				if :new.PLANET = rec.PLANET then
					Test := 'Found';
					break;
				end if;	
				fetch Cnets into rec;
			end loop;	
		if Test <> 'Found' then
			insert into PLANET values(:new.PLANET, 1,5);
		end if;	
	close Cnets;
end;
/


i have been looking at this for some time now so i could be over looking some thing little.

[Updated on: Tue, 11 November 2008 20:15]

Report message to a moderator

Re: Encountered the symbol "end-of-file" when expecting one of the following [message #358572 is a reply to message #358554] Tue, 11 November 2008 21:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is this a Pseudo code ? IF not , paste the code in SQL Session and show us where exactly the error comes.

Btw,

What are you intended to do with this code ?
Why are you looping through each record in row level trigger ?
Why can't you try to do the same using the straight sql rather than looping through the tables ?

Smile
Rajuvan.
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #359315 is a reply to message #358554] Fri, 14 November 2008 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Some errors in your code:

1. You have to fetch into something, so your first fetch should have an into clause, just as your second fetch does.

2. You are attempting to compare to rec.planet, which does not exist. You selected planet.name from planet in your cnets cursor, then fetched that into rec, so you need to compare to rec.name.

3. In pl/sql we don't use break; We use exit.

4. You have not assigned an initial value to your test variable, so by default it will be null, so it does not evaluate the test<>'Found' the way you want when test is null. So, you should assign an initial value or test for test is null.

Suggestion for a better way:

Replace the whole mess with one insert statement with a where not exists clause. You should be able to fill in the ... in the following partial code.

CREATE OR REPLACE TRIGGER P_Check
  BEFORE INSERT ON personnel
  FOR EACH ROW
BEGIN
  INSERT INTO ... 
  SELECT ... 
  FROM   DUAL
  WHERE  NOT EXISTS
         (SELECT ...
          FROM   ...
          WHERE  ...);
END p_check;
/



Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430489 is a reply to message #359315] Tue, 10 November 2009 12:42 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
I am getting an almost identical error message with a much simpler Stored procedure.

create or replace
PROCEDURE "SEL_ORGANIZATIONS_SP"
AS
BEGIN
Select OrganizationID, OrganizationName From ERFO.Organizations
END;

Which delivers compile error of
Error(6,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

I can bearly decipher the message, much less understand what it doesn't like. Any help would be appreciated
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430493 is a reply to message #430489] Tue, 10 November 2009 13:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Statements in PL/SQL should be terminated with a semicolon (Wink
Your select is not.
Furthermore, a select, without anything to catch the selected values in, has no meaning in Oracle's PL/SQL (This is NOT SQLServer!)
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430495 is a reply to message #430489] Tue, 10 November 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430508 is a reply to message #430493] Tue, 10 November 2009 14:44 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
assume the semi-colon as I tried it both ways, with no change to the message.

So the question is how do I generate a stored procedure to to this. (Once I get that information I expect to be building significantly more complex queries to return data to the datareaders, so I would like to understand what is wrong.)

I understand this is not SQL Server, or DB2 or any of the many other database engines I have experience with. It did not occur to me that the SQL for creating a procedure could differ that much from everything else, so please explain it to me in words of a few syllables as possible so I can understand.
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430510 is a reply to message #430508] Tue, 10 November 2009 15:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You need an INTO clause (and most likely a WHERE clause and EXCEPTION block).

[Updated on: Tue, 10 November 2009 15:17]

Report message to a moderator

Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430512 is a reply to message #430510] Tue, 10 November 2009 15:22 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
INTO Clause? Like Select * into Newtable from Oldtable?
Or "Fetch next into"? If so then I have to do a declare cursor etc?
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430517 is a reply to message #430512] Tue, 10 November 2009 15:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Select OrganizationID, OrganizationName
into some_var1, some_var2
From ERFO.Organizations;


You should really read the documentation for Oracle PL/SQL.
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430519 is a reply to message #430517] Tue, 10 November 2009 16:35 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
Did internet search for documentation on howto SP which led me here. I've done several hundred stored procedures over the past years for SQL Server, and DB2, and others but none for Oracle. It appears that the design philosophy behind them is essentially different to that which I am accustomed. Based on earlier entries I redesigned that stored proc as follows:

Create or Replace Procedure ERFO.yadayada
(
OrgID OUT integer,
orgname OUT varchar2(30)
)
BEGIN
SELECT OrganizationID, OrganizationName INTO OrgID, OrgName
FROM ERFO.ORGANIZATIONS;
END;

and got and error message about unexpected opening parens.
Then another site talks about ref_cursors but doesn't provide any in depth information beyond saying that procedures can return them, but not how.

So if someone could direct me to a location for the abc's hows and whys of building stored procedures as I've spent the afternoon doing this when I was assigned to design about 6 of them in that time.
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430520 is a reply to message #430519] Tue, 10 November 2009 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/PL/SQL_FAQ

http://asktom.oracle.com
above contains many fine coding examples


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/toc.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm


SQL> SET TERM ON ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
   NOW VARCHAR2(32);
BEGIN
   DBMS_OUTPUT.ENABLE(10000);
   SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') INTO NOW FROM DUAL;
   DBMS_OUTPUT.PUT_LINE(NOW);
END;
/  2    3    4    5    6    7    8  
2009-11-10 15:06:52

PL/SQL procedure successfully completed.



[Updated on: Tue, 10 November 2009 17:09]

Report message to a moderator

Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430527 is a reply to message #430519] Tue, 10 November 2009 18:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
It is not allowed to declare a size on an output parameter, so when it gets to the left parentheses around the unallowed 30 in varchar2(30) it causes the open parens error. Just use varchar2.

Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430654 is a reply to message #430519] Wed, 11 November 2009 08:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Barbara solved your error, but your next most likely error is going to be
ORA-01422: exact fetch returns more than requested number of rows
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430672 is a reply to message #358554] Wed, 11 November 2009 10:59 Go to previous messageGo to next message
joeller
Messages: 17
Registered: September 2007
Location: Indian Head, Maryland
Junior Member
Thank you Black Swan I will check out all of those sites. I have been instructed to use Oracle Data Access components for building SP's so Iwill be checking that out but I need to know how to build them without such a tool.
and you absolutely right joy_division. It appears that Output parameters can only return one record at a time while to do more you need to use a ref_cursor. whew! "What a strange new world that has such wonders in it."
Re: Encountered the symbol "end-of-file" when expecting one of the following [message #430674 is a reply to message #358554] Wed, 11 November 2009 11:44 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Parameters that are scalar datatypes can only return 1 value. There are several non-scalar datatypes - varrays and pl/sql tables for example.

That said ref cursors are often the best way to return multiple rows of data to a client program.
Previous Topic: materialized view and summary table
Next Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table (not understand)
Goto Forum:
  


Current Time: Sun Dec 04 08:25:22 CST 2016

Total time taken to generate the page: 0.12522 seconds