10 Replies Latest reply: Nov 16, 2012 8:58 AM by 643412 RSS

    problems with enclosing in ticks.

    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

      -------------------------------------------------------------------------------

      I got help recently using tick marks.
      It's complaining about the YES in the constraint towards the bottom.
      Previously, I was just using execute immediate, but was corrected to use the Qliteral...as seen here, to correct for issues with tick marks.
      I guess I still don't understand what I'm doing wrong.
      set serveroutput on;
      declare
        v_count     number;
      BEGIN
        select count(*) 
         into v_count
         from all_tables
         where table_name = 'NRV_ADMIN_REGIONS_WILLY'
         and owner = 'FS_NRIS_FSVEG';
         
         IF (v_count >= 1 )
          then dbms_output.put_line('TABLE EXISTS');  
          else
      execute immediate   --Qliteral begins here
      Q'{CREATE TABLE FS_NRIS_FSVEG.NRV_ADMIN_REGIONS_WILLY
      (
        CN           VARCHAR2(32 BYTE) CONSTRAINT NRV_ADMIN_REG_STAT_CK NOT NULL,
        REGION_ID    VARCHAR2(2 BYTE)                 NOT NULL,
        REGION_NAME  VARCHAR2(50 BYTE)                NOT NULL,
        STATUS       VARCHAR2(3 BYTE)
      )
      TABLESPACE USERS_NRIS_FSVEG
      RESULT_CACHE (MODE DEFAULT)
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
      LOGGING 
      NOCOMPRESS 
      NOCACHE
      NOPARALLEL
      MONITORING;
      
      
      CREATE UNIQUE INDEX FS_NRIS_FSVEG.NRV_ADMIN_REG_PK ON FS_NRIS_FSVEG.NRV_ADMIN_REGIONS_WILLY
      (CN)
      LOGGING
      TABLESPACE USERS_NRIS_FSVEG
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
      NOPARALLEL;
      
      
      CREATE OR REPLACE TRIGGER FS_NRIS_FSVEG.NRV_ADMIN_REGION_INS
       BEFORE INSERT
       ON FS_NRIS_FSVEG.NRV_ADMIN_REGIONS_WILLY
       REFERENCING OLD AS OLD NEW AS NEW
       FOR EACH ROW
      DECLARE
      BEGIN
         --
         --
         
         --
         if :new.cn is null
         then
            :new.cn := SYS_GUID();
         end if;
         --
      END;
      /
      
      
      ALTER TABLE FS_NRIS_FSVEG.NRV_ADMIN_REGIONS_WILLY ADD (
        CONSTRAINT NRV_ADMIN_REGION_STATUS
        CHECK (STATUS IN  ('YES','NO'))
        ENABLE VALIDATE,
        CONSTRAINT NRV_ADMIN_REG_PK
        PRIMARY KEY
        (CN)
        USING INDEX FS_NRIS_FSVEG.NRV_ADMIN_REG_PK
        ENABLE VALIDATE);
      
      GRANT DELETE, INSERT, SELECT, UPDATE ON FS_NRIS_FSVEG.NRV_ADMIN_REGIONS_WILLY TO NRV_DATA_COLLECTOR}';   --Qliteral ends here
      end if;
      EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm);
      END;
      /
        • 1. Re: problems with enclosing in ticks.
          812975
          for Yes and No you need to enter single quote twice

          ''YES'', ''NO''
          • 2. Re: problems with enclosing in ticks.
            kendenny
            I don't see a }' at the end of your quoted string. You need that.
            • 3. Re: problems with enclosing in ticks.
              JustinCave
              First, you would need a separate call to EXECUTE IMMEDIATE for each DDL statement. You'd need one for the CREATE TABLE statement, one for the CREATE INDEX statement, etc. You can't just combine a bunch of DDL statements together like this.

              Second, what, exactly, does "it's complaining" mean? I'm guessing that you are getting some sort of error. What error are you getting? What is the error stack? Are you getting a compilation error or a runtime error? Are you getting an error from the database? Or from your front end?

              Justin
              • 4. Re: problems with enclosing in ticks.
                odie_63
                First thing that comes to mind : why dynamic code? You're getting yourself problems where there shouldn't be any.

                In this case, what does PL/SQL and EXECUTE IMMEDIATE offer that you can't do with SQL?
                Just use plain SQL in a script and run it with SQL*Plus.

                If some DDLs produce errors, then they will be traced in the spool file, then the script will continue or get aborted depending on what you prefer.
                • 5. Re: problems with enclosing in ticks.
                  6363
                  odie_63 wrote:
                  First thing that comes to mind : why dynamic code? You're getting yourself problems where there shouldn't be any.
                  This has already been discussed in the OP's other thread, e.g.

                  {message:id=10694055}

                  This seems to be an attempt to meet this requirement
                  My code has to be able to be run multiple times without any kind of failure. It can ONLY write the error to the log.
                  In some weird upgrade process that seems both unusual and highly unreliable.
                  • 6. Re: problems with enclosing in ticks.
                    643412
                    SaadL wrote:
                    for Yes and No you need to enter single quote twice

                    ''YES'', ''NO''
                    Tried that...failed. As well as even doing actual quotes...failed.

                    kendenny wrote:
                    I don't see a }' at the end of your quoted string. You need that.
                    It's down there... look to the far left, it's annotated too.
                    Justin Cave wrote:
                    First, you would need a separate call to EXECUTE IMMEDIATE for each DDL statement. You'd need one for the CREATE TABLE statement, one for the CREATE INDEX statement, etc. You can't just combine a bunch of DDL statements together like this.

                    Second, what, exactly, does "it's complaining" mean? I'm guessing that you are getting some sort of error. What error are you getting? What is the error stack? Are you getting a compilation error or a runtime error? Are you getting an error from the database? Or from your front end?

                    Justin
                    You are right...my apologies....I know better than to not include details.

                    The only error I'm getting, with no details, is ORA-00911: invalid character. But it doesn't indicate any line or anything.
                    And I didn't realize that I could only put one DDL per execution. I will try that and report.


                    3360 wrote:
                    odie_63 wrote:
                    First thing that comes to mind : why dynamic code? You're getting yourself problems where there shouldn't be any.
                    This has already been discussed in the OP's other thread, e.g.

                    {message:id=10694055}

                    This seems to be an attempt to meet this requirement
                    My code has to be able to be run multiple times without any kind of failure. It can ONLY write the error to the log.
                    In some weird upgrade process that seems both unusual and highly unreliable.
                    When I have the scripts working correctly, it actually works really well. Here's why... We have multiple companies that all reside on one large oracle instance that is managed by a team of DBA's. My job as a development DBA is to process the code that comes in from the developers, and deal with them at our level. when it's time to update our application, I take all the code that they have turned in to me, along with my own adhoc requests like this, and I make one huge script. That script has to be able to be rerun. The biggest caviet is create table scripts. Because if our dev time is 8 weeks, we've built the table and have had 8 weeks of inserts and play time in the dev environment...so to just drop and recreate the table is not possible due to data loss. So my scripts have to be able to say, build the table...but if it exists, move on with no action. Views, procedures and other objects have no weight on them, as they don't store data.
                    Anyway...My packaged up script will call multiple objects. meanwhile, the dba team is getting the same thing from many teams at my level from the other companies. They have to be able to have their own script, on the night of deployment, fire off all of our scripts without any issues. So they spool the scripts to utilize their own respective log files, and upon completion ship those out to us. It makes it flow really well actually. The problem for me is that I'm not used to writing pl/sql code. Once I get these quarks figured out, it will be smooth sailing.

                    Great information. Thank you all.
                    • 7. Re: problems with enclosing in ticks.
                      6363
                      Willy_B wrote:

                      The only error I'm getting, with no details, is ORA-00911: invalid character. But it doesn't indicate any line or anything.
                      That is because of the WHEN OTHERS exception hiding code. Even if you can justify a need for it in your eventual requirements you should remove it until you are satisfied it mostly works because all it is doing right now is removing valuable information that you need to fix this.
                      • 8. Re: problems with enclosing in ticks.
                        odie_63
                        Hi Willy,
                        The problem for me is that I'm not used to writing pl/sql code.
                        Sorry for insisting on this, I still don't see a valid reason to use PL/SQL instead of a simple SQL script listing all the commands one after the other.
                        So my scripts have to be able to say, build the table...but if it exists, move on with no action.
                        OK, a SQL script can handle that. What's the problem?
                        If the table exists, then the DDL will return ORA-00955, that event will be traced in the log, and the script will proceed with the rest of the actions.


                        What you're trying to do is also error-prone IMO :

                        - What if you require string literals larger than 32k ?
                        - What if the quoting character also appears in the DDL string? Are you going to manually modify it in order to escape the character, with the risk of introducing syntax errors?

                        Edited by: odie_63 on 16 nov. 2012 15:32
                        • 9. Re: problems with enclosing in ticks.
                          avish16
                          I hope from the below logics you can anytime deal with single quotes -

                          select 'ALTER.......
                          CHECK (STATUS IN (''YES'',''NO''))
                          ENABLE VALIDATE,......
                          ENABLE VALIDATE);' from dual


                          select 'ALTER...
                          CHECK (STATUS IN ('||chr(39)||'YES'||chr(39)||','||chr(39)||'NO'||chr(39)||'))
                          ENABLE VALIDATE, .....
                          ENABLE VALIDATE);' from dual
                          • 10. Re: problems with enclosing in ticks.
                            643412
                            odie_63 wrote:
                            Hi Willy,
                            The problem for me is that I'm not used to writing pl/sql code.
                            Sorry for insisting on this, I still don't see a valid reason to use PL/SQL instead of a simple SQL script listing all the commands one after the other.
                            So my scripts have to be able to say, build the table...but if it exists, move on with no action.
                            OK, a SQL script can handle that. What's the problem?
                            If the table exists, then the DDL will return ORA-00955, that event will be traced in the log, and the script will proceed with the rest of the actions.


                            What you're trying to do is also error-prone IMO :

                            - What if you require string literals larger than 32k ?
                            - What if the quoting character also appears in the DDL string? Are you going to manually modify it in order to escape the character, with the risk of introducing syntax errors?

                            Edited by: odie_63 on 16 nov. 2012 15:32
                            ya know, I don't have an answer for you on that. Again, I'm learning this as I go, and this is how I was told it needed to be. Again, if there is a better way, then I'm listening. I was given a half baked, hand drawn road map that I'm trying to follow. I'm not against burning it and getting a GPS! LOL!

                            The crazy thing is I have bought books! (O'Reilly's pl/sql developing, Oracle Press' pl/sql developing, and one more that is actually at home right now called something like "programming in pl/sql"...) I also surf the heck out of the net and here trying to figure out why my code STILL doesn't work.

                            But in attempt to answer this as a whole, from what was explained to me, it has to be this way due to the way the upper DBA team calls the scripts.