8 Replies Latest reply: Dec 5, 2012 8:04 AM by 643412 RSS

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

    643412
      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
          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
            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
              <sigh> ....Thanks. Frustration is giving me blind tunnel vision. Sorry to waste your time.
              • 4. Re: Still fighting this...have tried ALL recommendations...
                BluShadow
                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
                  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
                    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
                      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
                        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!