Inserting binary data to an column

> *REAL* programmers use COPY CON to create Windows apps.
> http://www.kyphilom.com/www/comtags.html
>
> If you are able to insert any data independent from its data type, what do
> you think you gain except scratching your database? Why bother with
> inserts anyway, maybe, write hex stream directly into datafiles could be
> worth to consider ?
>
> Seriously, your *concept* seems fully cranky to me, you've better spent
> your time with learning database concepts, it would probably give you an
> immediate gain instead an imaginary.
>
> Best regards
>
> Maxim

hi,
k, i see u have misunderstood me^^.
and i was not precisely enough^^.

once again:
some tables have columns of type anydata.
i will check them out (by creating an sql textfile containing merges and
inserts)
the problem is, that the anydatatype may continue "not standard" types (eg
self defined).
in this case it is easier to get the binary representation of this column
(to insert this representation in the destination database).
i know, that both dbs (souce and dest) must have the type/object defined
(but this is always so).
logminer works the same way i guess.
i want to reproduce it.

and plz dont dicuss if it make sense or not^^. im not making the goal for
this. im the one who must find a way to make it possible^^.

greets

micha
 >> Stay informed about: Inserting binary data to an column 

Micha wrote:
> Hi,
> i want to make a proof of concept about handling "binary data".
> Background:
> I want to get a binary decription (or hex etc) of a data stored in an
> column. Later i want to insert this binary/hex etc data directly in a column
> (by code in plsql).
> If i can make this, imable to insert any data intependent from its data
> type.
>
> Questions:
> 1. How can i get the right data representation (binary/hex etc for later
> insert)?
> 2. What are the correct statements to insert this data representation?
>
> For example:
> the hex representation of 'Technical Writer' is
> '546563686e6963616c20577269746572'
>
> so i want to get the hex number and later i want to insert it (and the
> database (i my case Oracle 10g) understand it as 'Technical Writer'
>
> Greets
>
> Micha

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.DeleteThis@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: Inserting binary data to an column 

>
> 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.TakeThisOut@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
 >> Stay informed about: Inserting binary data to an column 

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.TakeThisOut@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.TakeThisOut@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: Inserting binary data to an column 

"DA Morgan" schrieb im Newsbeitrag

> 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 DeleteThis @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 DeleteThis @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
 >> Stay informed about: Inserting binary data to an column 

Micha wrote:
>
> "DA Morgan" schrieb im Newsbeitrag
>
>> 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 DeleteThis @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 DeleteThis @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 DeleteThis @x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 >> Stay informed about: Inserting binary data to an column