1 2 Previous Next 18 Replies Latest reply on Oct 17, 2018 9:25 PM by Glen Conway

    SQL Developer Bug v 18.2.0.183

    user7038478

      Seems like a pretty straight-forward piece of functionality has been broken or impacted with the 18.2 upgrade. I am now getting an issue when using the table "IMPORT DATA" functionality and see this error. I restored my environment back to 17.4 to get it working again. Basically, importing raw data files to an oracle table is no longer working using sql developer 18.2.

       

      Error:

      --Insert failed for row  1

      --operation not allowed: operation cannot be mixed with Oracle-style batchin

      --Row 1

       

      This repeats for all CSV (or XLSX) records.

       

      Thanks for looking into.

        • 1. Re: SQL Developer Bug v 18.2.0.183
          Glen Conway

          You should provide a test case, or at least elaborate on your problem.

          1. Does it involve a specific data type [does "importing raw data files" mean the data contain RAW columns?]?

          2. What is the Oracle version where the target table resides.

           

          The more detail you give, the better.

          • 2. Re: SQL Developer Bug v 18.2.0.183
            user7038478

            Hi Glen,

            Thanks for your email. Here is the scenario.

             

            1) Create a temp table:

             

            CREATE TABLE "your schema name"."TEST1" ( "ID" VARCHAR2(20 BYTE),"NAME" VARCHAR2(20 BYTE), "TYPE" VARCHAR2(20 BYTE)); commit;

             

            Next we want to create a .TXT dataset that has 1 header and 3 sample records:

             

            ID NAME TYPE

            1 abc xxx

            2 def yyy

            3 ghi zzz

             

            2) Next log into SQL developer using the GUI. On the left-hand side of the tool, browse to your schema and your newly created TEST1 table on the left-hand side.

            Once you found it, right-click the table and choose "Import Data".

            Point to your .TXT dataset, then click NEXT.

            Press NEXT again.

            Again, press NEXT.

            Verify source and target columns are mapped, then press NEXT again.

            Click FINISH.

             

            Error:

            Insert failed for row  1

            operation not allowed: operation cannot be mixed with Oracle-style batchin

            Do you want to ignore all errors?

            Click yes to continue and ignore all errors.

            Click no to continue and prompt on error.

            Click cancel to cancel and rollback.

             

            NOTE: if you manually insert a record, it is fine but the flat file import method is not working. Example: INSERT INTO TEST1 (ID, NAME, TYPE) VALUES ('1','abc','xxx');

             

            We are running Oracle SQL Developer client ver 18.2.0.183.

             

            DB Version:

              SELECT * FROM V$VERSION -> Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

             

            Thanks!

            • 3. Re: SQL Developer Bug v 18.2.0.183
              Glen Conway

              This test case is obviously both quite simple and common. I got thrown off-track by misinterpreting your "raw data files" comment. If there were any bug in SQL Developer the whole world would have complained about it by now.

               

              If I run the test case on 11g XE or 12.2 (I don't have 12.1 available) it works fine, although I do switch the default "Delimiter" on Step 1 of the Data Import wizard from "tab" to "space" (or "whitespace")

              Capture.JPG

              so the default column "Match By Name" on Step 4 is set properly

              Capture.JPG

               

              But if that is not working for you, and the result is always "operation not allowed: operation cannot be mixed with Oracle-style batching", I can only suggest deleting and re-installing 18.2  -- something very strange is going on in your environment.

              • 4. Re: SQL Developer Bug v 18.2.0.183
                thatJeffSmith-Oracle

                What do you mean by this?

                 

                NOTE: if you manually insert a record, it is fine but the flat file import method is not working. Example: INSERT INTO TEST1 (ID, NAME, TYPE) VALUES ('1','abc','xxx');

                 

                I was going to suggest trying the INSERT SCRIPT method, which will give you INSERT statements to run instead of us doing the inserts for you. Is that what you've tried? If it's not 'working', can you show what it is actually doing?

                • 5. Re: SQL Developer Bug v 18.2.0.183
                  user7038478

                  Hi Jeff,

                  Thank you for your comment. The point of my note was to communicate that scripting the insert statement vs using the SQL Develop GUI (meaning, the end user right-clicks the table in the left-hand side table navigation window to import the file method) is not producing the same results. All I know is that when I download the 17.4 on my windows 10 machine and compare that to the 18.2 version, this functionality is lost. It is strange that I have two install directories (v17.4 and v18.2) on my c:\ drive and they operate differently.

                   

                  Appreciate your help with this.

                  • 6. Re: SQL Developer Bug v 18.2.0.183
                    vstrale-JavaNet

                    I am getting the same error on Import Data from Excel.

                     

                    SQL Developer: 18.2.0.183

                    Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

                    • 7. Re: SQL Developer Bug v 18.2.0.183
                      user564385

                      I have the same problem importing a csv.  I install version 18.1.0.095 in diferent folder and it's working in that version (18.1.0.095).

                      • 8. Re: SQL Developer Bug v 18.2.0.183
                        Glen Conway

                        Since the original test case provided by user7038478 works for some of us but not others, but without anyone providing full details on the user environment and SQL Developer configuration, we are a bit stuck here.

                         

                        The message "operation cannot be mixed with Oracle-style batching" is a programming error.  Possibly a SQL Developer user can influence behavior in this area is through the preference

                        Database -> Utilities -> Import -> Import Methods -> Insert -> Insert Batch Size (Rows)

                        CAPTURE.jpg

                        I tried values of 1 (my original setting), 0, 10, and the max value (2147483647).  The first 3 values worked fine for me when importing those 3 rows of data from a csv file.  However setting the max value causes the import to fail and rollback the task:

                        Import Data into table TEST1 from file C:\Users\xxxxx\export.csv . Task failed and import rolled back.

                         

                        So check your value for Insert Batch Size.  Even if looks OK, maybe there is some other corruption in the user settings.

                         

                        Cheers

                        • 9. Re: SQL Developer Bug v 18.2.0.183
                          heidisarge

                          For me, it resolved when I switched my instant client from 11.2 to 12.2 in the advance database settings.

                          • 10. Re: SQL Developer Bug v 18.2.0.183
                            CristianoAguiar

                            Thank you for remember me I am using instant client.

                            I switched from 11.2 to 18.3 and now it's working.

                            • 11. Re: SQL Developer Bug v 18.2.0.183
                              Glen Conway

                              I recall reading somewhere, for some prior SQL Developer release, that the minimum required JDBC driver version was 11.2.0.3.  I just retested the OP's test case using the 11.2.0.4 Oracle Instant Client, for both the Thin and OCI/Thick driver cases, and in fact the import does fail:

                              Capture.png

                              I guess this means the release notes / installation notes should be updated to warn about that.

                              • 12. Re: SQL Developer Bug v 18.2.0.183
                                thatJeffSmith-Oracle

                                It should work, if it's not working with an 11204 client, it's a bug, please report to My Oracle Support.

                                • 13. Re: SQL Developer Bug v 18.2.0.183
                                  Sam_P

                                  This is definitely a bug.....a simple file import operation is failing no matter what options I tweak. This used to work just fine in prior versions. Hope it gets fixed in v18.3.

                                  1 2 Previous Next