How to insert value using case when and to_lob

User_7H0N0

I Have 2 Table which this schema.

I want to insert value form table TEST_1 to TEST_2 and I want to check type of column TEST_RAW in table 1 to convert datatype.

I use this query but I found error Error: ORA-00932: inconsistent datatypes: expected - got LONG BINARY

What should I do?

Frank Kulash

Hi, @User_7H0N0

For the tables you posted, use

INSERT INTO test_2 (name, test_blob)
       SELECT name, TO_LOB (test_raw)
       FROM  test_1;

The error was caused by trying to make the CASE expression return a LONG RAW sometimes, and a BLOB at other times. That's not allowed. A CASE expression should always return the same data type: all THEN and ELSE keywords should be followed by an expression in the same data tape.

In the future, please don't post images for the tables or your query. Post them as plain text so that the people who want to help you can easily copy them to run on their own systems.

Frank Kulash

Hi, @User_7H0N0

For the tables you posted, use

INSERT INTO test_2 (name, test_blob)
       SELECT name, TO_LOB (test_raw)
       FROM  test_1;

The error was caused by trying to make the CASE expression return a LONG RAW sometimes, and a BLOB at other times. That's not allowed. A CASE expression should always return the same data type: all THEN and ELSE keywords should be followed by an expression in the same data tape.

In the future, please don't post images for the tables or your query. Post them as plain text so that the people who want to help you can easily copy them to run on their own systems.

User_7H0N0

@Frank Kulash

Thank you. Is there any way that can return both blob and long blob?

Frank Kulash

Hi, @User_7H0N0

Thank you. Is there any way that can return both blob and long blob?

Aren't BLOBs long enough? Why do you need "long blob"?

Perhaps you meant LONG RAW, not "long blob". The same CASE expression can't return a BLOB in some situations and a LONG RAW in other situations: it must always return the same data type. You can't say  THEN x ELSE y   where x and y are different data types.

In the problem you posted, you don't need a CASE expression at all. If you have another problem where you do need a CASE expression, and you want would like it to return different data types, then start another thread, and explain what you need to do.

mathguy

Trying to understand why you were using that CASE expression in the first place. Was it because you didn't know the data type of the column in the source table? So that, when inserting into the second table, you wanted to convert to BLOB if the input was LONG RAW - but it was also possible that the column was BLOB already, in which case you didn't need to convert anything?

That would make sense (as a problem, not as a solution !)- but why would you not know the data type of the input column before you write the query? Are you trying to write something generic, that would work for similar tasks on various tables?