Oracle: LONG RAW to BLOB - ORA-00932: inconsistent datatypes

Question

I'm trying to convert a LONG RAW value into a BLOB one and I'm getting an error: ORA-00932: inconsistent datatypes: expected BINARY, got LONG BINARY. Code sample(document is a LONG RAW column):

DECLARE 
  var_blob BLOB;
BEGIN
  select To_Blob(document) into var_blob
    from instructions
    where id = 'XXX';
END;

I get the same error if I try to execute the code as a simple SQL query (without the PL/SQL code). What am I doing wrong?

Edit:

Using the information from the answers I tried doing:

create table temp_blob(id VARCHAR2(50), file_contents blob);

and then:

DECLARE
  z_id varchar(50) := 'XXX';
  z_blob blob;
BEGIN
 execute immediate '
  insert into temp_blob
  select :z_id, To_Blob(document)
   from instructions
   where id = :z_id' using z_id, z_id;

 begin
   select file_contents into z_blob
   from temp_blob where id = z_id;
 end;
END;

I'm still getting the same error. Also some additional information - the size of the result of the query:

select document
from instructions
where id = 'XXX';

is bigger than 32760 bytes, so I cannot assign it to a PL/SQL variable.

Artem Yurchenko Artem Yurchenko · Accepted Answer · 2017-05-12T12:41:09

You can get answer from Tom Kyte's blog. He's already answered here

Edited:

We should use TO_LOB instead of TO_BLOB.

Let's check it out:

create two tables :

create table instructions (id varchar2(50), document long raw);
create table temp_blob(id VARCHAR2(50), file_contents blob);

populate INSTRUCTIONS with data that exceeds 32767 bytes (to be sure that no implicit conversion to pl/sql varchar2 occurs) in DOCUMENT column. For this purpose I use this small solution (in C#):

using System;
using System.Data;
using Oracle.DataAccess.Client;

namespace LongRaw
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection connection = new OracleConnection("Data Source=XE;User ID=<user_name>;Password=<user_pass>;Pooling=yes;"))
            {
                connection.Open();
                using (OracleCommand command = new OracleCommand("insert into instructions values (:id, :document)", connection))
                {
                    Random random = new Random();
                    byte[] document = new byte[65535];

                    random.NextBytes(document);

                    command.Parameters.Add("id", "XXX");
                    command.Parameters.Add("document", OracleDbType.LongRaw, 65535, val: document, dir: ParameterDirection.Input);
                    command.ExecuteNonQuery();
                }

                connection.Close();
            }
        }
    }
}

now we have data in INSTRUCTIONS and execute our pl/sql-block:

SQL> set echo on
SQL> set serveroutput on
SQL> 
declare
    z_id varchar(50) := 'XXX';
    z_blob blob;
begin
    execute immediate
    'insert into temp_blob
     select :z_id, to_lob(document)
     from   instructions
     where  id = :z_id' using z_id, z_id;
     select file_contents into z_blob
     from temp_blob where id = z_id;
     dbms_output.put_line(dbms_lob.getlength(z_blob) || ' bytes');
end;
/
65535 bytes
PL/SQL procedure successfully completed

SQL>

And here we are - our z_blob contains 65535 bytes :)

Oracle: LONG RAW to BLOB - ORA-00932: inconsistent datatypes

2 Answers