This discussion is archived
5 Replies Latest reply: Nov 23, 2012 12:36 PM by 946257 RSS

Difference in syntax between SQL Developer and TOAD

973721 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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