Thanks for the question, jing.
Asked: March 25, 2014 - 9:55 pm UTC
Last updated: March 28, 2014 - 6:48 am UTC
Version: 11.2.0
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
I have this table 'attachments' in SQL Server 2005. It has a column 'attachment_file' with varbinary(max) as its datatype. It stores pdf files and most of them are larger than 32k. I only have select privilege in the SQL Server database.
create table attachments
(
record_id int,
attachment_file varbinary(max)
);
I need to convert the whole table to Oracle 11g. I've tried the following things so far:
1. Create a table in Oracle using 'create as select' through the dblink. I got error: ORA-00997: illegal use of LONG datatype.
2. Create an empty table in Oracle with a BLOB field, and use to_lob() for the 'attachment_file' field, got error: ORA-00997: illegal use of LONG datatype
CREATE TABLE TEST_BLOB
(
B_BLOB BLOB,
RECORD_ID NUMBER(8)
);
INSERT INTO TEST_BLOB (B_BLOB, RECORD_ID)
SELECT TO_LOB("attachment_file"), "record_id"
FROM attachments@SQLSERVERDB;
3. Create a view in Oracle instead of a table. It let me do that but the 'attachment_file' field in the view is LONG RAW so I'm restricted to 32k of data.
Could you please tell me what I should do here? Thank you very much.
and Tom said...
I'm going to suggest using the migration features found in SQL Developer, it can be used to reverse engineer schemas, reproduce those schemas, move data over, even convert stored procedures. I wrote to one of the developers and they said:
<quote>
Ok, so Varbinary(max):
For SQL Server, we do the following in SQLDeveloper Migrations.
In Online mode, we convert varbinary(max)-> blob or you can choose raw for named precisions
In offline mode, I have not checked this lately, but blobs are usually the way we go as varbinaries are hex encoded by bcp.
In Copy to Oracle (automapped jdbc types), we use blobs if the size is over the max for raw, which is 32k now in 12, so Varbinary(MAX) will go to blob. Copy to Oracle is available in SQL Developer if you right click on a table in a third party connection like SQL Server.
If this is a casual use, I'd tell them to use copy to oracle from the navigator in SQLDeveloper
</quote>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment