Oracle: How to convert CLOB to BLOB

FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);

Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);

v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));

v_offsetwrite := v_offsetwrite + v_amountwrite;

v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;

Oracle: How to convert BLOB to CLOB

FUNCTION blob2clob(p_blob IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_amount NUMBER DEFAULT 2000;
v_offset NUMBER DEFAULT 1;
v_buffer VARCHAR2(32767);
v_length PLS_INTEGER := dbms_lob.getlength(p_blob);

BEGIN
dbms_lob.createtemporary(v_clob, TRUE);

dbms_lob.OPEN(v_clob, dbms_lob.lob_readwrite);

WHILE v_offset <= v_length LOOP
v_buffer := utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, v_amount, v_offset));

IF length(v_buffer) > 0 THEN
dbms_lob.writeappend(v_clob, length(v_buffer), v_buffer);
END IF;

v_offset := v_offset + v_amount;
EXIT WHEN v_offset > v_length;
END LOOP;

RETURN v_clob;
END blob2clob;


----------------------------------------------------------------
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;

Aarti Chabria

ORA-00214: controlfile inconsistent

One of my database crashed with this error, and here is how I fixed it.

ORA-00214: controlfile inconsistent

Error I received
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL03.CTL' version 587

SQL> shutdown immediate;

SQL> create pfile from spfile;

SQL> shutdown immediate;

Open the pfile, locate "*.control_files", It will look similar to like below

*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL03.CTL'

Since one of the 3 control file is inconsistent, I removed the second and third and saved the file.

*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL'

SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL02.CTL' version 589

Now remove first and third, try with control file 2

*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL'

SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'


SQL> shutdown immediate;

Now create spfile from modified pfile, so it is used by default.

SQL> create spfile from pfile='C:\oracle\ora92\database\INITorcldb.ORA';

SQL> startup mount

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'


SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'


SQL> shutdown immediate;

SQL> startup force
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Specify the log file to use

SQL> Recover database using backup controlfile;
ORA-00279: change 3117356 generated at 04/08/2008 08:49:26 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00053.001
ORA-00280: change 3117356 for thread 1 is in sequence #53


Specify log: {=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\ORCLDB\REDO01.LOG
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 370220868 bytes
Fixed Size 454468 bytes
Variable Size 343932928 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

Blackberry: Battery too low for radio use

I am not sure what Blackberry engineers were thinking when they designed the phone.

It's really frustrating, and it is the most stupid thing to do. According to Blackberry, its a feature and not a design bug.

There are 2 options to force the battery on

1) You can dial 911 and disconnect the line, before the operator picks up. Though this works, I wont advice this, as you may offend 911 rules as this is clearly not an emergency

2) Install "ForceRadioOn" application. Its a freeware and available at http://www.blackberryapps.com/PlatformMain.asp?platform=5&sString=ForceRadioOn
or simply google for ForceRadioOn

After installing this application, just launch the application and click the button to turn the Radio On.