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
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/
Oracle demo
http://download.oracle.com/technology/products/oem/screenwatches/EM_Install/EM_Install.html
Categories: Grid
problems upgrading from 11.2.0.1 to 11.2.0.2 Grid Standalone
November 3, 2010
Leave a comment
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
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