How to display the value of a BLOB column in SQL*PLUS?

error message “SP2-0678: Column or attribute type can not be displayed by SQL*Plus”

SQL> create table demo (col1 raw(1000)) tablespace users;

Table created.

SQL> insert into demo values (utl_raw.CAST_TO_RAW('RAWrawDATAraw'));

1 row created.

SQL> select utl_raw.cast_to_varchar2(col1) from demo;

UTL_RAW.CAST_TO_VARCHAR2(COL1)
--------------------------------------------------------------------------------
RAWrawDATAraw

SQL>

You could even drop that into a substr to reduce the size of the output... say to view the first 100 characters. Here is an example of the first 5 characters:
SQL> select substr(utl_raw.cast_to_varchar2(col1),1,5) from demo;

SUBST
-----
RAWra

 

More examples

Rem
Rem Display a RAW data in SQLPLUS
Rem
create or replace procedure display_raw(rdata raw)
IS
    pos                 pls_integer;
    length              pls_integer;
BEGIN
    pos := 1;
    length := UTL_RAW.LENGTH(rdata);

    WHILE pos <= length LOOP
      IF pos+20 > length+1 THEN
        dbms_output.put_line(UTL_RAW.SUBSTR(rdata, pos, length-pos+1));
      ELSE
        dbms_output.put_line(UTL_RAW.SUBSTR(rdata, pos, 20));
      END IF;
      pos := pos+20;
    END LOOP;

END display_raw;
/

show errors;

Rem
Rem Display a BLOB data in SQLPLUS
Rem
create or replace procedure display_blob(bdata blob)
IS
    pos                 pls_integer;
    length              pls_integer;
BEGIN
    length :=  dbms_lob.getlength(bdata);
    pos := 1;
    WHILE pos <= length LOOP
      display_raw(DBMS_LOB.SUBSTR(bdata, 2000, pos));
      pos := pos+2000;
    END LOOP;
END display_blob;
/

show errors;

Rem
Rem Display a VARCHAR data in SQLPLUS
Rem
create or replace procedure display_varchar(vdata varchar)
IS
    pos                 pls_integer;
    text_len            pls_integer;
BEGIN
    text_len :=  length(vdata);
    pos := 1;

    WHILE pos <= text_len LOOP
      IF pos+20 > text_len+1 THEN
        dbms_output.put_line(SUBSTR(vdata, pos, text_len-pos+1));
      ELSE
        dbms_output.put_line(SUBSTR(vdata, pos, 20));
      END IF;
      pos := pos+20;
    END LOOP;

END display_varchar;
/

show errors;

Rem
Rem Display a CLOB data in SQLPLUS
Rem
create or replace procedure display_clob(cdata clob)
IS
    pos                 pls_integer;
    length              pls_integer;
BEGIN
    length :=  dbms_lob.getlength(cdata);
    pos := 1;
    WHILE pos <= length LOOP
      display_varchar(DBMS_LOB.SUBSTR(cdata, 2000, pos));
      pos := pos+2000;
    END LOOP;
END display_clob;
/

Advertisements

Oracle Grid control 11g installation procedure

Useful blogs on how to install oracle grid control 11g

http://nafey1.blogspot.com/2010/04/oracle-grid-control-11g-installation.html

http://gavinsoorma.com/2010/04/11g-enterprise-manager-grid-control-installation-overview/

http://oracleinnotiive.wordpress.com/2010/08/02/oracle-enterprise-manager-11g-grid-control-installation-guide/

Oracle demo

http://download.oracle.com/technology/products/oem/screenwatches/EM_Install/EM_Install.html

problems upgrading from 11.2.0.1 to 11.2.0.2 Grid Standalone

Useful blogs and forums I used to resolve the problem

http://surachartopun.com/2010/09/struggle-to-upgrade-oracle-database.html

http://dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=41647

downgrading a 11g database to a previous version

1. Shut down the database and start it up in the downgrade mode.

SQL> startup downgrade

2. Spool the results of the downgrade script.

SQL> spool downgrade.log

3. Execute the downgrade script, called catdwgrd.sql.

SQL> @catdwgrd.sql

4. After the catdwgrd.sql script finished executing, shut down the database cleanly.

SQL> shutdown immediate

5. Start up the database in the upgrade mode, from the pre-upgrade ORACLE_ HOME environment.

SQL> startup upgrade

6. Reload the old PL/SQL packages and data dictionary views.

SQL> @catrelod.sql

7. After the reloading of the old packages and views, shut down the database and restart it.

SQL> shutdown immediate SQL> startup

8. Run the utlrp.sql script to recompile any packages, procedures, and types that became invalid during the downgrading of the database.

SQL> @utlrp.sql

This completes the downgrading process.

You can run the utlrp.sql script may times. and you should make sure that you handle all invalid objects.