Home » SQL & PL/SQL » SQL & PL/SQL » urgent: changing non-empty column type?
urgent: changing non-empty column type? [message #325668] Sat, 07 June 2008 07:32 Go to next message
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!
Re: urgent: changing non-empty column type? [message #325669 is a reply to message #325668] Sat, 07 June 2008 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 25037
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: urgent: changing non-empty column type? [message #325694 is a reply to message #325668] Sat, 07 June 2008 10:36 Go to previous message
Barbara Boehmer
Messages: 8631
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> 






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


Current Time: Mon Dec 05 10:47:33 CST 2016

Total time taken to generate the page: 0.08609 seconds