converting BLOB to CLOB datatype in oracle

Converting BLOB column data type to CLOB is a very tedious task, since you cannot do it directly.

 

First, you need to create an “Oracle Function” under an oracle schema you have access to, the following is the function:

 

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)

RETURN CLOB

AS

v_clob CLOB;

v_varchar VARCHAR2(32767);

v_start PLS_INTEGER := 1;

v_buffer PLS_INTEGER := 32767;

BEGIN

DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

 

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)

LOOP

 

v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

 

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);

v_start := v_start + v_buffer;

END LOOP;

RETURN v_clob;

END blob_to_clob;

/

 

Second, alter the table by adding a temporary column with a name similar to the BLOB column name.

For example, let’s say the BLOB column name is STRING_NAMES, the new CLOB column will be STRING_NAMES2

 

Sql command:

 

Alter table DUMMY.TABLE add (STRING_NAMES2 CLOB);

 

Then, use the “update” statement to populate the column with the converted data:

 

update DUMMY.TABLE  set STRING_NAMES2=blob_to_clob(STRING_NAMES);

 

Once finished drop the original column, then rename the new one to the original name.

 

If your table has huge number of records, you may need to develop a script and schedule it to perform this intensive information.

*** UPDATE:

another way of converting BLOB to CLOB can be found here:

https://geodatamaster.wordpress.com/2014/01/01/convert-blob-to-clob-column-method-2/

 

Oracle Database Network Encryption and ArcSDE

To enable “Network Encryption” between ArcGIS Desktop and the Database, use “direct-connect” as your method of connection to the ArcSDE Geodatabase.

If the Oracle database has already configured for Network Encryption (which is not part of the Oracle Advanced Security License), you need to install Oracle Client 11g on your machine where ArcGIS Desktop is installed in. There is no extra configuration required on the client level.

As a consequence, this will also make all your map services encrypted (while creating MXD files based on SDE direct connection) and installing Oracle 11g Client on the server.

 

Reference Resource: http://resources.arcgis.com/content/enterprisegis/10.0/encryption_mechanism