4 Replies Latest reply on Jan 30, 2015 8:44 PM by Raj Jamadagni

    Potential bug with sqlcl

    Raj Jamadagni

      Not sure if this would be the right forum but here it is,

       

      In latest version 4.1.0 Beta, released 01/30/2015, if I run (notice table name is in lower case)

       

      SQL> set sqlformat insert
      SQL> select * from calendar where rownum < 6;
      

       

      The generated output is

      REM INSERTING into calendar
      SET DEFINE OFF;
      Insert into "calendar" (... ) values (...);
      Insert into "calendar" (... ) values (...);
      

       

      see table name is in lowercase and enclosed in double quotes, I think this is a problem. If output will be enclosed in double-quotes, it should be in uppercase, or don't double-quote it. Is this configurable?

      Also in previous version ctas command used to create the new table, in this version it doesn't. Looks like in this version it generates only a CTAS statement?  Maybe I am asking these questions prematurely.

       

      Raj

        • 1. Re: Potential bug with sqlcl
          thatJeffSmith-Oracle

          CTAS puts the CREATE into the buffer for you, so you can change it up as needed.

           

          Are you saying your table is case-sensitive or has case-sensitive column names? It looks like it is working for 'normal' table and column names when generating INSERT formatted query results.

          1 person found this helpful
          • 2. Re: Potential bug with sqlcl
            Raj Jamadagni

            my table name is in UPPERCASE as usual in the data dictionary, I am just typing it in lowercase. But I see insert statement generated as above, however column names are in UPPERCASE. I just ran a test, if i type a table name in lowercase, it comes in as "tablename" if i type table name in UPPERCASE then it appears as TABLENAME note absence of double-quotes.

             

            I am connecting against 12.1 db if it matters.

            • 3. Re: Potential bug with sqlcl
              thatJeffSmith-Oracle

              please show the DDL for your table

              • 4. Re: Re: Potential bug with sqlcl
                Raj Jamadagni

                Ok, how about an example as well...

                 

                SQL> select banner from v$version;
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
                PL/SQL Release 12.1.0.1.0 - Production
                CORE    12.1.0.1.0      Production
                TNS for Linux: Version 12.1.0.1.0 - Production
                NLSRTL Version 12.1.0.1.0 - Production
                

                 

                SQL>    CREATE TABLE "TABNAME"
                  2    (    "BPID" NUMBER NOT NULL ENABLE,
                  3          "CAL_NAME" VARCHAR2(32 CHAR) NOT NULL ENABLE,
                  4          "HOLIDAY_DATE" DATE NOT NULL ENABLE,
                  5          "DESCRIPTION" VARCHAR2(64 CHAR),
                  6          "CREATE_TS" DATE NOT NULL ENABLE,
                  7          "UPDATE_TS" DATE,
                  8          "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1696 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
                  9          CONSTRAINT "TABNAME_PK" PRIMARY KEY ("ID")
                10    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                11    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                12    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                13    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                14  ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
                15    STORAGE(
                16    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                17    PARTITION BY RANGE ("BPID") INTERVAL (1)
                18  (PARTITION "P_01"  VALUES LESS THAN (2) SEGMENT CREATION IMMEDIATE
                19    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
                20  COMPRESS FOR OLTP
                21    STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                22    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
                23    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
                
                Table "TABNAME" created.
                
                SQL> insert into tabname (bpid,cal_name,holiday_date,description,create_ts,update_ts) select col1,col2,col3,col4,col5,col6 from calendar;
                1,775 rows inserted.
                
                SQL> commit;
                Commit complete.
                
                SQL> set sqlformat insert
                SQL> select * from tabname where rownum < 6;
                
                REM INSERTING into tabname
                SET DEFINE OFF;
                Insert into "tabname" (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('25-DEC-09','DD-MON-RR HH.MI.SSXFF AM'),'Christmas',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1696);
                Insert into "tabname" (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('01-JAN-10','DD-MON-RR HH.MI.SSXFF AM'),'New Year''s Day',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1697);
                Insert into "tabname" (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('24-DEC-10','DD-MON-RR HH.MI.SSXFF AM'),'Christmas',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1698);
                Insert into "tabname" (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('22-APR-11','DD-MON-RR HH.MI.SSXFF AM'),'Good Friday',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1699);
                Insert into "tabname" (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('26-DEC-11','DD-MON-RR HH.MI.SSXFF AM'),'Christmas Day',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1700);
                
                SQL> select * from TABNAME where rownum < 6;
                
                REM INSERTING into TABNAME
                SET DEFINE OFF;
                Insert into TABNAME (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('25-DEC-09','DD-MON-RR HH.MI.SSXFF AM'),'Christmas',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1696);
                Insert into TABNAME (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('01-JAN-10','DD-MON-RR HH.MI.SSXFF AM'),'New Year''s Day',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1697);
                Insert into TABNAME (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('24-DEC-10','DD-MON-RR HH.MI.SSXFF AM'),'Christmas',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1698);
                Insert into TABNAME (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('22-APR-11','DD-MON-RR HH.MI.SSXFF AM'),'Good Friday',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1699);
                Insert into TABNAME (BPID,CAL_NAME,HOLIDAY_DATE,DESCRIPTION,CREATE_TS,UPDATE_TS,ID) values (54,'Custom Calendar',to_timestamp('26-DEC-11','DD-MON-RR HH.MI.SSXFF AM'),'Christmas Day',to_timestamp('11-JAN-13','DD-MON-RR HH.MI.SSXFF AM'),null,1700);
                
                SQL>
                

                 

                Hope this helps