Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dbms_sql.bind_variable to insert long blob data

678557Jun 1 2011 — edited Jun 2 2011
Hello,

I am trying to insert long hex string into blob column. As it does not allow to do that using plain sql statement due to 4000 limit of characters. I found one way of doing that given as below.
declare
  l_cursor number := dbms_sql.open_cursor;
  l_ignore number;
begin
  dbms_sql.parse( l_cursor, 'INSERT INTO table1 VALUES (:id, :name, :data, :type)', dbms_sql.native );
  dbms_sql.bind_variable( l_cursor, ':id', 154);
  dbms_sql.bind_variable( l_cursor, ':name', 'name');
  dbms_sql.bind_variable_raw( l_cursor, ':data', '0001000000FFFFFFFF.........');--this is creating problem
  dbms_sql.bind_variable( l_cursor, ':type', 0);
  l_ignore := dbms_sql.execute( l_cursor );  
  dbms_sql.close_cursor( l_cursor );
end;
But I am getting below error while executing the block in SQL Developer.
Error report:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1202
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at line 10
01460. 00000 -  "unimplemented or unreasonable conversion requested"
*Cause:    
*Action:
Can any one guide me resolving this problem? I appreciate your inputs.

Thanks,
Jignesh
This post has been answered by Billy Verreynne on Jun 2 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 30 2011
Added on Jun 1 2011
5 comments
1,859 views