If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > PL/SQL Cursors:- Error PLS-00103

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Super Moderator
 
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
Question PL/SQL Cursors:- Error PLS-00103

Code:
is
	ignore boolean;

CURSOR c_test IS
		SELECT name
		FROM fyp.location
		WHERE name like "ho%";
		
BEGIN
  DECLARE
    v_name       location.name%TYPE;
    v_count      BINARY_INTEGER:=0;
	OPEN c_test; 
	LOOP    
		FETCH c_test INTO v_name; 
		EXIT WHEN c_test%NOT_FOUND;
		v_count := v_count + 1;
	END LOOP;
	CLOSE c_test;
END;
The above code, which is pretty simple (!) doesn't work. Giving the errors on lines 15, 19 and 20. All errors are PLS-00103.
Quote:
Line # = 15 Column # = 3 Error Text = PLS-00103: Encountered the symbol "FETCH" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar
Line # = 19 Column # = 2 Error Text = PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: begin function package pragma procedure form
Line # = 20 Column # = 4 Error Text = PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor
I am using Oracle Release 9.0.1.0.1 if that makes any difference. Any help would be much aprechiated, as I am trying to learn this stuff and this is one of the first things I wrote (so it's probably a really obvious mistake!!! )

Last edited by rhs98; 05-01-03 at 13:16.
Reply With Quote
  #2 (permalink)  
Old
Drunkard
 
Join Date: Nov 2002
Location: Desk, slightly south of keyboard
Posts: 697
Hi,

The declare appears to be in the wrong place... try

DECLARE
CURSOR c_test IS
SELECT name
FROM fyp.location
WHERE name like "ho%";
v_name fyp.location.name%TYPE;
v_count BINARY_INTEGER:=0;

BEGIN
OPEN c_test;
LOOP
FETCH c_test INTO v_name;
EXIT WHEN c_test%NOT_FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE c_test;
END;

You should also consider closing the cursor in the event of an exception - it's good practice.... to do so change the last two lines to

CLOSE c_test;
EXCEPTION WHEN OTHERS THEN
IF c_test%ISOPEN THEN
CLOSE c_test;
END;
END;

HTH
Bill
Reply With Quote
  #3 (permalink)  
Old
Super Moderator
 
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
Code:
CREATE OR REPLACE  PROCEDURE "FYP"."TEST" is
DECLARE
CURSOR c_test IS
SELECT name
FROM fyp.location
WHERE name like "ho%";
v_name fyp.location.name%TYPE;
v_count BINARY_INTEGER:=0;

BEGIN
OPEN c_test; 
LOOP 
FETCH c_test INTO v_name;
EXIT WHEN c_test%NOT_FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE c_test;
END;
causes:
Quote:
Line # = 2 Column # = 1 Error Text = PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language The symbol "begin" was substituted for "DECLARE" to continue.
Line # = 18 Column # = 4 Error Text = 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> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe
It still don't work!!! ARRRG


Quote:
You should also consider closing the cursor in the event of an exception - it's good practice.... to do so change the last two lines to...
Yeah I will do, but think I should get it to compile first!!!
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: NY
Posts: 208
When using the Create or Replace syntax, you can not use Declare. Declare is only for anonymous blocks that are not named. So either remove line 1 and create an anonymous block, or remove line 2 and create a named procedure.


Quote:
Originally posted by rhs98
Code:
CREATE OR REPLACE  PROCEDURE "FYP"."TEST" is
DECLARE
CURSOR c_test IS
SELECT name
FROM fyp.location
WHERE name like "ho%";
v_name fyp.location.name%TYPE;
v_count BINARY_INTEGER:=0;

BEGIN
OPEN c_test; 
LOOP 
FETCH c_test INTO v_name;
EXIT WHEN c_test%NOT_FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE c_test;
END;
causes:


It still don't work!!! ARRRG



Yeah I will do, but think I should get it to compile first!!!
Reply With Quote
  #5 (permalink)  
Old
Super Moderator
 
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
Quote:
Originally posted by carloa
When using the Create or Replace syntax, you can not use Declare. Declare is only for anonymous blocks that are not named. So either remove line 1 and create an anonymous block, or remove line 2 and create a named procedure.
That just creates even more errors (I went the route of removing the DECLARE line)...

Quote:
Line # = 3 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
Line # = 5 Column # = 17 Error Text = PL/SQL: ORA-00904: invalid column name
Line # = 13 Column # = 18 Error Text = PLS-00208: identifier 'NOT_FOUND' is not a legal cursor attribute
Line # = 13 Column # = 1 Error Text = PL/SQL: Statement ignored
I really wanna know what is wrong. This should be a really simple task. Is it my version of Oracle??

Can anyone suggest what is wrong???

Failing that: Can someone give me a simple example of cursor processing on the Scott example stuff? i.e. one that counts records or something!!
Reply With Quote
  #6 (permalink)  
Old
Drunkard
 
Join Date: Nov 2002
Location: Desk, slightly south of keyboard
Posts: 697
Hi Russell,

This is an anonymous block (ie not procedure or function) which works. This is on an 8.1.7 db.

declare
cursor cfred is select null from dual;
rfred cfred%rowtype;
begin
open cfred;
loop
fetch cfred into rfred;
exit when cfred%notfound;
end loop;
close cfred;
end;

If you still have no joy, try saving your script as a .SQL file and posting it here.

HTH
Bill
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: NY
Posts: 208
I see a couple errors in the code itself. When comparing data in Oracle you place a literal in Single Quotes. Where Name like 'ho%';

Oracle is case sensitive, so take that into consideration when comparing literals.

The cursor attribute you want to check is NOTFOUND.
EXIT WHEN c_test%NOTFOUND;
Remove the underscore you have in NOT_FOUND.

Your code should look like this.
CREATE OR REPLACE PROCEDURE FYP.TEST
IS

CURSOR c_test IS
SELECT name
FROM fyp.location
WHERE name like 'ho%';

v_name fyp.location.name%TYPE;
v_count BINARY_INTEGER:=0;

BEGIN
OPEN c_test;
LOOP
FETCH c_test INTO v_name;
EXIT WHEN c_test%NOT_FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE c_test;
END;

Keep in mind you will want to catch any exceptions and close the cursor if it is still open as mentioned in a previous post. In addition, this procedure will run, but you won't see any output. You could either pass an OUT parameter, or use a DBMS_OUTPUT.PUT_LINE to display your counter to the screen.
Reply With Quote
  #8 (permalink)  
Old
Super Moderator
 
Join Date: Feb 2002
Location: Hampshire, UK
Posts: 441
thanks everyone, it works now!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On