Solved

how to Convert VARCHAR2 to BLOB

Posted on 2003-12-10
5
16,286 Views
Last Modified: 2007-12-19
convert varchar2 column to BLOB !

Hi,
have few tables each with one or two description columns declared as VARCHAR2 ,which i need to convert to BLOB.
TO_LOB function is for converting LONG or LONG RAW to BLOB that too within a select statement which in turn is inside an INSERT INTO statement..
have to maintain data when i make the change.
Any help appreciated.

Thanks in advance..
0
Comment
Question by:skaykay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Accepted Solution

by:
grim_toaster earned 125 total points
ID: 9918694
If you wish to store the description as just text, then a CLOB would probably suit your needs better.  The below script replaces the varchar2 column with a CLOB column (it works on 9i, but not tested on earlier versions).

-- Create a table with a number field, and a varchar2 field
CREATE TABLE tmp (col1 NUMBER, col2 VARCHAR2(4000))
-- will need populating

-- rename the varchar2 field to something
ALTER TABLE tmp RENAME COLUMN col2 TO col_2_varchar2

-- re-create the column as a CLOB
ALTER TABLE tmp ADD (col2 CLOB)

-- populate the clob field as that from the previous value
UPDATE tmp SET col2 = col_2_varchar2

-- Now remove the renamed varchar2 column
ALTER TABLE tmp DROP COLUMN col_2_varchar2
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9918727
Since a BLOB deals with uninterpreted data, the RAW type is the scalar type that is being used to write to the LOB using the DBMS_LOB.WRITE, DBMS_LOB.WRITE_APPEND procedure. So to add a VARCHAR2 column to a BLOB this column has to be converted to a RAW. This conversion is done using the UTL_RAW package.

The UTL_RAW.CAST_TO_RAW function converts a VARCHAR2 into a RAW datatype. The data is not modified, only its datatype is recast.

Syntax:
       UTL_RAW.CAST_TO_RAW ( v_varIN VARCHAR2) RETURN RAW;

The UTL_RAW.CAST_TO_VARCHAR2 function does the opposite of the above function.
    Syntax:
       UTL_RAW.CAST_TO_VARCHAR2 (raw_var IN RAW) RETURN VARCHAR2.

After the VARCHAR2 to RAW conversion has been made the DBMS_LOB procedures WRITE and WRITEAPPEND may be used in combination with a BLOB.
0
 
LVL 4

Author Comment

by:skaykay
ID: 9918744
Hi,

Thanks for the reply.
Tried your way by renaming existing column..it says "ORA-14155: missing PARTITION OR SUBPARTITION KEYWORD"
Is column renaming not possible ?

Thanks
0
 

Expert Comment

by:dvabrinda
ID: 9918794
hi,
   you will get error "ORA-14155: missing PARTITION OR SUBPARTITION KEYWORD" only when you forget to include column keyword in your query while renaming a column.
try this syntax
         alter table table1
          rename column col1 to col1

to rename the column
0
 
LVL 4

Author Comment

by:skaykay
ID: 9918988
hi dvabrinda,

Thanks..was a silly thing to do..anyway had corrected and the column name is getting renamed...will try out teh conversion.
Thanks again


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question