Category Archives: 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 <<EOF |xxd -p -r >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 !

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,<row r="2".*,,' > 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,.*</sheetData>,</sheetData>,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

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

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 </dev/null | 
      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

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 */

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
-
€

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 <<EOF
To: email@example.com
From: email@example.com
Subject: EMP
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="FILEBOUNDARY"

--FILEBOUNDARY
Content-Type: multipart/alternative; boundary="MSGBOUNDARY"

--MSGBOUNDARY
Content-Type: text/html
Content-Disposition: inline

<html><body>Below a graph...<br/>
<img src="cid:png"/><br/></body></html>
--MSGBOUNDARY--
--FILEBOUNDARY
Content-Type: image/png
Content-Disposition: inline; filename="png.png"
Content-Transfer-Encoding: base64
Content-Id: <png>

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

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 :)

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

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

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.

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

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 <i>Good morning, </i>
prompt <i>Here is the department report per &_DATE</i>
prompt <i>Kind Regards, </i>
prompt <i>Your IT Operations</i>

prompt <br/><h3>List of departments with the total salaries of their employees</h3>
select dname "Department", sum(sal) "Salary" from emp join dept using (deptno) group by rollup(dname);
spool off
host /usr/sbin/sendmail -t </tmp/rep.html
quit

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…

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.

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
---------------

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 :(

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.

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

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 :(

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

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

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 errorlog !


SQL>set errorl on
SQL> @foo

Table created.

1 row created.

1 row created.

insert into t(x) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

1 row created.

Commit complete.

SQL> set errorl off
SQL> select timestamp,script,statement,message from sperrorlog;
TIMESTAMP  SCRIPT  STATEMENT
---------- ------- ---------------------------
MESSAGE
---------------------------------------------------------
11:18:56   foo.sql insert into t(x) values (2)
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

There is also a huge bonus :-D

You can use it with 9i and 10g databases too! Only the client must be 11g. To download the 11g client only, go to Oracle E-Delivery Website

Even small, this is one of my favorite new features!

read user-input in plsql

How can I read user input in plsql?

kind of


begin
  write('Enter a value for x : ');
  read(x);
  write('you enterred '||x);
end;
/

the short answer is : you cannot do that.

Ok, let’s try to do it in Linux !


$ cat interactiveplsql.sql
set feedb off

create or replace directory tmp as '/tmp';

declare
inFile utl_file.file_type;
outFile utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile := utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x : ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you enterred '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/

quit

$ mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger @interactiveplsql &
);cat>/tmp/in
Enter a value for x :
ABC123
you enterred ABC123

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 a table.


SQL> sho pages
pagesize 14
SQL> select empno,ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

This is rather an annoying effect of the default setting than a feature and there is no set pagesize unlimited. The only way to have the header only once, is to set the pagesize to the maximum or use this trick : set pages 0 emb on newp none. Unfortunately, the later does not work for HTML reporting.

One of the less known and advanced sql*plus capabilities is the title of the page.

Today on the developpez.net forums I had a question about generating a describe for each table in user_tables.

The short answer is select * from user_tab_columns

In my output, I want to have each table on a separate page, with the table_name in the title, and the column names, not null options and datatypes in the page.

First I want to have a page per table, this I can define with break


bre on table_name ski page

I want to have the table name in the title of the page, but not as a column. I add some blank lines in the top title and in the bottom title.


col table_name new_v table_name nopri
tti le table_name s 2
bti s 1

Finally I set the pagesize to something bigger than the maximum number of columns of tables plus 6 for header and title

set pages 0
col pages new_v pages nopri
select max(count(*))+6 pages
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’)
group by table_name;
set pages &pages

Now the select

select
table_name,
column_name “Name”,
decode(nullable,’N’,’NOT NULL’) “Null?”,
DATA_TYPE||
case when DATA_TYPE in (‘NUMBER’,’FLOAT’)
and (data_precision is not null
or data_scale is not null) then
‘(‘||nvl(DATA_precision,38)||
case when data_scale!=0 then
‘,’||DATA_SCALE
end
||’)’
when data_type like ‘%CHAR%’ then
‘(‘||DATA_LENGTH||’)’ end
“Type”
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’);

DEPT

Name                           Null?    Type
------------------------------ -------- --------------------
DEPTNO                         NOT NULL NUMBER(2)
DNAME                                   VARCHAR2(14)
LOC                                     VARCHAR2(13)

EMP

Name                           Null?    Type
------------------------------ -------- --------------------
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)

For a HTML report, set mark html on


set mark html on
/

DEPT
Name Null? Type
DEPTNO NOT NULL NUMBER(2)
DNAME   VARCHAR2(14)
LOC   VARCHAR2(13)

 
 

EMP
Name Null? Type
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)

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 in defining the column length, no bizarre xml format. Plain html, that is cool!

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

Simply great :-P

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> alter user scott identified by values 'F894844C34402B67';

User altered.

but this is unfair. The user will be prevented from logging for a while, the password expire policy will be reset.

Sometimes you can simply use


SQL> alter session set current_schema=scott;

Session altered.

but this does not really mean a “su”, because you do not change your privileges. You just change the “default” schema.

Another approach is to use the BECOME USER privilege and undocumented upicui OCI function. But this will not work in sqlplus.

Thanks to Jonathan Lewis post today Proxy Users, I could imagine using the proxy functionality in sqlplus to do a su


SQL> create user su identified by secret123;

User created.

SQL> alter user scott grant connect through su;

User altered.

SQL> connect su[scott]/secret123;
Connected.

The connect username[proxy]/password@db is documented in the 10gR2 SQL*Plus reference