PLS-00103 When providing parameter in Oracle cursor

Question

Have been searching for a while, but I am not able spot the cause of the following error when passing a parameter in the cursor inside the function:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/7     PLS-00103: Encountered the symbol "WHERE" when expecting one of
     the following:
     begin function pragma procedure subtype type <an identifier>
     <a double-quoted delimited-identifier> current cursor delete
     exists prior

I have been checking the structure of the code with no luck. This is the complete sql code:

CREATE TABLE subs (
   subs_list VARCHAR(500)
)
INSERT INTO subs (subs_list)
VALUES(('MOUNTAIN, MOUNT, MT, MTN'));

INSERT INTO subs (subs_list)
VALUES(('VIEW, VU'));

CREATE TABLE subs (
subs_list VARCHAR(500)
)
INSERT INTO subs (subs_list)
VALUES(('MOUNTAIN, MOUNT, MT, MTN'));

INSERT INTO subs (subs_list)
VALUES(('VIEW, VU'));


    SQL> CREATE OR REPLACE FUNCTION replace_synonyms(text IN VARCHAR2)
  2      RETURN VARCHAR2
  3  IS
  4      p_text VARCHAR2(4000) := text;
  5
  6      CURSOR c_subs
  7      IS
  8        SELECT subs_list
  9        FROM subs
 10        WHERE subs_list LIKE '%' ||p_text|| '%';
 11  BEGIN
 12     FOR i IN c_subs
 13      LOOP
 14        dbms_output.put_line(i.subs_list);
 15      END LOOP;
 16
 17     RETURN p_text;
 18  END;
 19  /

score 2 · Accepted Answer · answered Nov 22 '19 at 01:49

While your INSERT statements have a ; at then end, the CREATE TABLE statements do not. Check all the preceding statements have completed successfully.

CREATE TABLE subs (subs_list VARCHAR(500));

Table created.

INSERT INTO subs (subs_list)  VALUES(('MOUNTAIN, MOUNT, MT, MTN'));

1 row created.

INSERT INTO subs (subs_list) VALUES(('VIEW, VU'));

1 row created.

CREATE OR REPLACE FUNCTION replace_synonyms(text IN VARCHAR2)
RETURN VARCHAR2
IS
    p_text VARCHAR2(4000) := text;

    CURSOR c_subs
    IS
      SELECT subs_list
      FROM subs
      WHERE subs_list LIKE '%' ||p_text|| '%';
BEGIN
   FOR i IN c_subs
    LOOP
      dbms_output.put_line(i.subs_list);
    END LOOP;

   RETURN p_text;
END;
/

Function created.

PLS-00103 When providing parameter in Oracle cursor

1 Answers1