Archive

Archive for November, 2010

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

November 19, 2010 Leave a comment

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
Categories: Oracle

Oracle Grid control 11g installation procedure

November 9, 2010 Leave a comment
Categories: Grid

problems upgrading from 11.2.0.1 to 11.2.0.2 Grid Standalone

November 3, 2010 Leave a comment
Categories: Oracle

downgrading a 11g database to a previous version

November 3, 2010 Leave a comment

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.

Categories: Oracle