Convert Oracle Varchar2 column to Blob data type Using utl_raw.cast_to_raw – Example

Here is the example for normal Oracle VARCHAR2 datatype to BLOB database.

In this example EMP is my sample table and I trying to convert ENAME column to blob.


SQL> desc emp;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                                  NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)

Table has 9 number of records

SQL> select ename from emp ;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING

9 rows selected.

Add one more column with BLOB data type.
SQL> alter table emp add BENAME blob;

Table altered.

SQL> desc emp;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 EMPNO                                  NUMBER(4)
 ENAME                                  VARCHAR2(10)
 JOB                                    VARCHAR2(9)
 MGR                                    NUMBER(4)
 HIREDATE                               DATE
 SAL                                    NUMBER(7,2)
 COMM                                   NUMBER(7,2)
 DEPTNO                                 NUMBER(2)
 BENAME                                 BLOB

Update the column using utl_raw.cast_to_raw.
SQL> update emp set bename=utl_raw.cast_to_raw(ENAME);

9 rows updated.

SQL> select ename, bename from emp;

ENAME      BENAME
---------- ------------------------------
SMITH      534D495448
ALLEN      414C4C454E
WARD       57415244
JONES      4A4F4E4553
MARTIN     4D415254494E
BLAKE      424C414B45
CLARK      434C41524B
SCOTT      53434F5454
KING       4B494E47

9 rows updated.

If you wanted to see the actual values from the BLOB, you can use UTL_RAW.CAST_TO_VARCHAR2 function.
SQL> select ename ,UTL_RAW.CAST_TO_VARCHAR2(Bename) Bename  from emp;

ENAME      BENAME
---------- ------------------------------
SMITH      SMITH
ALLEN      ALLEN
WARD       WARD
JONES      JONES
MARTIN     MARTIN
BLAKE      BLAKE
CLARK      CLARK
SCOTT      SCOTT
KING       KING

9 rows updated.

Now you have to drop the old column and rename the new column to the actual name.
SQL> alter table emp drop column ename;

Table altered.



SQL> alter table emp rename column BENAME to ename;

Table altered.
Now the table is ready with BLOB column
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                              NUMBER(4)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 ENAME                                              BLOB
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google