5 Replies Latest reply: Nov 23, 2012 2:36 PM by 946257 RSS

    Difference in syntax between SQL Developer and TOAD

    973721
      Hello there,

      Appreciate your input on the below matter:

      - I have tried running an sql script in TOAD and in SQL Developer, both yielded different results. Running the INSERT script in SQL Developer, I managed to get a success response, whereby the table was created. However, when running the INSERT script in TOAD, an error "ORA-00917: missing comma" was encountered.
      - I have verified the comma syntax and the command was written correctly.

      Has anyone encountered this before? Appreciate your comments on this.

      How would a query differ in both these applications?

      Thanks and have a great day!
        • 1. Re: Difference in syntax between SQL Developer and TOAD
          Marwim
          Hello 970718,

          welcome to the forum.

          Without the statement we can only guess what the problem might be. Most likely you have a number or date in your insert statement, that does not match the NLS-settings of your tool. Compare the settings with the following select:
          SELECT *
          FROM nls_session_parameters
          WHERE parameter IN ('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT');
          Those are the two most likely different settings. You can also omit the WHERE clause to compare the other settings.

          In case it's the date, then your values should not depend on imlicit conversion. Instead of
          INSERT INTO xy (date_column) VALUES ('01.01.2012');
          you should write
          INSERT INTO xy (date_column) VALUES (TO_DATE('01.01.2012','dd.mm.yyyy'));
          Regards
          Marcus

          Edited by: Marwim on 12.11.2012 09:25

          P.S.: Does the insert fail when executed in SQL*PLUS?
          • 2. Re: Difference in syntax between SQL Developer and TOAD
            973721
            Hi Marcus,

            Thanks for the reply.

            The INSERT statement which I was trying to execute is as follows:

            -----
            Insert into AUTOMATION_RULE (NAMESPACE,TASK_MNEMONIC,SUCCESS_STATUS,EXTERNAL_UPDATE_REQUIRED,CORR_ID,EVENT_NAME,EXT_SYS,TECH_EXCEPTION_STATUS,FUNC_EXCEPTION_STATUS,RETURNED_STATUS,EXECUTION_MODE,UNDO_TASK_MNEMONIC,CUSTOM_ERROR_HANDLING,CUSTOM_ERROR_KEY_PATH,ADDED_ERROR_INFO,ORDER_LEVEL,SERVICE_TYPE,CANCELLED_STATUS)
            values ('TM_NonConsumer','DD_INS_SWF_Send_Booking_Info','finish','N','concat(//OrderID,"-",//OrderHistID)','L3SendBookingInfoSWIFT','SWIFT','technical_exception','functional_exception','returned_task','do','null','null','null','null',null,'null','finish');
            -----

            The statement above was failing in TOAD. I have not tried it on SQL* PLUS.
            It was executed successfully on Oracle SQL Developer though.

            Hope to hear from you soon. Thanks!

            Regards,
            Adrian
            • 3. Re: Difference in syntax between SQL Developer and TOAD
              Jeff Smith Sqldev Pm-Oracle
              Let us know if the SQL*Plus behavior differs from SQL Developer's.

              My guess is that the discrepancy you see lies somewhere near here
              'concat(//OrderID,"-",//OrderHistID)',
              • 4. Re: Difference in syntax between SQL Developer and TOAD
                Marwim
                INSERT
                INTO AUTOMATION_RULE
                    (
                        NAMESPACE
                      ,TASK_MNEMONIC
                      ,SUCCESS_STATUS
                      ,EXTERNAL_UPDATE_REQUIRED
                      ,CORR_ID
                      ,EVENT_NAME
                      ,EXT_SYS
                      ,TECH_EXCEPTION_STATUS
                      ,FUNC_EXCEPTION_STATUS
                      ,RETURNED_STATUS
                      ,EXECUTION_MODE
                      ,UNDO_TASK_MNEMONIC
                      ,CUSTOM_ERROR_HANDLING
                      ,CUSTOM_ERROR_KEY_PATH
                      ,ADDED_ERROR_INFO
                      ,ORDER_LEVEL
                      ,SERVICE_TYPE
                      ,CANCELLED_STATUS
                    )
                    VALUES
                    (
                        'TM_NonConsumer'
                      ,'DD_INS_SWF_Send_Booking_Info'
                      ,'finish'
                      ,'N'
                      ,'concat(//OrderID,"-",//OrderHistID)'
                      ,'L3SendBookingInfoSWIFT'
                      ,'SWIFT'
                      ,'technical_exception'
                      ,'functional_exception'
                      ,'returned_task'
                      ,'do'
                      ,'null'
                      ,'null'
                      ,'null'
                      ,'null'
                      ,NULL
                      ,'null'
                      ,'finish'
                    );
                +(Code formatted with SQL Developer)+

                I cannot see anything wrong. Still you should execute it in SQL*PLUS. When it runs without an error you should post in a TOAD forum.

                Regards
                Marcus
                • 5. Re: Difference in syntax between SQL Developer and TOAD
                  946257
                  i faced a similar problem,
                  i was trying to insert into the table from xml using the toad. The date column didn't insert properly.
                  I used the SQL developer to insert the data and it went fine.
                  wondering how it changes from one to another.