How to transfer varchar2 to long raw
tamsun
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.
IANAL_VISTA
news:f1gcf299gkpta5h69...@4ax.com:
INSERT INTO T2 SELECT * FROM T1;
Brian Peasland
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
d...@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
tamsun
I try this sql,but error occur:
ORA-01465: invalid hex number
tamsun
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?
DA Morgan
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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
DA Morgan
> 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?
tamsun
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.
DA Morgan
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.
tamsun
wrote:
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 ?
Brian Peasland
> 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.
As I have stated, LONG RAW is for BINARY data and you have character
data. If you insist on storing character data in to a LONG RAW datatype,
then you will have to store the character data in some sort of file
(text file, MS Word doc, etc) and the insert that binary document into
the column. Talk about a long way to go when LONG RAW is not meant to
hold character data. And then you'd have to write a routine to pull
binary data out of the LONG RAW column and represent it in the web app.
It is much, much easier to use LONG or CLOB.
> Yes, Blob or Clob will be better.But web developers use
> a interface to operate database. This interface can only
> deal with long raw.
The interface "can only deal with long raw"?!?!?! That statement is
going to be incorrect 99.9% of the time. Every development platform that
I have worked with that supports LONG RAW also supports LONG. I am
leaving open the possibility that some crazy development platform does
not follow this, but I have yet to work with it. And I've worked with
quite a few web development platforms that interact with an Oracle
database....Java, ASP and ASP.NET, JSP, Cold Fusion, PHP, Perl/DBI, and
the list goes on and on.
And unless this app is written in some archaic/old web development
platform, it will also support BLOB and CLOB. In many cases, one doesn't
even know the difference between 3,000 characters stored in
VARCHAR2(4000) and CLOB. In many cases, the programming is the same.
DA Morgan
> 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 ?
CLOB CLOB and CLOB. That is the best possible solution.
If the web developers can't figure out to to use it then that is the
problem you should be asking about. What toolset are they using?
tamsun
wrote:
xcept using attachment ?
>
>CLOB CLOB and CLOB. That is the best possible solution.
>
>If the web developers can't figure out to to use it then that is the
>problem you should be asking about. What toolset are they using?
Thanks again.
Once I have create a table with a BLOB field to stored attachment.
The web program make wrong, when I change BLOB to long raw, it's
work. As for CLOB, I have not do experiment. I really don't know
why because the web platform is not open to me. When I consult
the engineer who make the platform, he ask me to use long raw.
I'm sorry for my low-grade question. I think I must misunderstand
something. Today I will ask the engineer again.
Thank you very much.
DA Morgan
I appreciate that English may not be your first language but it seems
that you are not understanding what multiple people have tried to tell
you.
VARCHAR2 and CLOB and LONG = characters
LONG RAW and BLOB = binary
They are not interchangeable.
tamsun
wrote:
>
>I appreciate that English may not be your first language but it seems
>that you are not understanding what multiple people have tried to tell
>you.
>
>VARCHAR2 and CLOB and LONG = characters
>LONG RAW and BLOB = binary
>
>They are not interchangeable.
I understand. thank you.