Display a blob

I have a table with a blob create table t(b blob); insert into t values (‘585858’); In 11g sql*plus, I can display raw data select b from t; B —— 585858 Ok, but if I want to display XXX (the character content) select utl_raw.cast_to_varchar2(b) from t; UTL — XXX However, in sql, a raw cannot… Continue reading Display a blob

errorlogging in 11g

This is a very neat feature in 11g. I have a script called foo.sql create table t(x number primary key); insert into t(x) values (1); insert into t(x) values (2); insert into t(x) values (2); insert into t(x) values (3); commit; It is eyes-popping that this script will return an error, but which one? Let’s… Continue reading errorlogging in 11g

sql*plus pagesize explained

SQL*Plus is a not only the command-line interface to the database server, it is also a featured reporting tool with paging capabilities. The pagesize is the number of rows of one page. The default is 14 and the maximum is 50000. One of the common property of the page is the headers when selecting from… Continue reading sql*plus pagesize explained

Export to Excel

One more neat solution from Michaels about exporting the data to Excel in this otn post set feed off markup html on spool on alter session set nls_date_format=’YYYY-MM-DD’; spool emp.xls select * from emp; spool off set markup html off spool off And it perfectly opens in Excel. No hassle with separator, no time lost… Continue reading Export to Excel

su in sqlplus

How to switch user in Oracle ? One approach is to change the password : SQL> connect / as sysdba Connected. SQL> select password from dba_users where username=’SCOTT’; PASSWORD —————————— F894844C34402B67 SQL> alter user scott identified by abc123; User altered. SQL> connect scott/abc123 Connected. SQL> create table t… SQL> connect / as sysdba Connected. SQL>… Continue reading su in sqlplus

clear screen reports cleared columns, breaks and computes

$ echo clear screen|sqlplus scott/tiger SQL> columns cleared breaks cleared computes cleared but if I quit properly, it does not report that $ echo “clear screen quit”|sqlplus scott/tiger SQL> a good reason to improve the quality of your shell script by quitting at the end 😉

Do you shu or do you spo?

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type “by”. Does it sound strange and meaningless to abbreviate “bye” in “by” ? Well, I have a few favorites SQL> set lin 999 SQL> rollb SQL> spo f SQL> shu SQL> spo off

select column only if it exists

i need to display tablespace attributes SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces; TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG ——————– ——— ——— ——— ———- ——— —— ———– — SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO looks fine. but what if I try that… Continue reading select column only if it exists

return code

there is a myth of using sql.sqlcode in sqlplus whenever sqlerror exit sql.sqlcode this not ok. you should prefer whenever sqlerror exit failure or exit 1 Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88. Let’s try it $ sqlplus “/ as sysdba” SQL> create user… Continue reading return code

set pages 50000

I have often see set pagesize 50000 or set pages 9999, just to avoid page break. I wonder how many line break will appears when the select grow over 50000… A cool solution is to use embedded with pagesize 0 SQL> set emb on pages 0 newp 0

login.sql

Hi, I am not recommending the use of glogin.sql and login.sql to do sql computation. It is degrading the performance and the maintenability of the scripts run over the database. Myself I am using a big login.sql, which is located in the “SQLPATH” variable. I tested it with 7.3, 8.1.7, 9.2 and 10.0 on AIX.… Continue reading login.sql

shell + sqlplus

How to send commands to sqlplus ? Use stdin $ ( echo prompt $(hostname) $(date); echo desc emp ) | sqlplus -s scott/tiger dbsrv85a Mon Jun 6 17:01:46 CEST 2005 Name Null? Typ —- —– — EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) How… Continue reading shell + sqlplus