Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    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.
    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 14:16.

  2. #2
    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

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    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:
    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


    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!!!

  4. #4
    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.


    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!!!

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    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)...

    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!!

  6. #6
    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

  7. #7
    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.

  8. #8
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    thanks everyone, it works now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •