1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error(2,2): pls-00103: encountered the symbol " " when expecting one of the following: return

Discussion in 'SQL PL/SQL' started by sania, Oct 17, 2018.

  1. sania

    sania Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    Hi Friends,

    I am getting the following error while trying to execute below code
    - Error(2,2): PLS-00103: Encountered the symbol " " when expecting one of the following: return


    Code (SQL):
    SET define off;
    CREATE OR REPLACE FUNCTION text_fn
    (pstr IN CLOB)
    RETURN  CLOB
    AS
      vstrin CLOB := pstr;
      vstringout CLOB;
    BEGIN
      vstrout:=vstrin;
      IF INSTR( vstrout,'&') > 0 THEN
      BEGIN
         vstrout := REPLACE (vstrout,'&','&');
     
         vstrout := REPLACE (vstrout,'"','"');
         vstrout := REPLACE (vstrout,'&lt;','<');
     
         vstrout := REPLACE (vstrout,'&gt;','>');
       END;
      IF INSTR( vstrout,'<') > 0 THEN
      BEGIN
        vstrout:=regexp_replace( vstrout,'<.*?>') ;
      END;
      RETURN vstrout;
    END text;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    1) Why you use anonymous pl/sql block in IF-statement?
    2) This line "IF INSTR( vstrout,'&') " is contain not escaped char
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That's not the error generated when I run it:

    Errors for FUNCTION TEXT_FN:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    23/5 PLS-00103: Encountered the symbol "TEXT" when expecting one of
    the following:
    if

    The name in the final END statement doesn't match the function name. Changing that generates:

    Errors for FUNCTION TEXT_FN:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    23/5 PLS-00103: Encountered the symbol "TEXT_FN" when expecting one of
    the following:
    if

    You have not terminated your IF blocks correctly with END IF; so I added those. Then I generated the following errors:

    Errors for FUNCTION TEXT_FN:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/3 PL/SQL: Statement ignored
    8/3 PLS-00201: identifier 'VSTROUT' must be declared
    9/3 PL/SQL: Statement ignored
    9/13 PLS-00201: identifier 'VSTROUT' must be declared
    17/3 PL/SQL: Statement ignored
    17/13 PLS-00201: identifier 'VSTROUT' must be declared
    22/3 PL/SQL: Statement ignored
    22/10 PLS-00201: identifier 'VSTROUT' must be declared

    Your variable names don't match the declared variable so I fixed THAT. The final code is:

    Code (SQL):
    CREATE OR REPLACE FUNCTION text_fn
    (pstr IN CLOB)
    RETURN  CLOB
    AS
      vstrin CLOB := pstr;
      vstrout CLOB;
    BEGIN
      vstrout:=vstrin;
      IF INSTR( vstrout,'&') > 0 THEN
      BEGIN
         vstrout := REPLACE (vstrout,'&amp;','&');
         vstrout := REPLACE (vstrout,'&quot;','"');
         vstrout := REPLACE (vstrout,'&lt;','<');
         vstrout := REPLACE (vstrout,'&gt;','>');
       END;
      END IF;
      IF INSTR( vstrout,'<') > 0 THEN
      BEGIN
        vstrout:=regexp_replace( vstrout,'<.*?>') ;
      END;
      END IF;
      RETURN vstrout;
    END text_fn;
    /
     
     
  4. sania

    sania Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    THANKYOU

    THANKYOU :) :)