6 Replies Latest reply on Dec 12, 2018 5:11 PM by thatJeffSmith-Oracle

    Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)

    Marian_

      Hello,

       

      I have attempted to export rows from query with BLOB columns and developer generated insert command looks like this

       

      Insert into my_table (ID,blob_column) values (55,TO_BLOB(HEXTORAW('626567696E2030207575656E636F64652E6275660D0A6C34244C23212130202140202820202020283021212D5E2A20464C32'))

      || TO_BLOB(HEXTORAW('2E3C40242020203025202020332020402236542D4F3B4731453B47313F3527455039372D3D2B47414D3B22232B462030220D'))

      || TO_BLOB(HEXTORAW('0A6C2A2C2A402020282020202020202020202020202020202020202020202020202020202020202020202020202020202020'))

      ..

      || TO_BLOB(HEXTORAW('2050202025223420202020200D0A0A656E64')));

       

      But insert failed with error

      ERROR at line 1:

      ORA-00932: inconsistent datatypes: expected CHAR got BLOB

       

      The problem is probably with concatenating TO_BLOB - I think this is not allowed.

      Or exists any other explanation for this error?

      Thanks.

       

      Marian

        • 1. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)

          I have attempted to export rows from query with BLOB columns and developer generated insert command looks like this

          What 'query' are you talking about?

           

          The problem is probably with concatenating TO_BLOB - I think this is not allowed.

          Yes - but there is no need to 'think this is not allowed'.

           

          The FIRST thing to do to learn about Oracle functionality is RTFM. Did you read the doc about the concatenation operator?

           

          I found it easily using a simple search for 'oracle 12c concatenating'

          https://docs.oracle.com/database/121/SQLRF/operators003.htm#SQLRF51158

          Concatenation Operator

          The concatenation operator manipulates character strings and CLOB

          data.

          See where it says 'character strings and CLOB data'? You don't have a character string or CLOB data.

          ORA-00932: inconsistent datatypes: expected CHAR got BLOB

          See where your exception message says 'expected CHAR got BLOB'?

           

          Are you suggesting the SQL Developer, on its own, created that concatenation?

           

          Or is is really your query that is trying to do it?

           

          If you need more help than the above then SHOW US:

           

          1. WHAT you do

          2. HOW you do it

          3. WHAT results you get

          • 2. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)
            Marian_

            You should read in more detail :-)

             

            I have exported rows using INSERT format  (as I wrote)export.png

             

            SQL Developer generated this commands

            Insert into my_table (ID,blob_column) values (55,TO_BLOB(HEXTORAW('626567696E2030207575656E636F64652E6275660D0A6C34244C23212130202140202820202020283021212D5E2A20464C32'))

            || TO_BLOB(HEXTORAW('2E3C40242020203025202020332020402236542D4F3B4731453B47313F3527455039372D3D2B47414D3B22232B462030220D'))

            || TO_BLOB(HEXTORAW('0A6C2A2C2A402020282020202020202020202020202020202020202020202020202020202020202020202020202020202020'))

            ..

            || TO_BLOB(HEXTORAW('2050202025223420202020200D0A0A656E64')));

             

            But this command failed with error

            ORA-00932: inconsistent datatypes: expected CHAR got BLOB

             

            concatenating in this way is not allowed, see example

            create table test (c1 number, c2 blob);

            Table TEST created.

            insert into test values (1, TO_BLOB(HEXTORAW('626567696E')));

            1 row inserted.

            insert into test values (1, TO_BLOB(HEXTORAW('626567696E')) || TO_BLOB(HEXTORAW('626567696E')));

            ERROR at line 1:

            ORA-00932: inconsistent datatypes: expected CHAR got BLOB

             

            so, it looks like SQL Developer bug

             

            But today strange thing happened.

            Export using insert format generate insert commands WITHOUT blob columns, but yesterday generated command like above.

            I have no explanation for this behaviour.

             

            Marian

            • 3. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)
              thatJeffSmith-Oracle

              Generally if you want to export data with BLOBs the recommended ways are using the Loader format or use Data Pump

               

              I talk about the SQL*Loader method here

              https://www.thatjeffsmith.com/archive/2014/05/exporting-multiple-blobs-with-oracle-sql-developer/

               

              So basically just like this

              • 4. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)
                Marian_

                Jeff,

                 

                thanks for the reply!

                I know the export using loader format, but for smaller BLOBs would be nice to have choice generate sql insert commands.

                Why developer yesterday generated insert with the BLOBs columns and today no more?

                Is there any setup? I have tried to find any configuration but I haven't succeeded.

                 

                Marian

                • 5. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)

                  You should read in more detail

                  Hmmm - maybe YOU should try to understand that we can NOT see your screen or what you do.

                   

                  There are several ways to do an export from sql developer. You also mentioned a query but had not posted it.

                   

                  I notice in your reply you also didn't post the query or ALL of the steps you took.

                   

                  We can ONLY reply based on what you post. You CLEARLY have the info that was ask for and it is EASY to capture and paste the images as you showed in your last reply.

                   

                  That is why you were ask to do this:

                  If you need more help than the above then SHOW US:

                   

                  1. WHAT you do

                  2. HOW you do it

                  3. WHAT results you get

                  When you do that we can then SEE for ourselves which export option you tried to use, what configuration choices you made and what result you got.

                   

                  That enables us to try to reproduce the problem. We can't reproduce anything at all without that info.

                   

                  Doesn't see like too much to ask of someone seeking help.

                  • 6. Re: Developer 17.4.1.054 generates incorerrect insert commands (BLOB column)
                    thatJeffSmith-Oracle

                    No idea...our export formatters generally ingnore blob columns