This discussion is archived
8 Replies Latest reply: Dec 5, 2012 8:04 AM by 643412 RSS

Still fighting this...have tried ALL recommendations...

643412 Newbie
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Man I hate buggin people about the same issue, but it seems that I'm missing something, as I keep fighting the same deamon...TICKS!

I'm trying to compile a trigger:
alter session set current_schema = FS_NRIS_FSVEG;

BEGIN execute immediate
Q'{CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
   BEFORE INSERT
   ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
BEGIN
   IF :new.version_cn IS NULL
   THEN
      :new.version_cn := SYS_GUID();
   END IF;

   IF    :new.application_name IS NULL
      OR :new.client_version IS NULL
      OR :new.arc_version IS NULL
   THEN
      Raise_Application_Error (
         -20000,
         '||chr(39)||'ERROR: A Spatial version can not be entered if any of the following fields are n
ot populated (Application_Name, Client_Version, Arc_Version)'||chr(39)||');
   END IF;
END}';
END:
/
I get the following error
BEGIN execute immediate
Q'{CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
   BEFORE INSERT
   ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
BEGIN
   IF :new.version_cn IS NULL
   THEN
      :new.version_cn := SYS_GUID();
   END IF;

   IF    :new.application_name IS NULL
      OR :new.client_version IS NULL
      OR :new.arc_version IS NULL
   THEN
      Raise_Application_Error (
         -20000,
         '||chr(39)||'ERROR: A Spatial version can not be entered if any of the following fields are n
ot populated (Application_Name, Client_Version, Arc_Version)'||chr(39)||');
   END IF;
END}';
END:
/
Error at line 3
ORA-06550: line 24, column 4:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

   ; <an identifier> <a double-quoted delimited-identifier>
The symbol "; was inserted before ":" to continue.
{code}


If I take it out of the block of pl/sql and just run it as the straight sql, I STILL get the following error:

{code}
alter session set current_schema = FS_NRIS_FSVEG;

CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
   BEFORE INSERT
   ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
BEGIN
   IF :new.version_cn IS NULL
   THEN
      :new.version_cn := SYS_GUID();
   END IF;

   IF    :new.application_name IS NULL
      OR :new.client_version IS NULL
      OR :new.arc_version IS NULL
   THEN
      Raise_Application_Error (
         -20000,
         'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)';
   END IF;
END;

{code}
[Warning] ORA-24344: success with compilation error
14/152  PLS-00103: Encountered the symbol ";" when expecting one of the following:
          ) , * & = - + < / > at in is mod remainder not rem =>
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset
          The symbol ")" was substituted for ";" to continue.
 (3: 0): Warning: compiled but with compilation errors
{code}

I've tried every recommendation from the other thread about problems with ticks....and it STILL doesn't work.
Can someone please tell me what's wrong here and maybe explain the mentality, or principle of ticks?  OR recommend something that explains it solidly?  I'm so sick of fighting these!  
TODAY is code cut off, and I'm so far behind from fighting these, I'm facing possibly missing my deadline.

I understand that most of you don't agree with the methodology of our code standard.  However, it's our standard and I have to stick to it...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 1. Re: Still fighting this...have tried ALL recommendations...
    BluShadow Guru Moderator
    Currently Being Moderated
    In your second one you're missing a bracket on the end of the raise_application_error call...
          Raise_Application_Error (
             -20000,
             'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)';
          Raise_Application_Error (
             -20000,
             'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)');
  • 2. Re: Still fighting this...have tried ALL recommendations...
    BluShadow Guru Moderator
    Currently Being Moderated
    In your first one you have a ":" after your END statement instead of ";"
    END:
    END;
  • 3. Re: Still fighting this...have tried ALL recommendations...
    643412 Newbie
    Currently Being Moderated
    <sigh> ....Thanks. Frustration is giving me blind tunnel vision. Sorry to waste your time.
  • 4. Re: Still fighting this...have tried ALL recommendations...
    BluShadow Guru Moderator
    Currently Being Moderated
    Not sure why you want to create a trigger dynamically, but if you ever do dynamic code it's wise to just assign it to a string and then output that string to check what statement is going to be executed...
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_sql varchar2(32767);
      3  BEGIN
      4  v_sql :=
      5  Q'{CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
      6     BEFORE INSERT
      7     ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
      8     REFERENCING OLD AS OLD NEW AS NEW
      9     FOR EACH ROW
     10  DECLARE
     11  BEGIN
     12     IF :new.version_cn IS NULL
     13     THEN
     14        :new.version_cn := SYS_GUID();
     15     END IF;
     16     IF    :new.application_name IS NULL
     17        OR :new.client_version IS NULL
     18        OR :new.arc_version IS NULL
     19     THEN
     20        Raise_Application_Error (
     21           -20000,
     22           '||chr(39)||'ERROR: A Spatial version can not be entered if any of the following fields are n
     23  ot populated (Application_Name, Client_Version, Arc_Version)'||chr(39)||');
     24     END IF;
     25  END}';
     26    dbms_output.put_line(v_sql);
     27* END;
    SQL> /
    CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
       BEFORE INSERT
       ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
       REFERENCING OLD AS OLD NEW AS NEW
       FOR EACH ROW
    DECLARE
    BEGIN
       IF :new.version_cn IS NULL
       THEN
          :new.version_cn :=
    SYS_GUID();
       END IF;
       IF    :new.application_name IS NULL
          OR :new.client_version IS NULL
          OR :new.arc_version IS NULL
       THEN
          Raise_Application_Error (
             -20000,
             '||chr(39)||'ERROR: A Spatial version can not be
    entered if any of the following fields are n
    ot populated (Application_Name, Client_Version, Arc_Version)'||chr(39)||');
       END IF;
    END
    
    PL/SQL procedure successfully completed.
    this shows you are missing the ";" after the END statement for the trigger, as well as the ")" after the raise_application_error statement.
  • 5. Re: Still fighting this...have tried ALL recommendations...
    BluShadow Guru Moderator
    Currently Being Moderated
    I'm guessing you want this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_sql varchar2(32767);
      3  BEGIN
      4  v_sql :=
      5  Q'{CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
      6     BEFORE INSERT
      7     ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
      8     REFERENCING OLD AS OLD NEW AS NEW
      9     FOR EACH ROW
     10  DECLARE
     11  BEGIN
     12     IF :new.version_cn IS NULL
     13     THEN
     14        :new.version_cn := SYS_GUID();
     15     END IF;
     16     IF    :new.application_name IS NULL
     17        OR :new.client_version IS NULL
     18        OR :new.arc_version IS NULL
     19     THEN
     20        Raise_Application_Error (
     21           -20000,
     22           'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)');
     23     END IF;
     24  END;}';
     25    dbms_output.put_line(v_sql);
     26* END;
    SQL> /
    CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
       BEFORE INSERT
       ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
       REFERENCING OLD AS OLD NEW AS NEW
       FOR EACH ROW
    DECLARE
    BEGIN
       IF :new.version_cn IS NULL
       THEN
          :new.version_cn :=
    SYS_GUID();
       END IF;
       IF    :new.application_name IS NULL
          OR :new.client_version IS NULL
          OR :new.arc_version IS NULL
       THEN
          Raise_Application_Error (
             -20000,
             'ERROR: A Spatial version can not be entered if any
    of the following fields are not populated (Application_Name, Client_Version, Arc_Version)');
       END IF;
    END;
    
    PL/SQL procedure successfully completed.
  • 6. Re: Still fighting this...have tried ALL recommendations...
    Paul Horth Expert
    Currently Being Moderated
    It's nothing to do with 'ticks' or single quotes as most people call them.

    Your first problem is to do with
    END:
    near the end. Should be
    END;
    of course. Funny how the error message is telling you exactly that.

    The second problem is to do with a missing bracket:
    Raise_Application_Error (
             -20000,
             'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)';
    Should be
    Raise_Application_Error (
             -20000,
             'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)');
    Again indicated by the error message.

    So, it seems the problem is to do with other things besides single quotes.
  • 7. Re: Still fighting this...have tried ALL recommendations...
    Paul Horth Expert
    Currently Being Moderated
    Paul  Horth wrote:
    It's nothing to do with 'ticks' or single quotes as most people call them.

    Your first problem is to do with
    END:
    near the end. Should be
    END;
    of course. Funny how the error message is telling you exactly that.

    The second problem is to do with a missing bracket:
    Raise_Application_Error (
    -20000,
    'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)';
    Should be
    Raise_Application_Error (
    -20000,
    'ERROR: A Spatial version can not be entered if any of the following fields are not populated (Application_Name, Client_Version, Arc_Version)');
    Again indicated by the error message.

    So, it seems the problem is to do with other things besides single quotes.
    You're too fast for me, BluShadow. :-)
  • 8. Re: Still fighting this...have tried ALL recommendations...
    643412 Newbie
    Currently Being Moderated
    BluShadow wrote:
    Not sure why you want to create a trigger dynamically, but if you ever do dynamic code it's wise to just assign it to a string and then output that string to check what statement is going to be executed...
    SQL> ed
    Wrote file afiedt.buf
    
    1  declare
    2    v_sql varchar2(32767);
    3  BEGIN
    4  v_sql :=
    5  Q'{CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ARC_VER_VER_CN
    6     BEFORE INSERT
    7     ON FS_NRIS_FSVEG.NRV_SPATIAL_ARC_VERSIONS
    8     REFERENCING OLD AS OLD NEW AS NEW
    .
    .
    .
    I don't know why that 'explained' things so well, but that clicked for me. THANK YOU!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points