Categories
sqlplus

super-long-lines in CLOB

Sometimes you use sqlplus and your line is longer than your linesize


SQL> select n||';'||x from t2;
1;one
2;twoPxMQztzLaqjWjGKOXIVIVrrHC
fJkTLbRgCPiENfWrrCjUMRSkmCfLUY
RdVASFacGtyEnUplOAXspDJZmSLPfg
ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy
lZLQROFxIxOzqeQeFMGXcGkuJwnGGg
zvNSuCFMVxyQgboLAUpDcYnBsuVyXX
vFtWPICwqmcEdmzRRkOzzPethrqjRR
aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh
ZcxJBvSCVXwONmS
prVzTykEfSsePyYwyLVoyYrVLynUzs
MLFWQxwUKNsVcYzUOAhslNldnBpITS
rxPlpJbLSjJqgxNxsGVsrYhkWAMufk
QnRayieEkSDYrNqyLejJuggADNxcgV
tszjJIYKCxPweNGhXsOFKGbMkTBPCf
DXwjBNgQYswbaNWBOEtSTHjIhdLAyM
nbhyhRKKdfaTTpTgHqQelVWmnkBHjA
ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR
SjdVlRwMas

3;three
SQL>

There are two extra line breaks, one after ONmS and one after wMas
An easy solution is to set long lines with set lin 32767 longc 32767 long 2000000000… but, once you reach 32K, end of the game.

I come up with an easy workaround, using clob2file

create directory d as '/tmp';
begin
for f in (select rownum r, n||';'||x||chr(10) txt from t)
loop
dbms_lob.clob2file(f.txt, 'D',
'F'||to_char(f.r,'FM00009')||'.txt');
end loop;
end;
/

ls -la F?????.txt
-rw-r----- 1 oracle 6 Sep 26 13:40 F00001.txt
-rw-r----- 1 oracle 506 Sep 26 13:40 F00002.txt
-rw-r----- 1 oracle 8 Sep 26 13:40 F00003.txt
cat F?????.txt

1;one
2;twoPxMQztzLaqjWjGKOXIVIVrrHC
fJkTLbRgCPiENfWrrCjUMRSkmCfLUY
RdVASFacGtyEnUplOAXspDJZmSLPfg
ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy
lZLQROFxIxOzqeQeFMGXcGkuJwnGGg
zvNSuCFMVxyQgboLAUpDcYnBsuVyXX
vFtWPICwqmcEdmzRRkOzzPethrqjRR
aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh
ZcxJBvSCVXwONmSprVzTykEfSsePyY
wyLVoyYrVLynUzsMLFWQxwUKNsVcYz
UOAhslNldnBpITSrxPlpJbLSjJqgxN
xsGVsrYhkWAMufkQnRayieEkSDYrNq
yLejJuggADNxcgVtszjJIYKCxPweNG
hXsOFKGbMkTBPCfDXwjBNgQYswbaNW
BOEtSTHjIhdLAyMnbhyhRKKdfaTTpT
gHqQelVWmnkBHjAZTrGqdtlYAgoXNH
noryxHxVVyaMiGRSjdVlRwMas
3;three

Now you can produce files with very long lines. It would also possible to dump everything in one file (by using DBMS_LOB.CREATETEMPORARY + DBMS_LOB.APPEND ) or to dump all files in parallel…

Categories
18c sqlplus

dynamic linesize in 18.1

Whenever you select and describe in sqlplus it looks ugly

default: pagesize 14 linesize 80

change the default: it is often too large or too narrow

Let’s try WINDOW in sqlplus 18.1, which is available for download on Solaris / Linux / Windows

SQL> set lin window
SQL> sho lin
linesize 95 WINDOW
SQL> sho pages
pagesize 86

And look the result :

Almost perfect 🙂

I think it would nicer if DESC didn’t have this empty line

The cool thing with SET LINESIZE WINDOW is that it is dynamic (as I tested with CMD.EXE/windows and XTERM/Linux). Your window is too narrow, you make it bigger, re-run your select, it looks nicer

but… pagesize cannot be set to NON-WINDOW

SQL> set lin window
SQL> set pages 50000
SQL> sho pages
pagesize 21
SQL>

Categories
dba plsql sqlplus

execute sql script from pl/sql

When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of bugs…

Another approach is to use sqlplus.

Wait! calling sqlplus from plsql? Well… why not. For instance via external job

  1. create a table with sql scripts
  2. create table t(id number primary key, text clob);
    insert into t(id, text) values (
      42, 'update emp set sal=sal*2 /* XMas gift */;'
    );
    
  3. create a shell script, /tmp/ddl, that spool and executes T.text content
  4. #!/bin/ksh
    ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
    export ORACLE_HOME
    $ORACLE_HOME/bin/sqlplus -s -L scott/tiger <
    
  5. create a UNIX credential
  6. begin 
      dbms_scheduler.create_credential(
        'C','USER01','PW');
    end;
    /
    
  7. create and run an external job
  8. begin
      DBMS_SCHEDULER.CREATE_JOB(
        job_name             => 'J',
        job_type             => 'EXECUTABLE',
        number_of_arguments  => 1,
        job_action           => '/tmp/ddl',
        credential_name      => 'C');
      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        'J', 1, '42');
      DBMS_SCHEDULER.run_job('J');
    end;
    /
    

CREATE EXTERNAL JOB privilege is powerfull and your DBA won't like it 😉

Categories
12c sqlplus

column width change in 12c

In 11g I used to have 30 characters width in my dictionary

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          COMM
BONUS                          SAL
BONUS                          JOB
BONUS                          ENAME
DEPT                           LOC
DEPT                           DNAME
DEPT                           DEPTNO
EMP                            DEPTNO
EMP                            COMM
EMP                            SAL
EMP                            HIREDATE

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP                            MGR
EMP                            JOB
EMP                            ENAME
EMP                            EMPNO
SALGRADE                       HISAL
SALGRADE                       LOSAL
SALGRADE                       GRADE

18 rows selected.

Which was pretty nice to read with no setting.

Now in 12c it is ugly enought to make your eyes tired

SQL> select table_name, column_name from user_tab_columns;

TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT
DEPTNO

DEPT
DNAME

DEPT
LOC


TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
EMPNO

EMP
ENAME

EMP
JOB


TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
MGR

EMP
HIREDATE

EMP
SAL


TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
EMP
COMM

EMP
DEPTNO

BONUS
ENAME


TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
BONUS
JOB

BONUS
SAL

BONUS
COMM


TABLE_NAME
------------------------------------------------------------------------------------------------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SALGRADE
GRADE

SALGRADE
LOSAL

SALGRADE
HISAL


18 rows selected.

SQL>

This is due to a change of the width of the underlying column in the dictionary. Probably a good-news for our data modeling colleagues that it may be 128 in future.

But currently it is only 30 characters in 12c. So why not format it correctly ?

Simply add the format in $ORACLE_HOME/sqlplus/admin/glogin.sql

col TABLE_NAME for a30
col COLUMN_NAME for a30 

DISCLAIMER: it’s fine to add UNIQUE_KEY_LEVEL_NAME or REFERENCED_TRIGGER_NAME, but you may have application tables that have columns called OWNER or USER, it is probably safer to not assume they are all smaller than 30 chars, so don’t add common names.

Categories
sql sqlplus

Drop table if exists

The syntax that you are looking for is
docs.oracle.com/…/drop-table.html

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

Wait, this does not work !

SQL> drop table if exists t;
drop table if exists t
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

Okay. It was the Oracle MySQL book 😉

In the Oracle database, I have created my own droptableifexists script.

I went for a SQL*Plus no-plsql approach. PL/SQL is also possible but it generated different error messages (ORA-06512: at line 1) and different feedback (PL/SQL procedure successfully completed.)

So I check the dictionary, put a command to drop in the sqlplus buffer if a table exists, then run that command first.

droptableifexists.sql

set feed off ver off pages 0 newp none
def cmd="select 'OK: Table does not exist' from dual"
col cmd new_v cmd nopri
select 'drop table "'||table_name||'"' cmd
from user_tables
where table_name='&1';
1 select
del *
1 &cmd
set feedb 6 head off
/
set head on
del *
undef cmd
col cmd clear

Ok, let’s try

SQL> create table t(x number);

Table created.

SQL> @droptableifexists T

Table dropped.

SQL> @droptableifexists T
OK: Table does not exist

A PL/SQL approach could be

for f in (
select 'drop table "'||table_name||'"' cmd
from user_tables where table_name='T')
loop
execute immediate f.cmd;
end loop;

Try it :

SQL> create table t(x number);

Table created.

SQL> exec for f in (select 'drop table "'||table_name||'"' cmd from user_tables where table_name='T')loop execute immediate f.cmd;end loop

PL/SQL procedure successfully completed.

SQL> exec for f in (select 'drop table "'||table_name||'"' cmd from user_tables where table_name='T')loop execute immediate f.cmd;end loop

PL/SQL procedure successfully completed.

A bit easier to read. Same has to be done for USER, VIEW and so on.

PS: there are also other versions around catching for ORA-942, but ORA-942 may be ORA-943 in next release, try drop table t;; in 11g and 12c to see those things really happen !

Categories
dba sqlplus

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba.

Test case (21 is a not something to do in real life):

SQL> alter system set processes=21 scope=spfile;
System altered.
SQL> startup force quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> quit

From now on, sqlplus as sysdba is impossible.
$ sqlplus -s -L / as sysdba
ERROR:
ORA-00020: maximum number of processes (40) exceeded
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

Okay, if you really really need to run one script, you could connect with sqlplus -prelim and restart the database.

if :|sqlplus / as sysdba|grep ORA-00020
then
echo shu abort|sqlplus -prelim / as sysdba
echo startup quiet|sqlplus / as sysdba
fi

If ORA-20 is detected, then the database will be restarted.

Categories
dba security sqlplus

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo.

By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user.


SQL> ALTER USER app_user PROXY ONLY CONNECT;
SQL> CONNECT app_user/xyz
ERROR:ORA-28058: login is allowed only through a proxy

Categories
sql sqlplus

select pdf from sqlplus

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd


sqlplus -s scott/tiger <doc.pdf
set pages 0 lin 17000 long 1000000000 longc 16384
select document from emp where ename=user;
EOF

Obviously, it could also be a sound, a video or an image !

Categories
dba sqlplus

DBMS_METADATA.GET_DDL in sqlplus

Some settings matter when using dbms_metadata.

define large clobs

set long 1000000

large long columns

set longchunksize 32000

long lines

set linesize 32000

no trailing spaces

set trimspool on

no header

set heading off

no page size

set pages 0

no page feed (^L)

set newpage none

no start of page

set embedded on

no tabulator (^T)

set tab off

no feedback (n rows returned)

set feedback off

no echo

set echo off

Per default you get no terminator

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

When running the spooled code, allow blank lines

set sqlblanklines on

Get rid of &

set define off

Get rid of a leading #


set sqlprefix off

Get rid of . on a single line

set blockterminator OFF

To get rid of a trailing -, you may use set lin bigger than set longc and trimsp off, but I could not make it bug yet

CREATE TABLE T(x number default -
1);

would give 1 instead of -1 when run in sqlplus. But METADATA translates it to

CREATE TABLE "SCOTT"."T"
( "X" NUMBER DEFAULT -
1
)

So just forget about trailing dash for now.

Now try

SQL> set SQLBL ON SQLPRE OFF DEF OFF BLO OFF
SQL> create view v as select '
2
3 .
4 #?
5 &_date
6 ' x from dual;

View created.
SQL> set long 1000000 longc 32000 lin 32000 trims on hea off pages 0 newp none emb on tab off feed off echo off
SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;

CREATE OR REPLACE FORCE VIEW "SCOTT"."V" ("X") AS
select '

.
#?
&_date
' x from dual;

Now you are safer to use dynamic sql in sqlplus. But hardly ever 100% safe.

Plenty of useful transformation parameters there to get rid of storage, tablespace and others.

Categories
sqlplus

Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.

Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!

1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
excel-screenshot

2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.

3) transfer the excel file to your favorite platform

4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)

$ unzip /tmp/DynamicExcel.xlsx
Archive: /tmp/DynamicExcel.xlsx
inflating: [Content_Types].xml
inflating: _rels/.rels
inflating: xl/_rels/workbook.xml.rels
inflating: xl/workbook.xml
inflating: xl/styles.xml
inflating: xl/worksheets/sheet2.xml
inflating: xl/worksheets/_rels/sheet1.xml.rels
inflating: xl/worksheets/_rels/sheet2.xml.rels
inflating: xl/drawings/_rels/drawing1.xml.rels
inflating: xl/theme/theme1.xml
inflating: xl/worksheets/sheet1.xml
inflating: xl/drawings/drawing2.xml
inflating: xl/charts/chart1.xml
inflating: xl/drawings/drawing1.xml
inflating: xl/sharedStrings.xml
inflating: docProps/core.xml
inflating: docProps/app.xml

5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2

cd xl/worksheets

tr -d '\r' < sheet1.xml | sed 's, head
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
"row",
xmlattributes ((rownum+1) AS "r",
'1:2' AS "spans",
'0.2' AS "x14ac:dyDescent"),
XMLELEMENT ("c",
xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
XMLELEMENT ("v", d-date '1899-12-30')),
XMLELEMENT ("c",
xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
XMLELEMENT ("v", c)))
x
FROM ( SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
FROM v$backup_redolog
WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
AND TRUNC (SYSDATE) - 1 / 86400
GROUP BY TRUNC (next_time, 'DD')
ORDER BY 1);
EOF
tr -d '\r' < sheet1.xml | sed -n 's,.*,,p' > tail
cat head body tail | tr -d '\n' > sheet1.xml
rm head body tail

6) recreate zip file

$ cd ../..
$ zip -r /tmp/DynamicExcel2.xlsx *
adding: [Content_Types].xml (deflated 78%)
adding: docProps/ (stored 0%)
adding: docProps/core.xml (deflated 51%)
adding: docProps/app.xml (deflated 53%)
adding: _rels/ (stored 0%)
adding: _rels/.rels (deflated 60%)
adding: xl/ (stored 0%)
adding: xl/_rels/ (stored 0%)
adding: xl/_rels/workbook.xml.rels (deflated 71%)
adding: xl/workbook.xml (deflated 42%)
adding: xl/styles.xml (deflated 56%)
adding: xl/worksheets/ (stored 0%)
adding: xl/worksheets/sheet2.xml (deflated 45%)
adding: xl/worksheets/_rels/ (stored 0%)
adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%)
adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%)
adding: xl/worksheets/sheet1.xml (deflated 81%)
adding: xl/drawings/ (stored 0%)
adding: xl/drawings/_rels/ (stored 0%)
adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%)
adding: xl/drawings/drawing2.xml (deflated 58%)
adding: xl/drawings/drawing1.xml (deflated 61%)
adding: xl/theme/ (stored 0%)
adding: xl/theme/theme1.xml (deflated 79%)
adding: xl/charts/ (stored 0%)
adding: xl/charts/chart1.xml (deflated 85%)
adding: xl/sharedStrings.xml (deflated 22%)

7) Check it

DynamicExcel2.xlsx

Categories
sqlplus unix

return code and sqlplus

Calling a shell script from within sqlplus is buggy…

I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 because some metalink guru closed 3798918 as not reproducible.

As written in return code, host does not return the correct code


SQL> host exit 7
SQL> def _RC
DEFINE _RC = "0" (CHAR)

If you never use _RC, you may believe you are safe. But watch this :

SQL> get foo.sh list
1 #!/bin/sh
2 if /bin/false
3 then
4 echo this is wrong
5* fi
SQL> host ./foo.sh
this is wrong

The return code not being set is not only affecting the _RC variable, but it is also affecting all subshells !

Note this is not reproducable with SQLPLUS /NOLOG

SQL> host false
SQL> def _rc
DEFINE _RC = "1" (CHAR)
SQL> conn x/x
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> host false
SQL> def _rc
DEFINE _RC = "0" (CHAR)

After my (failed or successfull) tentative to connect as x/x, it is reproducible again

Categories
dba sqlplus

tnsping and instant client

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance.

I just created one function in my .profile

whence tnsping >/dev/null 2>&1 ||
tnsping() {
:| sqlplus -L -s x/x@$1 | grep ORA- | (grep -v ORA-01017 || echo OK)
}

and tested it

$ tnsping db999
ORA-12154: TNS:could not resolve the connect identifier specified
$ tnsping db01
OK
$ tnsping db02
ORA-12541: TNS:no listener

Categories
dba sqlplus

What does # mean in sqlplus?

The script used to be

shutdown abort

it has been replaced by

#shutdown abort
shutdown immediate

Let’s try !


SQL> #shutdown abort
ORACLE instance shut down.
SQL> shutdown immediate
ORA-01012: not logged on

sqlplus just silently ignored the # symbol and executed the first statement.

Thanks to Maxim comment, here is a new case to explain the sql prefix #

SQL> sho sqlpre
sqlprefix "#" (hex 23)
SQL> select
2 #prompt hello world
hello world
2 * from dual;

D
-
X

While within (or outside of) an sqlplus block, you can tell sqlplus to immediately run a sqlplus statement

The correct syntaxes to put comment are documented in Placing Comments in Scripts

SQL> remark shutdown abort
SQL> rem shutdown abort
SQL> -- shu abort
SQL> /* shutdown abort */

Categories
sqlplus windows

EURO symbol, sqlplus, cmd.exe and various issues

One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos).

Why?

First, the character set did not support it.

select * from v$nls_parameters where PARAMETER like '%CHARACTERSET%';

PARAMETER VALUE
------------------------------ ---------------
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

If you are still using WE8ISO8859P1, consider migrating to WE8MSWIN1252 using csalter

sqlplus "/ as sysdba" @?/rdbms/admin/csminst
csscan "'sys/sys as sysdba'" full=y tochar=we8mswin1252 array=1024000 process=5
sqlplus "/ as sysdba" @?/rdbms/admin/csalter.plb

It is not always that straight forward, check output from csscan (scan.*) carefully before running csalter.

Ok, now retry

H:\>set NLS_LANG=american_america.we8pc850

H:\>sqlplus.exe scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 10 11:28:01 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-

Not good!

Obviously, the PC850 client character is not good enough. Let’s switch to mswin1252 on the client.


H:\>chcp 1252
Active code page: 1252

H:\>set NLS_LANG=american_america.we8mswin1252

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-
Ç

Well, what’s missing now? The font ! Let’s change it from “Raster Fonts” to “Lucida Console”. Either by clicking on the command com properties, or even dynamically with that gem (tested on XP) !


H:\>type Lucida.cs
using System;
using System.Runtime.InteropServices;

public class Lucida
{
const int STD_OUT_HANDLE = -11;

[DllImport("kernel32.dll", SetLastError = true)]
static extern int SetConsoleFont(IntPtr hOut, uint dwFontSize);

[DllImport("kernel32.dll", SetLastError = true)]
static extern IntPtr GetStdHandle(int dwType);

public static void Main()
{
SetConsoleFont(GetStdHandle(STD_OUT_HANDLE), 6);
}
}

H:\>csc Lucida.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.
H:\>Lucida

H:\>sqlplus.exe scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select chr(128) from dual;

C
-

Categories
linux sqlplus unix

send graph per mail from sqlplus

How to send a graph with a single command from your database to your mail in Unix?

I tried this (gnuplot is available for Solaris, AIX and most Unix derivates) :

echo '
set hea off pages 0 feed off
prom set title "salaries of EMP"
prom unset key
prom unset xtics
prom unset xlabel
prom set term png
prom
prom plot "-" with circle
select row_number() over (order by sal),sal from emp;
prom e
prom quit
' |
sqlplus -s scott/tiger |
gnuplot |
uuencode emp.png |
mailx email@example.com

The picture is sent as attachment.

If you want to embed your png in an HTML mail, use sendmail

/usr/sbin/sendmail -t <Below a graph...


--MSGBOUNDARY--
--FILEBOUNDARY
Content-Type: image/png
Content-Disposition: inline; filename="png.png"
Content-Transfer-Encoding: base64
Content-Id:

iVBORw0KGgoAAAANSUhEUgAAAoAAAAHgCAMAAAACDyzWAAABIFBMVEX///8AAACgoKD/AAAAwAAA
gP/AAP8A7u7AQADIyABBaeH/wCAAgEDAgP8wYICLAABAgAD/gP9//9SlKir//wBA4NAAAAAaGhoz
MzNNTU1mZmZ/f3+ZmZmzs7PAwMDMzMzl5eX////wMjKQ7pCt2ObwVfDg///u3YL/tsGv7u7/1wAA
/wAAZAAA/38iiyIui1cAAP8AAIsZGXAAAIAAAM2HzusA////AP8AztH/FJP/f1DwgID/RQD6gHLp
lnrw5oy9t2u4hgv19dyggCD/pQDugu6UANPdoN2QUEBVay+AFACAFBSAQBSAQICAYMCAYP+AgAD/
gED/oED/oGD/oHD/wMD//4D//8DNt57w//Cgts3B/8HNwLB8/0Cg/yC+vr7MAY9LAAAKJElEQVR4
nO3d0XaiSBSGUV15/3ee7unEoKLWwYIfrL0vJhNDmLn4FgQoPacTAAAAAAAAAAAAbOl8Ps++uvAX
oWhhgH+2+N3o/L/T/9+f/33X8f+Qj7awlatf+/7m75fvMBXII9/Hp5/D1M8//2Vz+dnMlje/frXL
y48EyHOTPK5LmqZz+cddT5cXb6M8TwJ0Cuax6eHqND0Cnq9ivPR0f7CbffF0VSw8dhXR7cHussXN
YW/ud29+LkBeuz2xPg/w0SlYgCw1ubQ4T06zk1Pw9MXfX7j99Ztd/qYqQAAAAAAAAACGMP983cpJ
NjL7rgVrx9nKZHGvANneb3Lnuy+XTaCsFuF5LsKfH3ctnuP5Kr38TznAx6dgAY5uzQBnyxMgV2ZT
e9pfezXfp+vZL8Vd8anWDPA1AQ5PgGTNxPa8PwHSkwDJusvtRX8CpKuvr+ff3xEgfU2Te5mfAOnv
6193Xw35CZBVfLXVdxIgYQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIESJQAiRIg
UQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIESFR9VNf5fmprfVfwT6GayWQ4k5Lo
pL2a8/nnCChAuimM6roeEjcZmHnZojZ8k9FVipkc7IxrpZ/2AC+1mpZJP+WrYAHSUzFA41rpy41o
ogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJ
kCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEmVQDVEL5gUb1UU/
5XnBAqSn+rzg892XyxbmBVOxYF7wfX2OgLyjOC/YKZi+zAsmym0YotyIJkqARAmQKAESJUCiBEiU
AIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAmQKAES
JUCiBEiUAIkSIFECJEqARAmQKAESJUCiBEiUAIkqzIr7GRNyPy6kuCu4KM+Ku/3OpCTeUR7Vdf6p
ToB0UD4F/w5uNa6V91SL+T3/GtdKJ0sDdAqmiwUXIQKkn+LfgMa10pcb0UQJkCgBEiVAogRIlACJ
EiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVA
ogRIlACJEiBRAuR0+vqj53YFAhzed1Qv22rdrkaAg5vm9Cyt1u2qBPjBXp8xb3/+aPvW7eoE+Kma
zph3P3sUYON2dQL8TG1nzJkfzG7but0CAvxEzWfWppcESFHjGfOt2DoVaE7IB3qroZ0GaFLSgXxi
gP9vK8BDePPPuMVXxovUx7VeT8o0rnV/DhPggnGt9/U5Au7OYQL8yyn44zT3cqgAXYQcxkb3/NyG
GdfzZ7wfGuCmu+Kxl894Bch6Wp7xbnLPz6O4EbU94xUgK2m7QC2cMS3HoqD/AcuCVArWOGNakk+r
N863T1dFe1MSTQoBFs+Y3pZJg8rtuLXOmN0J8DCKj2TXOWN2J8DDqK8JWOGM2Z0AD2PVRSkxAjwM
AW63K+asuSYgRoDHIcDNdsUcAW62K2atuCglRoAHIsCtdsW89VZFxQjwSNZbFRUjwGM5zDPeVgI8
moM8420lwAM6wjPeVgIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIEuB+ftMilmQB3
4sOW+TUT4C583ELnZgLcgQ98q0ez8rDC36+XF6u74sYHvtmt2YJRXdffGdX1tk98u2+zcoA/U1sF
2I0AW7edTik0L7iXj/zIlya1Yi7bmhfc17gB/lW4CJn8m1NwRwJs2m7u6kOAHXzmB582aw9w7v6L
2zDvE+AOdzUSAe5wVyMR4A53NRQXIfvb1VAEuL9dDUWA+9vVWDyK292uxiLA3e1qMJZj7W1Xg7Eg
dW+7Go4l+fva1YC8KWlPuxqTt2XuZlcMQ4BECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIE
SJQAiRIgUQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZCo4rhWc0Loa8G4VpOS6EeARNXHtZ7v
vlx+bFwrFQvGtd7X5wjIO4rjWp2C6cu4VqKMayXKjWiiBEiUAIkSIFECJEqARAmQKAESJUCiBEiU
AIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAlwfV9/pP8fdkuAK/uOT4MPCHBV0+wkOEeA
K7pNToL3BLiiu94EeEeA65nJTYG3BLgeATYQ4GpmY1PgDQGuRoAtBLjcixvMAmwhwIVe3mB+8LoC
rwlwkYYbzAJsIsAFmm4wC7CJABdousEswCYCrGu8v+cipIUA6wTYkQDLWsMSYIvKqK7To3Eh1V0d
W3NYHsU1KIzq+lfc9zcDT0oSYE/Nk5ImR8CxAyxc3VqO9Vr5FDwztfWywRDjWisBWpD6VHVc6++/
jDyutXR/z5L8V5YG6BTc9rI3Jb2w+BQ8bID12yvelvmEca1l7u/15EZ0mQB7EmCd+3sdCbBOgB0J
8M7rawY3mPsR4LWmuyZuMPcjwKnm+8ZuMPciwF+lA5sbzH0I8Ff1Tzs3mDs4foDNGbza0MVtwsED
bD4RNmwowIRDB9j1msEDjogDB9h8zdC2oQAjjhzgyxdKGwow4rgBNv/J1rah95FnCPDp7wpwbYcN
8J33ps29KMAMAT7bTICrE2B5h/R01ACbD1jvbSjAtQnw6YYCXJsAiy/RlwCLL9HXfgPs9Rn0bzz2
0N/6dhrg68UrawRopf32dhlg18UrlYsLK+03t8MAGw9EnR/FXf5jVtpvao8Bvnzh0asdAjxZab+t
/QW4PKz3lmMRceQA+y5IJWJ3Aa5yzeDiYrcOHWDhmsHFxU4dPMBT4ZrBxcUe7S1Ay/IGI0CiBEiU
AInaPMAFn9Civw+2bYDLPqFFgB9sywCbbgcLcCzbBdj6QMzC5KFUR3UtH1TTuiRAgENZMC942aiu
9q4sXhlJeV7wBgFavDKQ+rDC61GtrfOCS9cWFq+MYcG84Pv6Go+AxYtbi1dGsdW41vrdFYtXhrBR
gJ6wMW+jecECZN5GN6IFyDwBErXVoziPeJnVM8AunyXEWLoeAXt8lhBj6X0Kriyz0h8r/A347mcJ
MZQVLkIscqHdGlfBb36UCyPZLkCLXJixyn3A9z/KhVFsG+DJIheubR4gTK3zKE6BNBIgUQIkSoBE
uQghSoBECZCoLR/FwR0BErXdciyYsdmCVJiz2ZJ8mLPVm5Jg1lZvy4RZe5sTwmAESJQAiRIgUQIk
SoBECZAoARIlQKIESJQAiRIgUcVq5seFLNoVnBYEePlSnJYJc5YcAQVINwuOgE/HtULNgmYfjGuF
TTyalgmrezauFdb3fda+/gIAAAAAAAC0+Q9+0RhW4FaMYwAAAABJRU5ErkJggg==
--FILEBOUNDARY--
EOF

Sendmail syntax and more tips on : Sending Emails With Sendmail – Part 3

Categories
dba sql sqlplus

On implicit commit

An explicit commit is when you issue a COMMIT statement

SQL> create table t(x number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

An implicit commit is when a commit is issued without your approval.

ex: AUTOCOMMIT (default is OFF)
SQL> set autoc on
SQL> insert into t values(1);

1 row created.

Commit complete.

ex: EXITCOMMIT (default is ON)
SQL> set autoc off exitc on
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/tiger
Connected.
SQL> select * from t;
X
----------
1

before / after a successful DDL statement
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t(x);

Index created.

SQL> rollback;

Rollback complete.

SQL> select * from t;
X
----------
1

Before / after an unsuccessful DDL statement, sometimes :
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t(blabla);
create index i on t(blabla)
*
ERROR at line 1:
ORA-00904: "BLABLA": invalid identifier

SQL> rollback;

Rollback complete.

SQL> select * from t;
X
----------
1

But not always :
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t();
create index i on t()
*
ERROR at line 1:
ORA-00936: missing expression

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

In the last case, no DDL was executed, but in the case before that, the DDL was executed and failed.

If you want to commit, use COMMIT 🙂

Categories
dba sqlplus

sqlplus -prelim

If you cannot login to the database, for instance due to ORA-00020 maximum number of processes exceeded, then chance exists that you could use the -prelim option.

Documented in note 121779.1 for sqlplus version 10.1 and later :
In some cases, no connections are allowed on the instance (in some ORA-20 situations for example).
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to
generate traces.
sqlplus -prelim / as sysdba

Only sysdba connection is possible.
sqlplus -prelim system/manager

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 4 10:38:36 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

ERROR:
ORA-24300: bad value for mode

And very little access is granted
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

You can shutdown abort and then restart your database, instead of rebooting your server where other instances may be running.

This is the ultimate chance before reboot. Before this, consider disconnecting / killing some user session to get a regular sqlplus / as sysdba

Categories
sql sqlplus

CSV part 4, fast !!

I got some comments that my other csv solutions were slow to export gigabytes of data.

One more try.

thanks to the feedbacks, I provided a new version

This could generate very large files in just a few minutes (instead of hours).

I use bulk collect and utl_file to boost performance

CREATE TYPE collist IS TABLE OF VARCHAR2 (4000)
/

CREATE OR REPLACE PROCEDURE bulk_csv (directory_name VARCHAR2,
file_name VARCHAR2,
query VARCHAR2)
AUTHID CURRENT_USER
IS
-- $Id$
fh UTL_FILE.file_type;
stmt VARCHAR2 (32767) := NULL;
header VARCHAR2 (32767) := NULL;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2 (32767);

TYPE cola IS TABLE OF collist
INDEX BY BINARY_INTEGER;

res cola;
rcur SYS_REFCURSOR;
current_line VARCHAR2 (32767);
next_line VARCHAR2 (32767);

BEGIN
curid := DBMS_SQL.open_cursor;
DBMS_SQL.parse (curid, query, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS (curid, colcnt, desctab);

FOR i IN 1 .. colcnt
LOOP
DBMS_SQL.DEFINE_COLUMN (curid,
i,
namevar,
32767);
END LOOP;

IF DBMS_SQL.execute (curid) = 0
THEN
FOR i IN 1 .. colcnt
LOOP
IF (i > 1)
THEN
header := header || ';';
stmt := stmt || ',';
END IF;

header := header || desctab (i).col_name;
stmt :=
stmt
|| CASE
WHEN desctab (i).col_type IN
(DBMS_SQL.Varchar2_Type,
DBMS_SQL.Char_Type)
THEN
'"'||desctab (i).col_name || '"'
WHEN desctab (i).col_type IN
(DBMS_SQL.Number_Type,
DBMS_SQL.Date_Type,
DBMS_SQL.Binary_Float_Type,
DBMS_SQL.Binary_Bouble_Type,
DBMS_SQL.Timestamp_Type,
DBMS_SQL.Timestamp_With_TZ_Type,
DBMS_SQL.Interval_Year_to_Month_Type,
DBMS_SQL.Interval_Day_To_Second_Type,
DBMS_SQL.Timestamp_With_Local_TZ_type)
THEN
'to_char("' || desctab (i).col_name || '")'
WHEN desctab (i).col_type = DBMS_SQL.Raw_Type
THEN
'rawtohex("' || desctab (i).col_name || '")'
WHEN desctab (i).col_type = DBMS_SQL.Rowid_Type
THEN
'''unsupport datatype : ROWID'''
WHEN desctab (i).col_type = DBMS_SQL.Long_Type
THEN
'''unsupport datatype : LONG'''
WHEN desctab (i).col_type = DBMS_SQL.Long_Raw_Type
THEN
'''unsupport datatype : LONG RAW'''
WHEN desctab (i).col_type = DBMS_SQL.User_Defined_Type
THEN
'''unsupport datatype : User Defined Type'''
WHEN desctab (i).col_type = DBMS_SQL.MLSLabel_Type
THEN
'''unsupport datatype : MLSLABEL'''
WHEN desctab (i).col_type = DBMS_SQL.Ref_Type
THEN
'''unsupport datatype : REF'''
WHEN desctab (i).col_type = DBMS_SQL.Clob_Type
THEN
'''unsupport datatype : CLOB'''
WHEN desctab (i).col_type = DBMS_SQL.Blob_Type
THEN
'''unsupport datatype : BLOB'''
WHEN desctab (i).col_type = DBMS_SQL.Rowid_Type
THEN
'''unsupport datatype : ROWID'''
WHEN desctab (i).col_type = DBMS_SQL.Bfile_Type
THEN
'''unsupport datatype : BFILE'''
WHEN desctab (i).col_type = DBMS_SQL.Urowid_Type
THEN
'''unsupport datatype : UROWID'''
ELSE
'''unsupport datatype : '||desctab (i).col_type||''''
END;
END LOOP;

stmt := 'select collist(' || stmt || ') from (' || query || ')';

fh :=
UTL_FILE.fopen (directory_name,
file_name,
'W',
32767);

begin
OPEN rcur FOR stmt;
exception
when others then
dbms_output.put_line(stmt);
raise;
end;
LOOP
FETCH rcur
BULK COLLECT INTO res
LIMIT 10000;

current_line := header;
next_line := NULL;

FOR f IN 1 .. res.COUNT
LOOP
FOR g IN 1 .. res (f).COUNT
LOOP
IF (g > 1)
THEN
next_line := next_line || ';';
END IF;

IF ( NVL(LENGTH (current_line),0)
+ NVL(LENGTH (next_line),0)
+ NVL(LENGTH (res (f) (g)),0)
+ 5 > 32767)
THEN
UTL_FILE.put_line (fh, current_line);
current_line := NULL;
END IF;

IF (NVL(LENGTH (next_line),0) + NVL(LENGTH (res (f) (g)),0) + 5 > 32767)
THEN
UTL_FILE.put_line (fh, next_line);
next_line := NULL;
END IF;

next_line := next_line || res (f) (g);
END LOOP;

current_line :=
CASE
WHEN current_line IS NOT NULL
THEN
current_line || CHR (10)
END
|| next_line;
next_line := NULL;
END LOOP;

UTL_FILE.put_line (fh, current_line);
EXIT WHEN rcur%NOTFOUND;
END LOOP;

CLOSE rcur;

UTL_FILE.fclose (fh);
END IF;

DBMS_SQL.CLOSE_CURSOR (curid);
END;
/

CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

EXEC bulk_csv('TMP','emp.csv','SELECT * FROM EMP ORDER BY ENAME')


EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
7876;ADAMS;CLERK;7788;1987-05-23 00:00:00;1100;;20
7499;ALLEN;SALESMAN;7698;1981-02-20 00:00:00;1600;30;30
7698;BLAKE;MANAGER;7839;1981-05-01 00:00:00;2850;;30
7782;CLARK;MANAGER;7839;1981-06-09 00:00:00;2450;;10
7902;FORD;ANALYST;7566;1981-12-03 00:00:00;3000;;20
7900;JAMES;CLERK;7698;1981-12-03 00:00:00;950;;30
7566;JONES;MANAGER;7839;1981-04-02 00:00:00;2975;;20
7839;KING;PRESIDENT;;1981-11-17 00:00:00;5000;;10
7654;MARTIN;SALESMAN;7698;1981-09-28 00:00:00;1250;140;30
7934;MILLER;CLERK;7782;1982-01-23 00:00:00;1300;;10
7788;SCOTT;ANALYST;7566;1987-04-19 00:00:00;3000;;20
7369;SMITH;CLERK;7902;1980-12-17 00:00:00;800;;20
7844;TURNER;SALESMAN;7698;1981-09-08 00:00:00;1500;0;30
7521;WARD;SALESMAN;7698;1981-02-22 00:00:00;1250;50;30

Categories
dba security sql developer sqlplus toad

On using Toad against a database

I got this question once again today in a previous post.

What’s wrong by using Toad against a database?

The worst case scenario:
– some non-technical staff is clicking around in your production database with read-write access 🙁

The best-case scenario :
– nobody has access to your database 🙂

Here is a short list on how you could protect your data :
– Give the right privilege to the right person. DBA role to the DBA, CREATE TABLE/CREATE INDEX to the developer, INSERT/UPDATE/DELETE to the application
– Restrict access to your database server. Use some firewall. Allow only the dba workstation and the application server to the Production environment

What if the end-user PC needs access to the Production database with a powerfull user? This often happend in real world. A fat client is installed on the PC, the password is somehow hardcoded, the privileges granted to the hardcoded user are uterly generous…

It is not a bad practice in this case to block access to the database server to Toad/SQLPLUS and thelike. This will very ineffeciently prevent some garage-hacker from corrupting your database, but it will prevent your sales / marketing colleagues from deleting data, locking tables and degrading performance. This could be done by some login triggers or, my preference, some administrative measures like information, auditting and sanctions.

Categories
linux sqlplus unix windows

What is the current setting of NLS_LANG in sqlplus?

I just learnt a neat trick from Oracle Support.

How do you see the current value of NLS_LANG in SQLPLUS ?

HOST is not the right answer.

E.g.:
Unix:

SQL> host echo $NLS_LANG
AMERICAN_SWITZERLAND

Windows:

SQL> HOST ECHO %NLS_LANG%
%NLS_LANG%

The correct setting is revealed by @.[%NLS_LANG%]
E.g.:
Unix:

SQL> @.[$NLS_LANG]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

Windows:

SQL> @.[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

It could well be that both return the same answer, but not necessarly, as shown above.

The unix discrepancy is related to the subshell created by HOST. The subshell may read some .profile and overwrite the value of NLS_LANG

In Windows, the NLS_LANG setting may be set by sqlplus according to some registry entries

Categories
sqlplus

Send sqlplus reports in Excel format

I posted earlier about sending html mail.

Here an example on how to send -fake- excel per mail

echo "select * from emp;"|
sqlplus -M "HTML ON" -s scott/tiger|
uuencode emp.xls|
mail laurentschneider@example.com

May this be of some help to my readers !

Categories
sqlplus

Send html report per email from sqlplus

Your business partner wants to receive some daily mail with an sql query output in it. It does not need to be ultra-fancy, but some colors and titles would not hurt.

Here is the report in SQL:

select dname, sum(sal) from emp join dept using (deptno) group by rollup(dname);

Ok, let’s do the report within sqlplus.

rep.sql

set echo off numf 999G999G999G999 lin 32000 trims on pages 50000 head on feed off markup html off
alter session set nls_numeric_characters='.''' nls_date_format='Day DD. Month, YYYY';
spool /tmp/rep.html
prompt To: laurentschneider@example.com
prompt From: laurentschneider@example.com
prompt Subject: Daily department report
prompt Content-type: text/html
prompt MIME-Version: 1.0
set markup html on entmap off table 'BORDER="2" BGCOLOR="pink"'
prompt Good morning,
prompt Here is the department report per &_DATE
prompt Kind Regards,
prompt Your IT Operations

prompt

List of departments with the total salaries of their employees

select dname "Department", sum(sal) "Salary" from emp join dept using (deptno) group by rollup(dname);
spool off
host /usr/sbin/sendmail -t

Then simply call it from sqlplus (you may want to configure the sendmail part)

SQL> @rep

check your mail :

To: laurentschneider@example.com
From: laurentschneider@example.com
Subject: Daily department report
Good morning,

Here is the department report per Friday 15. April , 2011

Kind Regards,

Your IT Operations

List of departments with the total salaries of their employees

Department Salary
ACCOUNTING 8'750
RESEARCH 10'875
SALES 9'400
  29'025

It is pretty easier to maintain than APEX, but the capabilities are not that rich...

Categories
11g dba sqlplus

List events in session, process or system

There is a new command in 11g to display the current events, which is oradebug eventdump.

For instance :
SQL> alter session set events '10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3';

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace level=12
942 trace name ERRORSTACK level 3

Read metalink note 436036.1

In 10g and before, the command was oradebug dump events 1 and the list was dumped in a trace file, 11g directly outputs to the console.

Note there is no backward compatibility with unsupported tools like oradebug.
In 11g you will get an ORA-76 with dump events
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 1
ORA-00076: dump EVENTS not found
$ oerr ora 76
00076, 00000, "dump %s not found"
// *Cause: An attempt was made to invoke a dump that does not exist.
// *Action: Type DUMPLIST to see the list of available dumps.

Categories
sqlplus windows

What’s your favorite shell in Windows?

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC.

PS> $stmt = "set hea off`n"
PS> $stmt += "select 'hello world' from dual;"
PS> $res = ($stmt | sqlplus -s scott/tiger)
PS> $res

hello world

Pretty cool 🙂

Categories
dba sql sqlplus

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------
114352430549782

SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

STEP_ID
---------------

Categories
sql sqlplus

Do you know the ORA- nonerrors?

In one of my script, I am checking at the end for any ORA- error. And if I have any ORA- error, I quit with an error.

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ERROR:
ORA-28002: the password will expire within 10 days

X

My password will expire in 10 days. And this screw up my day 🙁

Categories
dba sqlplus

Difference between rollbac and rollback

What is the difference between rollbac and rollback?


SQL> create table t as select 1 x from dual;

Table created.

SQL> update t set x=2;

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update t set x=3;

1 row updated.

SQL> rollbac to savepoint a;
Rollback complete.
SQL> select * from t;
X
----------
1

WTF! rollbac does not seem to work correctly 😉

Actually, ROLL, ROLLB, ROLLBA and ROLLBAC are not valid SQL statements. However, SQLPLUS recognizes it a sqlplus statement but this behavior is not documented.


SQL> del *
SQL> roll
Rollback complete.
SQL> list
SP2-0223: No lines in SQL buffer.

The statement does not end with semi-column and is not stored in the SQLPLUS buffer.

So it is not a SQL command.

Categories
Blogroll dba sql sqlplus

return size of to_char

The width of a columns is known before execution.

Well, in most cases…


SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH')
FROM DUAL;
TO_CHAR(
--------
SAMEDI

The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.


SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT

Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED
EIGHTY-EIGHT

again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

let’s try with spelling the nanoseconds :

select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION

here 78 is a really bad guess… the nanoseconds could be 100 character long !

What happened then :

select to_char(timestamp '0001-01-01 00:00:00.777777777',
'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
*
ERROR at line 1:
ORA-01877: string is too long for internal buffer

The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

It simply means Oracle sets the width column to something too small to contain the result.

By setting the column to something longer, the query should work…

select cast(to_char(timestamp '0001-01-01 00:00:00.
777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN

Ok, let’s do something perverse :mrgreen:

Let set the CURSOR_SHARING to FORCE !

Have a look :


SQL> alter session set cursor_sharing='FORCE';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION

the width of the column is evaluated to 75. 75 however is not the maximum length!


SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
*
ERROR at line 1:
ORA-01801: date format is too long for internal buffer

Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

Let’s revert to EXACT for confirmation.

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION

SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED
SEVENTY-SEVEN

Categories
Blogroll dba sqlplus

SET LONGCHUNKSIZE

I noticed a side effect of SET LONGC today…

The default setting for LONG and LONGC is 80. This is quite annoying when you SELECT TEXT FROM ALL_VIEWS as it truncates the text to 80.

So why not setting it to the maximum?

Let’s first demo the usage of LINESIZE, LONG and LONGCHUNKSIZE

SQL> create view v as select
'12345678910111213141516171819202' x
from dual;

View created.

SQL> set lin 80 long 20 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101

SQL> set lin 80 long 1000000000 longc 20
SQL> select text from user_views where view_name='V';

TEXT
--------------------
select '123456789101
11213141516171819202
' x from dual

SQL> set lin 80 long 1000000000 longc 1000000000
SQL> select text from user_views where view_name='V';

TEXT
-----------------------------------------------------
select '12345678910111213141516171819202' x from dual

When I use a LONG setting smaller than the length of the TEXT column, I got it truncated. When I use a huge LONG setting but a LONGCHUNKSIZE setting smaller than the length of the TEXT column, I got it wrapped. When both are huge, it seems I am getting the expecting result. So why not setting SET LONG 2000000000 LONGC 2000000000 in your login.sql ?

Have a look :


SQL> SET LONG 10 LONGC 10 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X
----------
X

Elapsed: 00:00:00.01
SQL> SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:00.00
SQL> SET LONG 1000000000 LONGC 1000000000 LIN 80 TIMI ON
SQL> SELECT TO_CLOB('X') FROM DUAL;

TO_CLOB('X')
------------
X

Elapsed: 00:00:02.06

2 seconds to select 1 character from dual !

I also tried to set it to 2000000000 but since I do not have 2Gb of free memory my notebook started swapping so badly I could not even move my mouse 🙁

Categories
Blogroll sql sqlplus

take care of minus !

Imagine this script (10gR2) :
set echo on
select BINARY_DOUBLE_INFINITY -
BINARY_DOUBLE_INFINITY from DUAL;

Run it and you would will get an expected result!

SQL> select BINARY_DOUBLE_INFINITY -
> BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY
----------------------
Inf

The issue in sqlplus is that – at the end of line means “query continues next line”. The correct answer of Inf-Inf is Nan.

SQL> select BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY from DUAL;

BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
---------------------------------------------
Nan

Categories
Blogroll dba sql sqlplus

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 be more than 2000 bytes long.

Another way to print your blob content is to use DBMS_LOB.CONVERTTOCLOB


var c clob
set autoprint on
declare
b blob;
dest_offset integer := 1 ;
src_offset integer := 1 ;
lang_context integer:= 1 ;
warning integer;
begin
select b into b from t for update;
dbms_lob.createtemporary(:c,true);
dbms_lob.converttoclob(
:c, b, DBMS_LOB.LOBMAXSIZE,
dest_offset, src_offset,
1, lang_context, warning);
end;
/
C
---
XXX