OraFAQ Forum: SQL & PL/SQL » urgent: changing non-empty column type?

Home » SQL & PL/SQL » SQL & PL/SQL » urgent: changing non-empty column type?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

urgent: changing non-empty column type? [message #325668]

Sat, 07 June 2008 07:32

viki250
Messages: 1
Registered: June 2008

Junior Member

Hi All,
I have a table in 10g which has data in it including primary/foriegn keys. This table is also referenced by others. One of its column is right now of type varchar(2), but I want to change it to BLOB.
Reason: SIince varchar(2) allows max 2000 charachers. But now I want to use this column to enter rich text. Means a text which will contain images as well. And it will be definitely above 2000 characters.

I have checked that i can change col type via TOAD. But want to make sure is it a safe way to change? Or should be done via queries? If queries, then please let me know the queries as am a newbie in this area. Also, i dont want to loose data in table/col.

thanks in advance!

Report message to a moderator

Re: urgent: changing non-empty column type? [message #325669 is a reply to message #325668]

Sat, 07 June 2008 08:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>urgent: changing non-empty column type?
Please explain why it is urgent for ME to solve this problem for you.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above.

>Also, i dont want to loose data in table/col.
Does this mean you are working without any backup or duplicate copies?
If so, you have nobody to blame but yourself.

[Updated on: Sat, 07 June 2008 08:21] by Moderator

Report message to a moderator

Re: urgent: changing non-empty column type? [message #325678 is a reply to message #325668]

Sat, 07 June 2008 09:21

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

SIince varchar(2) allows max 2000 charachers

In France, it allows 4000 characters but we are lucky ones.

Quote:

Means a text which will contain images as well. And it will be definitely above 2000 characters.

I doubt image is characters.

Quote:

I have checked that i can change col type via TOAD. But want to make sure is it a safe way to change?

Ask Quest Software.

Quote:

i dont want to loose data

If you use character datatypes to store images you will lose data.

Regards
Michel

Report message to a moderator

Re: urgent: changing non-empty column type? [message #325694 is a reply to message #325668]

Sat, 07 June 2008 10:36

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

This can be done in a roundabout way, as demonstrated below. In addition to having a backup, you should make a copy and use that for testing to see if any data is not compatible. Note that once your data is in a blob column, you will have to select it differently.

-- initial data:

SCOTT@orcl_11g> CREATE TABLE test_tab (test_col VARCHAR2 (2000))
  2  /

Table created.

SCOTT@orcl_11g> INSERT INTO test_tab (test_col) VALUES ('testing')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM test_tab
  2  /

TEST_COL
--------------------------------------------------------------------------------
testing

-- change:

SCOTT@orcl_11g> ALTER TABLE test_tab ADD (blob_col BLOB)
  2  /

Table altered.

SCOTT@orcl_11g> UPDATE test_tab
  2  SET    blob_col = UTL_RAW.CAST_TO_RAW (test_col)
  3  /

1 row updated.

SCOTT@orcl_11g> ALTER TABLE test_tab
  2  DROP COLUMN test_col
  3  /

Table altered.

SCOTT@orcl_11g> ALTER TABLE test_tab
  2  RENAME COLUMN blob_col TO test_col
  3  /

Table altered.

-- results:

SCOTT@orcl_11g> SELECT UTL_RAW.CAST_TO_VARCHAR2 (test_col) FROM test_tab
  2  /

UTL_RAW.CAST_TO_VARCHAR2(TEST_COL)
--------------------------------------------------------------------------------
testing

SCOTT@orcl_11g>

Report message to a moderator

Previous Topic:	SQL Query Question
Next Topic:	HOW CAN I DO THIS QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 16:45:03 CST 2025