advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment