4 Replies Latest reply: Jan 1, 2013 10:33 PM by Muthu RSS

    SQL Error while running......

    Muthu
      Hello all,

      I am using R12.1.3.

      I have ran the insert query in GL_interface Table from my client system. I have attached the Insert query as below.

      insert into GL_INTERFACE (
      STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
      ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
      SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
      ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID
      )
      values
      ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 1000.00, 0.00, 1122),
      ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 500.00, 0.00, 1122),
      ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '000', '1110', '0000', '000', 'A', 1500.00, 0.00, 1122);

      While running the above query, its giving the below error in SQL Developer.

      An error was encountered performing the requested operation:

      ORA-00933: SQL command not properly ended

      *00933.00000 - "SQL command not properly ended"*
      **Cause:*
      **Action:*
      Vendor code 933Error at line:8 Column:215

      Please guide me what could be the issue.

      Thanks and Regards,
      Muthukumar
      upmuthukumar@gmail.com
        • 1. Re: SQL Error while running......
          Hussein Sawwan-Oracle
          Muthu wrote:
          Hello all,

          I am using R12.1.3.

          I have ran the insert query in GL_interface Table from my client system. I have attached the Insert query as below.

          insert into GL_INTERFACE (
          STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
          ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
          SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
          ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID
          )
          values
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 1000.00, 0.00, 1122),
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 500.00, 0.00, 1122),
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '000', '1110', '0000', '000', 'A', 1500.00, 0.00, 1122);

          While running the above query, its giving the below error in SQL Developer.

          An error was encountered performing the requested operation:

          ORA-00933: SQL command not properly ended

          *00933.00000 - "SQL command not properly ended"*
          **Cause:*
          **Action:*
          Vendor code 933Error at line:8 Column:215

          Please guide me what could be the issue.

          Thanks and Regards,
          Muthukumar
          upmuthukumar@gmail.com
          Change the above to the following:
          SQL> insert into GL_INTERFACE (
          STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
          ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
          SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
          ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
          values 
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 1000.00, 0.00, 1122);
          
          insert into GL_INTERFACE (
          STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
          ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
          SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
          ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
          values 
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 500.00, 0.00, 1122);
          
          insert into GL_INTERFACE (
          STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
          ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
          SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
          ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
          values 
          ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '000', '1110', '0000', '000', 'A', 1500.00, 0.00, 1122);
          Thanks,
          Hussein
          • 2. Re: SQL Error while running......
            Muthu
            Hi Hussein,

            Thank you for your update.

            I am trying to insert multiple row in a single update query.

            I found the syn from the below link.

            http://www.dbforums.com/oracle/1628974-multiple-row-insert-one-sql-stmt.html

            INSERT INTO BookTable
            (
            BookId , Title , Description
            )
            VALUES
            (
            101, "Moby Dick", "Whale book";
            101, "Beaches", "Chick book";
            101, "From Good to Great", "Boss book";
            101, "C#", "Nerd book"
            )

            Is it possible to update multiple row in a single query like above?

            Thanks and Regards,
            Muthu
            • 3. Re: SQL Error while running......
              Hussein Sawwan-Oracle
              Muthu,

              Please try this:
              SQL> insert all 
              into GL_INTERFACE (STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
              ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
              SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
              ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
              values ( 'NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 1000.00, 0.00, 1122)
              into GL_INTERFACE (STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
              ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
              SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
              ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
              values ('NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '830', '5300', '0000', '000', 'A', 500.00, 0.00, 1122)
              into GL_INTERFACE (STATUS, LEDGER_ID, REFERENCE1, REFERENCE4, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME,
              ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
              SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
              ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID)
              values ('NEW', 1, 'TEST_Batch2','TEST_Journal2', 'Payroll', 'Payroll', to_date('DEC-22-2008','MON-DD-YYYY'), 'USD', to_date('DEC-23-2008','MON-DD-YYYY'), 1092, '01', '000', '1110', '0000', '000', 'A', 1500.00, 0.00, 1122)
              select * from dual;
              http://www.techonthenet.com/oracle/questions/insert_rows.php

              Thanks,
              Hussein
              • 4. Re: SQL Error while running......
                Muthu
                Hi Hussein,

                Thank you very much for your help, and Wish you very Happy new year to you and your family.

                My clients are giving the journal detail through txt file, So that I tried this format.

                Please tell me the below format will work for my requirement...

                Script:-
                --------
                options (skip=1)
                load data
                infile 'D:\flat.txt'
                into table GL_INTERFACE
                fields terminated by "," optionally enclosed by '"'
                ( STATUS, LEDGER_ID, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
                SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID )

                Datafile:-
                ----------

                "STATUS","LEDGER_ID","USER_JE_SOURCE_NAME","USER_JE_CATEGORY_NAME","ACCOUNTING_DATE","CURRENCY_CODE","DATE_CREATED","CREATED_BY","SEGMENT1","SEGMENT2","SEGMENT3","SEGMENT4","SEGMENT5","ACTUAL_FLAG","ENTERED_DR","ENTERED_CR","GROUP_ID"
                NEW,1,Payroll,Payroll,to_date(DEC-22-2008,MON-DD-YYYY),USD,to_date(DEC-23-2008,MON-DD-YYYY),1092,01,830,5300,0000,000,A,1000.00,0.00,12345
                NEW,1,Payroll,Payroll,to_date(DEC-22-2008,MON-DD-YYYY),USD,to_date(DEC-23-2008,MON-DD-YYYY),1092,01,000,1110,0000,000,A,0.00,1000.00,12345


                Please help me to move forward...

                Thanks and regards,
                Muthu