Oracle Image to SQL Server

[stephenk1973]

I am trying to DT an Oracele 8i table to SQL server.

The oracle table includes images of type ORDSYS.ORDIMAGE. When i try this i get unspecified error.

Is this possible? I suspect they're just not campatiable.

Any suggestions much appreciated.

Thanks

Stephen

 

[dbomrrsm]

You would need to find out what data type ORDSYS.ORDIMAGE is in the oracle DB.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[stephenk1973]

The image is stored as a jpeg. Where do i go from there?

 

[dbomrrsm]

as the oracle dba what the field data type is - if you can establish this then I can tell you what data type it will need to be in sql server. Although you say it is a jpeg is it the actual image that is stored or just a path to the image.

If you can establish these details I am sure I can help.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[stephenk1973]

As far as i can tell the image is ORDSYS.ORDIMAGE

SQL> desc images
Name Null? Type
IMAGE_NUM NOT NULL NUMBER(10)
ENTITY NOT NULL VARCHAR2(3)
IMAGE_TYPE NOT NULL VARCHAR2(20)
IMAGE ORDSYS.ORDIMAGE
NEW_IMAGE VARCHAR2(1)
THUMBNAIL ORDSYS.ORDIMAGE
IN_USER VARCHAR2(15)
UP_USER VARCHAR2(15)

 

[dbomrrsm]

when you desc images it tells you that the image and thumbnail are ORDSYS.ORDIMAGE types - that is the same type as ORDIMAGE field in ORDSYS table.

Can you dec ORDSYS and post back the data type of ORDIMAGE field.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[stephenk1973]

I have run a query in Oracle Qquery Analyser and the field is coming back as BLOB.

 

[dbomrrsm]

so it is a binary large object - you can use the binary data type when designing this table for the field the BLOB is going to go into - an interesting discussion though as to whether to store the image in the DB or just a path to the image held elsewhere - see

http://www.webmasterworld.com/forum47/1850.htm

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[stephenk1973]

I'm afraid i cannot acces your link.

I have created a table in SQL server, two columns, image_id (numeric,9) and StyImage (have tried Bunary and Image). When i try to create a query with DTS to transform into those fields i get "ADO error, ORA 01024, invalid datatype in OCI call"

Any ideas?

 

[dbomrrsm]

http://www.csee.umbc.edu/help/oracle8/server.815/a67785/e900.htm

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[stephenk1973]

Cause: An OCI program call specified an invalid datatype. In OCI calls, Oracle datatypes are specified as numbers between 1 and 7.

Action: Check the datatype description and enter the correct number for the datatype

Where do i set this??