Re: Inserting binary data to an column

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 08 Nov 2007 10:03:16 -0800
Message-ID: <1194544991.855206@bubbleator.drizzle.com>

Micha wrote:

> 
> "DA Morgan" <damorgan_at_psoug.org> schrieb im Newsbeitrag 
> news:1194470031.8469_at_bubbleator.drizzle.com...
>> Micha wrote:


>>>> http://www.psoug.org/reference/convert_func.html#chtr

>>>> http://www.psoug.org/reference/dbms_xdbutil_int.html#xinh

>>>> http://www.psoug.org/reference/convert_func.html#crth

>>>> http://www.psoug.org/reference/utl_raw.html#urctr

>>>> http://www.psoug.org/reference/builtin_functions.html

>>>> http://www.psoug.org/reference/dbms_reputil.html#rurv

>>>> -- 

>>>> Daniel A. Morgan

>>>> University of Washington

>>>> damorgan_at_x.washington.edu (replace x with u to respond)

>>>> Puget Sound Oracle Users Group
>>>> www.psoug.org
>>>
>>> Thnx o lot, this will help a little bit.
>>> But im not able to bring this in an correct way^^.
>>> Lets make an example:
>>> 1.in a table (table_a) exists a column (col_a) of type anydata
>>> 2.the data in anydata is an object (lets make a simple object (obj_a)
>>> with 2 itmes 1st_name (of varchar2) and 2nd (phonenumber (of number))
>>> 3. in the destination db is obj_a already defined
>>>
>>> if i understand it right, i can get the raw presentation of col_a
>>> (with the obj_a) with utl_raw.cast_to_raw.
>>>
>>> Now i want to create an INSERT statement that should the following:
>>> 1. insert the raw data in col_b from table_b (will have the same
>>> definition as table_a and col_a, but will be in another db)
>>> 2. the data in col_b should not be the raw data. it must represent
>>> the original obj_a as in table_a.
>>>
>>> So there must be a "recast"? or anything that the destination db
>>> recognize the raw data as the obj_a.
>>>
>>> My problem is, that i will realize this independent from the types in
>>> the anydata field.
>>> Surely, i can step through the anydata field and findout the type and
>>> type the cast and recast (eg utl_raw.cast_to_varchar2 and
>>> utl_raw.cast_to_raw.
>>>
>>>
>>> But i will find a way to transmitt an binary representation of the
>>> data, that will later (in the destination db) recognize as the
>>> original data.
>>>
>>> How can i do this?
>>>
>>> Thnx for ur help
>>>
>>> Micha

>>
>> Let me see if I get this correctly. You want to use the anydata
>> data type to store a user defined data type? Why?
>>
>> Take what you want to store, binary, hex, octal, who cares and
>> create a UDT like this:
>>
>> TYPE my_udt
>> what_i_am_storing    BLOB
>> what_i_want_it_to_be VARCHAR2
>>
>> And then just reconstitute it whenever you want where-ever you want.
>> -- 
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
> 
> But then must be the column you want to store the my_udt of typ my_udt.
> I havnt choose it so, but it ist so. there is an anydata typed clolumn 
> and the types therein can be different from dataset to dataset. Dont 
> matter if its normal varchar, number or even user defined types.
> I must find a easily way to export and import the data as i have told.
> 
> Micha

The UDT I described can be used to create a column. Don't over-complicate a simple task.

create type my_udt AS OBJECT (
data_store BLOB,
data_type VARCHAR2(20));
/

SQL> create table t (

   2 testcol my_udt);

Table created.

SQL> desc t
  Name Null? Type
------- -------- ------
TESTCOL MY_UDT SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 08 2007 - 12:03:16 CST