Hi Tom,

Thanks a lot for your prompt response. Is it possible to update a Long Raw column with a BLOB value? I read some where that that is possible by converting the binary bytes into hex but I am not sure if that is possible or how to do it. Any suggestions is highly appreciated.

Hi Tom,

I have tables x and y as

create table x
(
id number,
str varchar2(255),
lrimage long_raw
);

create table y
(
id number,
str varchar2(255),
blimage blob
);

Table x has about 11 million rows and I am trying to move data from table x to table y.

I tried the following:

create or replace procedure procblob
as
tstr varchar2(255);
timage blob;

begin
..........
select str, to_lob(lrimage) into tstr, timage
from x where id = 1;
insert into y values (1, tstr, timage);

.....
end;
/

When I try to compile this procedure I am getting the error
PL/SQL: ORA-00932: inconsistent datatypes: expected - got BINARY

I also tried

create or replace procedure procblob
as
tstr varchar2(255);
timage long raw;

begin
..........
select str, lrimage into tstr, timage
from x where id = 1;
insert into y values (1, tstr, to_lob(timage));

.....
end;
/

Again I am getting the same error. Please let me know what to do.

Thanks.

Thank you Tom it works great.

Hi Tom,

is there a difference for 9.2.0.6.?

I have a table

blobdata(
ownerid number(10),
longid number(10),
segmentid number(10),
segment LONG RAW
)

But a Query

Select longid, to_lob(segment) from blobdata where longid = 12345

returns

ORA-00932: inconsistent datatypes: expected - got BINARY



Then I create table
blobdata_temp(
ownerid NUMBER(10),
longid NUMBER(10),
segmentid NUMBER(10),
segment BLOB
)

and tried

INSERT INTO blobdata_temp SELECT ownerid, longid, segmentid, to_lob(segment) from blobdata where ...

All I got is

ORA-00997: illegal use of LONG datatype

I really has no idea what's going wrong...

Would be great if you could help me...

Thanks,

Roland

to_lob only works on insert as select.


give me a cut and pasted example. something like this from my 9206 database:

ops$tkyte@ORA9IR2> create table blobdata(
  2  ownerid         number(10),
  3  longid         number(10),
  4  segmentid     number(10),
  5  segment         LONG RAW
  6  );

Table created.

ops$tkyte@ORA9IR2> insert into blobdata values ( 1, 2, 3, rpad( '00', 4000, '0' ) );

1 row created.

ops$tkyte@ORA9IR2> declare
  2          l_raw long raw;
  3  begin
  4          l_raw := rpad( '00', 32766, '0' );
  5          insert into blobdata values ( 1, 2, 3, l_raw );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table blobdata_temp(
  2  ownerid         NUMBER(10),
  3  longid        NUMBER(10),
  4  segmentid        NUMBER(10),
  5  segment         BLOB
  6  );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> INSERT INTO blobdata_temp
  2  SELECT ownerid, longid, segmentid, to_lob(segment)
  3    from blobdata;

2 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dbms_lob.getlength( segment) from blobdata_temp;

DBMS_LOB.GETLENGTH(SEGMENT)
---------------------------
                       2000
                      16383

ops$tkyte@ORA9IR2>

 

Hi Tom,
I have been visiting your site so many times, but it is only this time I am taking the chance to make a question regarding the convertion of Long Raw to BLOB.
I am in the process of loading some 40(GB)pictures from a MSSQL system to Oracle 10g R2 using OLEDB though heterogeneus system(HS).

This is my target table in oracle:

create table T_PICTURES
(
IMAGE_ID VARCHAR2(10) not null,
FILENAME VARCHAR2(250),
ACTIONNR NUMBER,
PICTURE BLOB,
BYTES VARCHAR2(10),
INFO VARCHAR2(10),
IMAGEDATE DATE
);

alter table T_PICTURES
add constraint XPK_PICTURES primary key (IMAGE_ID));


And this is the description from sqlplus of the remote table in MSSQL.

image_id varchar2(8)
filename varchar2(250) y
actionnr number(10) y
picture long raw y
bytes number(10) y
info number(10) y
imagedate date y

The following is a code I wrote and it is suposed to migrate a record at a time, any time it is being executed and converting the LONG RAW picture field to BLOB using the TO_LOB function.


begin
for c in (select t.appno
from stg_passports t, t_pictures p
where t.appno = p.image_id(+)
and p.image_id is null
and rownum <= 1 )
loop

insert into t_pictures
(image_id, filename, actionnr, picture, bytes, info, imagedate)
SELECT "ImageID" image_id,
"FileName" filename,
"ActionNr" actionnr,
to_lob("Picture") picture,
"Bytes" bytes,
"Info" info,
"ImageDate" imagedate
FROM ITCPortrait@mssql
WHERE "ImageID" = c.appno;
commit;
end loop;
--
end;

When I try to run it, I am getting ORA-00997: illegal use of LONG datatype
ORA-06512: at line 10

Am I doing something wrong here? I am realy confused. I'd really appreciate your intervention on this. I hope I was somehow clear in my explanations.

Thanks and regards,
Visi

Hi Tom,
Sorry for the confusion. By MSSQL I ment Microsoft SQL Server not MYSQL.

However, I think I was able to overcome the issue by introducing a middle step in the procedure. Instead of using an INSERT...INTO clause with a TO_LOB function, I used a record type Variable as a helper.
This is the final code of the procedure:

create or replace procedure p_load_picture
as
type TPicData is record(
image_id varchar2(8),
filename varchar2(250),
actionnr number(10),
picture long raw,
bytes number(10),
info number(10),
imagedate date);
--
v_data TPicData;
--
begin
for c in (select t.appno
from stg_passports t, t_pictures p
where t.appno = p.image_id(+)
and p.image_id is null
and rownum <= 1)
loop
SELECT "ImageID" image_id,
"FileName" filename,
"ActionNr" actionnr,
"Picture" picture,
"Bytes" bytes,
"Info" info,
"ImageDate" imagedate
into v_data
FROM ITCPortrait@mssql
where "ImageID" = c.appno;
--
insert into t_pictures
(image_id, filename, actionnr, picture, bytes, info, imagedate)
values
(v_data.image_id,
v_data.filename,
v_data.actionnr,
v_data.picture,
v_data.bytes,
v_data.info,
v_data.imagedate);
--
end loop;
--
commit;
--
end;

Amazingly, Oracle is implicitly converting the LONG RAW data type to BLOB data type, so at the end we don't need to call a TO_LOB function at all.

Note: The LOOP might look unnecessary there but probably I am going to increase the number of records loaded in a single run.

Thanks again Tom

Regards,
Visi

Hi Tom,
The avarege size of a picture is about 10KB.The largest foto so far looks to be around 20KB. Probably I was lucky :)

Thanks for letting me know.

Regards,
Visi

Would you help me, I want to insert doc or pdf file into oracle database from oracle developer 6i.

Thanks
Anis

Hi Tom,

I have a table t with a LOB column into which i tried the below query,

insert into t select to_lob(text) from user_views;

and this worked.

But when I tried to execute only

select to_lob(text) from user_views;

I got the below error

SQL Error: ORA-00932: inconsistent datatypes: expected - got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Could you please help me understand the reason for this behaviour.

Thanks in advance
Badri

Can you explain to us the difference between BLOB and RAW datatype and for what kind of data you use RAW data type.

INSERT INTO APAC2.PS_VENDOR_CONVER (SELECT DISTINCT 'MASTR', A.VENDOR_ID,
A.CONVER_DT, A.CONVER_SEQ_NUM, A.CNTCT_SEQ_NUM, A.CONVER_TOPIC,
A.OPRID, A.REVIEW_DAYS, A.REVIEW_DATE, A.REVIEW_NEXT_DATE,
A.KEYWORD1, A.KEYWORD2, A.KEYWORD3, TO_LOB (A.DESCRLONG) FROM
APAC2.PS_C_NR_VDCNVR_TMP A)

A.DESCRLONG is a LONG field, so I am using TO_LOB

Hi Tom

I need help here wrt LONG field. In the SELECT I have to use a DISTINCT becos of functionality and I have to use the Long Field as well.
A.DESCRLONG is a LONG field.
But this is showing me an error that it is a wrong data type.
Can you help me in resolving the sql insert please

Thanks

select .... existing select list....
  from table
 where rowid in (select max(rowid) from table group by <that list of columns>);

Hi,
I created a one table in that i created one column with LONG RAW datatype and i want to migrate the data to another table but in that column datatype is BLOB.
I want to know is it possible or not.If yes how?

when i tried to compile, i got below.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Advanced Analytics and Real Application Testing options


Warning: Java created with compilation errors.

Errors for JAVA SOURCE "BlobArray":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 BlobArray:12: oracle.jdbc.driver.OracleResultSet is not public in
oracle.jdbc.driver; cannot be accessed from outside package

0/0 OracleResultSet rs = (OracleResultSet) blobs.getResultSet();
0/0 ^
0/0 BlobArray:12: oracle.jdbc.driver.OracleResultSet is not public in
oracle.jdbc.driver; cannot be accessed from outside package

0/0 2 errors
0/0 ^
0/0 Note: BlobArray uses or overrides a deprecated API.

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 Note: Recompile with -Xlint:deprecation for details.
0/0 OracleResultSet rs = (OracleResultSet) blobs.getResultSet();

Warning: Java created with compilation errors.

SQL> l 12
12* conn.setAutoCommit(false);
SQL> l
1 create or replace and resolve java source named "BlobArray" as
2 import java.io.*;
3 import java.lang.*;
4 import oracle.sql.*;
5 import oracle.jdbc.driver.*;
6 import java.sql.*;
7
8 public class BlobArray {
9 public static void doWork(oracle.sql.ARRAY blobs) throws Exception {
10 DriverManager.registerDriver(new OracleDriver());
11 Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:@");
12 conn.setAutoCommit(false);
13 OracleResultSet rs = (OracleResultSet) blobs.getResultSet();
14 PrintStream os = new PrintStream(System.out);
15
16 for (int i = 0; rs.next(); i++) {
17 BLOB b = rs.getBLOB(2);
18 int length = (int) b.length();
19 int bufferSize = b.getBufferSize();
20 byte[] buffer = new byte[bufferSize];
21 InputStream is = b.getBinaryStream();
22
23 while ((length = is.read (buffer,0,bufferSize)) != -1) {
24 os.write(buffer, 0, length);
25 }
26
27 os.println();
28 os.println("===========================");
29 }
30
31 os.println();
32 os.println("JAVA PART IS DONE");
33 os.flush();
34 }
35* }
SQL>

Please help

SQL> create table t ( x long raw );

Table created.

SQL> insert into t values ( utl_raw.cast_to_raw('qweqweqweqwe'));

1 row created.

SQL> alter table t modify x blob;

Table altered.

SQL> desc t
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------
 X                                                                                BLOB

SQL> select x from t;

X
----------------------------------------------------------------------------------------
717765717765717765717765