Java Oracle Blob to Blob comparisons failling using PreparedStatement statement

Question

I try to compare string inside blob saved as byte array to string in SQL query from what i understand i need to convert the value to Blob and then compare Blob and Blob but im getting error

String s = "SELECT * FROM TEST VAL like ?";
Blob blob = conn.createBlob();
blob.setBytes(1, ((String)"yes").getBytes("UTF-8"));
PreparedStatement p = conn.prepareStatement(s);
p.setBlob(1,blob);
p.executeUpdate();

but getting exception error

java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'CAST_TO_VARCHAR2'

Well, where is the definition to CAST_TO_VARCHAR2 and the code that calls it? Also the "Select" query you have is incorrect - missing "FROM" — OldProgrammer
– OldProgrammer, Commented Nov 22, 2017 at 15:12
Why are you performing this cast: (String)"yes"? It's redundant. — cdaiga
– cdaiga, Commented Nov 22, 2017 at 15:16

krokodilko · Accepted Answer · 2017-11-22 18:04:47Z

1

The first mistake is that the statement is a SELECT statement :

String s = "SELECT * FROM TEST VAL like ?";

but you are trying to call p.executeUpdate();
According to the documentation of: PreparedStatement#ecexuteUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

that is - this method is not intended for execution of SELECT statement. Only INSERT, DELETE, UPDATE or an SQL that returns nothins - SELECT returs a resultset.

Another mistake is that according to the documentation of LIKE operator their arguments can be only of the following datatypes:

char1 LIKE char2 [ ESCAPE asc_chars ]
All of the character expressions (char1, char2, and esc_char) can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. If they differ, then Oracle converts all of them to the data type of char1.

As you see, BLOB datatype are not alloved here as parameter. BLOB datatype was used as a parameter of LIKE operator --> p.setBlob(1,blob);, therefore the error was thrown: ORA-06553: PLS-306: wrong number or types of arguments in call to 'CAST_TO_VARCHAR2' because Oracle tried to cast BLOB to VARCHAR2 datatype, but this cast is not allowed.

answered Nov 22, 2017 at 18:04

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user63898 Over a year ago

Thanks , so if i change the query to VAL = ? and replace it to executeQuery() is should work ? somwhow ? or there is no way to compare Blob to Blob ?

Collectives™ on Stack Overflow

Java Oracle Blob to Blob comparisons failling using PreparedStatement statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related