hi, I have a question:

I hava a table, such as:

Table: T1
Field:
ID varchar(38),
VALUE varchar(4000),

Now I need modify table structure to:
Table: T2
Filed:
ID varchar(38),
VALUE long raw

My question is:
As filed "VALUE" had be changed to long raw,
how to move old record into new table T2 by SQL,
e.g. how to insert a string(such as "Hello World")
into long raw filed by pure sql.

tamsun <tamsun@gmail.com> wrote in
news:f1gcf299gkpta5h69tikqr4h040vl1rb9t@4ax.com:

>
> hi, I have a question:
>
> I hava a table, such as:
>
> Table: T1
> Field:
> ID varchar(38),
> VALUE varchar(4000),
>
> Now I need modify table structure to:
> Table: T2
> Filed:
> ID varchar(38),
> VALUE long raw
>
> My question is:
> As filed "VALUE" had be changed to long raw,
> how to move old record into new table T2 by SQL,
> e.g. how to insert a string(such as "Hello World")
> into long raw filed by pure sql.
>

INSERT INTO T2 SELECT * FROM T1;

tamsun wrote:
> hi, I have a question:
>
> I hava a table, such as:
>
> Table: T1
> Field:
> ID varchar(38),
> VALUE varchar(4000),
>
> Now I need modify table structure to:
> Table: T2
> Filed:
> ID varchar(38),
> VALUE long raw
>
> My question is:
> As filed "VALUE" had be changed to long raw,
> how to move old record into new table T2 by SQL,
> e.g. how to insert a string(such as "Hello World")
> into long raw filed by pure sql.

The LONG RAW datatype is used for BINARY data while the VARCHAR2
datatype is used for character data. These two are inconsistent with
each other. You would want to use the LONG datatype instead.

That being said, it makes much more sense if you are creating the table
anew to use the CLOB datatype as the LONG and LONG RAW datatypes may
seee their end in the future.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

On Thu, 31 Aug 2006 02:40:05 GMT, "IANAL_VISTA"
<IANAL_Vista@hotmail.com> wrote:

>tamsun <tamsun@gmail.com> wrote in
>news:f1gcf299gkpta5h69tikqr4h040vl1rb9t@4ax.com :
>
>>
>> hi, I have a question:
>>
>> I hava a table, such as:
>>
>> Table: T1
>> Field:
>> ID varchar(38),
>> VALUE varchar(4000),
>>
>> Now I need modify table structure to:
>> Table: T2
>> Filed:
>> ID varchar(38),
>> VALUE long raw
>>
>> My question is:
>> As filed "VALUE" had be changed to long raw,
>> how to move old record into new table T2 by SQL,
>> e.g. how to insert a string(such as "Hello World")
>> into long raw filed by pure sql.
>>
>
>INSERT INTO T2 SELECT * FROM T1;


I try this sql,but error occur:
ORA-01465: invalid hex number

On Thu, 31 Aug 2006 03:12:25 GMT, Brian Peasland
<dba@nospam.peasland.net> wrote:

>tamsun wrote:
>> hi, I have a question:
>>
>> I hava a table, such as:
>>
>> Table: T1
>> Field:
>> ID varchar(38),
>> VALUE varchar(4000),
>>
>> Now I need modify table structure to:
>> Table: T2
>> Filed:
>> ID varchar(38),
>> VALUE long raw
>>
>> My question is:
>> As filed "VALUE" had be changed to long raw,
>> how to move old record into new table T2 by SQL,
>> e.g. how to insert a string(such as "Hello World")
>> into long raw filed by pure sql.
>
>The LONG RAW datatype is used for BINARY data while the VARCHAR2
>datatype is used for character data. These two are inconsistent with
>each other. You would want to use the LONG datatype instead.
>
>That being said, it makes much more sense if you are creating the table
>anew to use the CLOB datatype as the LONG and LONG RAW datatypes may
>seee their end in the future.
>
>HTH,
>Brian


field F1 is varchar2(4000), I want move the data in F1 into
another filed F1 which is long raw.
I wonder how to do by sql?

tamsun wrote:
> On Thu, 31 Aug 2006 02:40:05 GMT, "IANAL_VISTA"
> <IANAL_Vista@hotmail.com> wrote:
>
>> tamsun <tamsun@gmail.com> wrote in
>> news:f1gcf299gkpta5h69tikqr4h040vl1rb9t@4ax.com:
>>
>>> hi, I have a question:
>>>
>>> I hava a table, such as:
>>>
>>> Table: T1
>>> Field:
>>> ID varchar(38),
>>> VALUE varchar(4000),
>>>
>>> Now I need modify table structure to:
>>> Table: T2
>>> Filed:
>>> ID varchar(38),
>>> VALUE long raw
>>>
>>> My question is:
>>> As filed "VALUE" had be changed to long raw,
>>> how to move old record into new table T2 by SQL,
>>> e.g. how to insert a string(such as "Hello World")
>>> into long raw filed by pure sql.
>>>
>> INSERT INTO T2 SELECT * FROM T1;
>
>
> I try this sql,but error occur:
> ORA-01465: invalid hex number

Read Brian's comment. You need LONG, not LONG RAW. But more importantly
this data type has been deprecated and your move makes little sense. Why
not a CLOB?

Also both of your column names are reserved words in Oracle. A very bad
choice so consider changing your column names to avoid problems.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

tamsun wrote:

> field F1 is varchar2(4000), I want move the data in F1 into
> another filed F1 which is long raw.
> I wonder how to do by sql?

Why LONG RAW?
What is the business case?
In which version of Oracle?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Wed, 30 Aug 2006 22:57:20 -0700, DA Morgan <damorgan@psoug.org>
wrote:

>tamsun wrote:
>
>> field F1 is varchar2(4000), I want move the data in F1 into
>> another filed F1 which is long raw.
>> I wonder how to do by sql?
>
>Why LONG RAW?
>What is the business case?
>In which version of Oracle?

The real field name is YFBZYJ in table T1 and T2.
I use Oracle 9i.

Some one design the database, the field YFBZYJ is
used for client to fill opinion which maybe
100 characters, or maybe 5,000 characters long.
So the designer change the origin field type
from varchar2(4000) to Long Raw.

Yes, Blob or Clob will be better.But web developers use
a interface to operate database. This interface can only
deal with long raw.

I am responsible to dealing with data. Now I should
move data to new table with the long raw field.

tamsun wrote:
> On Wed, 30 Aug 2006 22:57:20 -0700, DA Morgan <damorgan@psoug.org>
> wrote:
>
>> tamsun wrote:
>>
>>> field F1 is varchar2(4000), I want move the data in F1 into
>>> another filed F1 which is long raw.
>>> I wonder how to do by sql?
>> Why LONG RAW?
>> What is the business case?
>> In which version of Oracle?
>
> The real field name is YFBZYJ in table T1 and T2.
> I use Oracle 9i.
>
> Some one design the database, the field YFBZYJ is
> used for client to fill opinion which maybe
> 100 characters, or maybe 5,000 characters long.
> So the designer change the origin field type
> from varchar2(4000) to Long Raw.
>
> Yes, Blob or Clob will be better.But web developers use
> a interface to operate database. This interface can only
> deal with long raw.
>
> I am responsible to dealing with data. Now I should
> move data to new table with the long raw field.

Either the web developers are crazy or you have misunderstood
what they are saying. LONG RAW is binary ... not text. There is
no way an end user is going to use it to store comments.

Check out the Data Types page in Morgan's Library at www.psoug.org
to view the various data types and what their definitions.

In my experience there is no interface that does not allow CLOB
and you should not accept anything except CLOB without a far better
understanding of why anyone is claiming it can not be done. That
said ... what product are they using (include version number).

It just occured to me that some Oracle products, Forms for example,
still use LONG to indicate LONG or CLOB. Hopefully this misuse of the
name will be fixed in Forms 11g.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Thu, 31 Aug 2006 06:48:13 -0700, DA Morgan <damorgan@psoug.org>
wrote:

>tamsun wrote:
>> On Wed, 30 Aug 2006 22:57:20 -0700, DA Morgan <damorgan@psoug.org>
>> wrote:
>>
>>> tamsun wrote:
>>>
>>>> field F1 is varchar2(4000), I want move the data in F1 into
>>>> another filed F1 which is long raw.
>>>> I wonder how to do by sql?
>>> Why LONG RAW?
>>> What is the business case?
>>> In which version of Oracle?
>>
>> The real field name is YFBZYJ in table T1 and T2.
>> I use Oracle 9i.
>>
>> Some one design the database, the field YFBZYJ is
>> used for client to fill opinion which maybe
>> 100 characters, or maybe 5,000 characters long.
>> So the designer change the origin field type
>> from varchar2(4000) to Long Raw.
>>
>> Yes, Blob or Clob will be better.But web developers use
>> a interface to operate database. This interface can only
>> deal with long raw.
>>
>> I am responsible to dealing with data. Now I should
>> move data to new table with the long raw field.
>
>Either the web developers are crazy or you have misunderstood
>what they are saying. LONG RAW is binary ... not text. There is
>no way an end user is going to use it to store comments.
>
>Check out the Data Types page in Morgan's Library at www.psoug.org
>to view the various data types and what their definitions.
>
>In my experience there is no interface that does not allow CLOB
>and you should not accept anything except CLOB without a far better
>understanding of why anyone is claiming it can not be done. That
>said ... what product are they using (include version number).
>
>It just occured to me that some Oracle products, Forms for example,
>still use LONG to indicate LONG or CLOB. Hopefully this misuse of the
>name will be fixed in Forms 11g.

thank you very much.

Can you give me some suggestions about this scenario:

user will input many characters, maybe 1,000,
maybe 10,000. How to store these characters into database
except using attachment ?