Archive

Archive for the ‘sqlplus’ Category

send graph per mail from sqlplus

September 20th, 2011 4 comments

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

Tags:

On implicit commit

July 4th, 2011 3 comments

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

Tags:

sqlplus -prelim

July 4th, 2011 16 comments

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

Tags:

CSV part 4, fast !!

June 28th, 2011 14 comments

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

Tags:

On using Toad against a database

June 7th, 2011 4 comments

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.

Tags:

What is the current setting of NLS_LANG in sqlplus?

May 10th, 2011 14 comments

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

Tags:

Send sqlplus reports in Excel format

April 20th, 2011 1 comment

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 !

Tags:

Send html report per email from sqlplus

April 15th, 2011 3 comments

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…

Tags:

List events in session, process or system

March 7th, 2011 2 comments

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.

Tags:

What’s your favorite shell in Windows?

October 19th, 2010 9 comments

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

Tags: ,

How to check if I have a pending transaction?

October 6th, 2010 3 comments

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

Tags:

Do you know the ORA- nonerrors?

August 17th, 2010 3 comments

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

Tags:

Difference between rollbac and rollback

August 24th, 2008 2 comments

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.

Tags:

return size of to_char

August 9th, 2008 4 comments

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

Tags:

SET LONGCHUNKSIZE

July 16th, 2008 1 comment

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

Tags:

take care of minus !

March 3rd, 2008 2 comments

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

Tags:

Display a blob

September 28th, 2007 6 comments

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

Tags:

errorlogging in 11g

August 27th, 2007 8 comments

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!

Tags:

Oracle Certified SQL Expert

July 10th, 2007 28 comments

Oracle Certification Program: Exam Details

I immediately registered for this exam and will do it next week, I just cannot wait for this one…

Tags:

read user-input in plsql

June 26th, 2007 3 comments

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

Tags:

sql*plus pagesize explained

June 4th, 2007 1 comment

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

Export to Excel

May 11th, 2007 50 comments

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

Tags:

su in sqlplus

December 19th, 2006 1 comment

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

Tags:

clear screen reports cleared columns, breaks and computes

January 11th, 2006 No comments 


$ echo clear screen|sqlplus scott/tiger
SQL&gt; columns cleared
breaks cleared
computes cleared

but if I quit properly, it does not report that


$ echo "clear screen
quit"|sqlplus scott/tiger
SQL&gt;

a good reason to improve the quality of your shell script by quitting at the end ;-)

Tags:

Do you shu or do you spo?

December 29th, 2005 7 comments

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type “by”.

Does it sound strange and meaningless to abbreviate “bye” in “by” ? Well, I have a few favorites

SQL> set lin 999
SQL> rollb
SQL> spo f
SQL> shu
SQL> spo off

Tags:

the sqlplus settings I like

December 5th, 2005 10 comments

It is monday, I am going to give a list of settings I like in sqlplus

set lin 32767 trimsp on tab off
extends the linesize and avoid line breaks. I use it before SPOoling. But it is annoying before DESCribing. Trimspool is necessary to avoid spaces at the end of the line. Set tab off makes sure sqlplus does not use “tab” for formatting, but spaces.

set emb on pages 0 newp none
this avoid page breaks. there is one header in the top, than no more, and no ^L. newp none is not working in version 7, there you must use newp 0.

set head on
set head off
show or hide column headers

set feedb 6
set feedb off
report result of query, set feedb 6 do not give feedback if a select returned 1 to 5 lines, because it is too easy to count… Set feedback off removes feedback

set ver off
I am never interrested in the translation of my defined variables

set termout on
set termout off
Off avoids screen output. Warning, this does not avoid spool output. Works only in scripts, not in command mode. Note that a command piped thru sqlplus is still a command more.

set echo on
set echo off
Display executed command. Works only in scripts, not in command mode.

sqlplus / <<EOF
set echo on
set termout off
select * from dual;
EOF
the echo on and termout off will have no effect, because it is not a sql script (called with @).

def _editor=vi
set editf /tmp/lscfile.sql
Use vi (instead of ed) as editor, and use a file in /tmp (instead of afiedt.buf in working directory) as temp file

set long 1000000000 longc 60000
do not truncate longs nor long chunks. Very usefull with clob in sqlplus.

set serverout on size 1000000
set serverout on size unlimited
allows dbms_output to print to current terminal. Unlimited is a 10gR2 enhancement

set sqlp “_USER @ _CONNECT_IDENTIFIER> ”
change the prompt to contain a dynamic user and connection string.

thursday I am having dinner with tom kyte, drop me a comment there if you want to come

Tags:

select column only if it exists

August 31st, 2005 No comments

i need to display tablespace attributes

SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

looks fine. but what if I try that on my oracle7 database? I will get ORA-00904: invalid column name

ok, I will then select only the columns that exist! than select from dba_tablespaces

t.sql:
set termout off
def logging=”"
def extent_management=”"
def allocation_type=”"
def segment_space_management=”"
def retention=”"
def bigfile=”"
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ‘,logging’ logging from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’LOGGING’;
select ‘,extent_management’ extent_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’EXTENT_MANAGEMENT’;
select ‘,allocation_type’ allocation_type from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’ALLOCATION_TYPE’;
select ‘,segment_space_management’ segment_space_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’SEGMENT_SPACE_MANAGEMENT’;
select ‘,retention’ retention from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’RETENTION’;
select ‘,bigfile’ bigfile from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’BIGFILE’;
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;

let’s try

SYS@LSC69 AS SYSDBA/7.3.4.5
SQL> @t
TABLESPACE_NAME STATUS CONTENTS
——————– ——— ———
SYSTEM ONLINE PERMANENT

SYS@LSC65 AS SYSDBA/8.1.7.4
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
——————– ——— ——— ——— ———- ———
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER

SYS@LSC67 AS SYSDBA/9.2.0.6
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN
——————– ——— ——— ——— ———- ——— ——
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER MANUAL

SYS@LSC63 AS SYSDBA/10.2.0.1
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

one script for any version

Tags:

return code

August 23rd, 2005 8 comments

there is a myth of using sql.sqlcode in sqlplus

whenever sqlerror exit sql.sqlcode

this not ok. you should prefer whenever sqlerror exit failure or exit 1

Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88.

Let’s try it

$ sqlplus “/ as sysdba”
SQL> create user gaston identified by lagaffe quota 1k on users default tablespace users;

User created.

SQL> grant create table to gaston;

Grant succeeded.

SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’

Disconnected from …
$ echo $?
0

1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!

well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable

SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.

SQL> def _rc
DEFINE _RC = “2″ (CHAR)

many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.

SQL> create or replace procedure p(o out number) is begin o:=1; end;
2 /

Procedure created.

SQL> var rc number
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> exit :rc
Disconnected …
$ echo $?
1

Tags:

set pages 50000

June 16th, 2005 6 comments

I have often see set pagesize 50000 or set pages 9999, just to avoid page break.

I wonder how many line break will appears when the select grow over 50000…

A cool solution is to use embedded with pagesize 0

SQL> set emb on pages 0 newp 0

Tags:

login.sql

June 10th, 2005 No comments

Hi,
I am not recommending the use of glogin.sql and login.sql to do sql computation. It is degrading the performance and the maintenability of the scripts run over the database.

Myself I am using a big login.sql, which is located in the “SQLPATH” variable. I tested it with 7.3, 8.1.7, 9.2 and 10.0 on AIX. Be aware, it has side effects!

Ok, here it is

host if ! [ -t 0 ] || ! ( unset PERLLIB PERL5LIB; /bin/perl -e ‘use POSIX qw/getpgrp tcgetpgrp/;open(TTY,”/dev/tty”) or exit 1;exit(tcgetpgrp(TTY)!=getpgrp());’ ); then :;else echo @login_ux_fg;fi >/tmp/loginsql.sql
host chmod 2>/dev/null 666 /tmp/loginsql.sql
@/tmp/loginsql

Surprised? Does this looks like sql?
Not really. I am actually checking that sqlplus is not piped (-t 0), and run in terminal (dev/tty), in foreground (getpgrp). Only then I am executing login_ux_fg.sql

Well here is my login_ux_fg.sql, with colors, terminal header and more…

– login_ux_fg.sql
def _editor=vi
set editfile /tmp/tmporacle.sql
set ver off pages 40000 lin 80 long 1000000000 longc 60000 trims on con .
col file_name format a60
col member format a41
col tablespace_name format a20
col db_link format a20
col host format a20

– I am not interrested in that when select * from system views
col ini_trans nopri
col max_trans nopri
col INITIAL_EXTENT nopri
col NEXT_EXTENT nopri
col MAX_EXTENTS nopri
col MIN_EXTENTS nopri
col PCT_INCREASE nopri
col PCT_THRESHOLD nopri
col INCLUDE_COLUMN nopri
col FREELISTS nopri
col FREELIST_GROUPS nopri
col PCT_FREE nopri

– sql error can occurs, if db is down or when no privilege. The show must go on
set termout off echo off arrays 7
whenever sqlerror continue
whenever oserror continue

– save the current line of the query and the query (10g)
host echo > /tmp/lastquery.sql; chmod 2>/dev/null 666 /tmp/lastquery.sql
spool /tmp/lastquerycurrentline.txt
list *
spool off
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.txt
save /tmp/lastquery.sql rep
host chmod 2>/dev/null 666 /tmp/lastquery.sql

col “_myprompt” new_value myprompt
col “_mytitle” new_value mytitle
col “_user” new_value _USER
col “_privilege” new_value _PRIVILEGE
col “_connect_identifier” new_value _CONNECT_IDENTIFIER
col “_o_release” new_value _O_RELEASE

– default to null
select ‘x’ “_user”, ‘x’ “_privilege”, ‘x’ “_connect_identifier”, ‘x’ “_o_release” from dual where 1=2;

select nvl(‘&_user’, user) “_user”, nvl(‘&_privilege’, decode(user,’SYS’,'AS SYSDBA’,'PUBLIC’,'AS SYSOPER’)) “_privilege”, nvl(‘&_o_release’,’0′) “_o_release” from dual;
select nvl(‘&_connect_identifier’,name) “_connect_identifier” from v$database;
select nvl(‘&_connect_identifier’,substr(global_name, 1, instr(global_name||’.',’.'))) “_connect_identifier” from global_name;

– check if we support colors / linesize
host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo def _SYSDBA=\”`tput colf1`\”;echo def _NORMAL=\”`tput colf4`\”;echo def _SYSOPER=\”`tput colf5`\”;echo def _RESET=\”`tput sgr0`\”;echo set lin `tput cols`;else echo def _SYSDBA=\”\”;echo def _NORMAL=\”\”;echo def _SYSOPER=\”\”;echo def _RESET=\”\”;echo set lin 80; fi > /tmp/color.sql; chmod 2>/dev/null 666 /tmp/color.sql
@/tmp/color.sql

.

select
decode(‘&_PRIVILEGE’, ‘AS SYSDBA’, ‘&_SYSDBA’, ‘AS SYSOPER’, ‘&_SYSOPER’, ‘&_NORMAL’)||
substr(‘&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
decode(‘&_PRIVILEGE’, null, null, ‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
),
1, 33)||
‘&_RESET’||
chr(10)||’SQL> ‘ “_myprompt”
from dual;

select
chr(27)||
‘]2;&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
rtrim(‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
)||
chr(7) “_mytitle”
from dual;

create
.

del 1 last
get /tmp/lastquery
.

host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo;echo ‘&mytitle’;echo; fi
host /usr/bin/sed 1>/tmp/lastquerycurrentline.sql 2>/dev/null -n ‘s/*.*//p’ /tmp/lastquerycurrentline.txt
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.sql
@/tmp/lastquerycurrentline

col “_myprompt” clear
col “_mytitle” clear
col “_user” clear
col “_privilege” clear
col “_connect_identifier” clear

set sqlp “&myprompt”
undef myprompt mytitle _SYSDBA _SYSOPER _NORMAL _RESET

undef _RC
set arraysize 15
set termout on

Only in sqlplus 10g, this script is run at each connection. It is basically good, because the prompt will be recalculated. What less good is, is that I will have to set whenever error to continue. I found no way to reset it to its original value after connect. Same for termout

so if I have a script

whenever sqlerror exit
connect /
create table …

It will not work as expected. You can search on asktom.oracle.com or on forums.oracle.com, you will find no solution to that problem, but many posts of mines…

Tags:

shell + sqlplus

June 6th, 2005 8 comments

How to send commands to sqlplus ?
Use stdin

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

How do you retrieve data from your database with the shell and sqlplus ?

USERS=$(echo “select ‘@’,username from dba_users;” | # this is my stdin
sqlplus -s “/ as sysdba” | # sqlplus as a filter
awk ‘/^ORA-/ /^SP2-/ { print|”cat >&2″;exit 1 }
/^@/ { print $2 }’ # search for @
)
if [ $? != 0 ] # awk returned error
then
echo error >&2
# exit 1
fi
for u in $USERS # do something
do
echo hello $u
done

hello SYSTEM
hello SYS
hello PERFSTAT
hello SCOTT
hello DIP
hello U01
hello EXFSYS
hello MON
hello DBSNMP
hello OUTLN

How do I get OS output from sqlplus ?
The high end solution is to use Java. However, this implies you have Java in the Database (cost memory), you have the necessary privileges (which can be easily misused to destroy your system), and you use plsql. Have a look at my blog about Disk Free space.
Ok, let’s imagine I do not want to install all that stuff.

SQL> host uptime
17:09pm up 84 days, 23:32, 14 users, load average: 1.13, 1.23, 1.28

How do I get the return code?
SQL> prompt return code is &_RC
return code is 0

But this does not work in 10g :-(

How do I use the os user, hostname, system date, ip address?
Use SQL!

SQL> select sys_context(‘USERENV’,'OS_USER’), host_name, sysdate, UTL_INADDR.GET_HOST_ADDRESS(host_name) from v$instance;

oracle dbsrv85a 06.06.2005 17:14:46 147.50.59.167

Ok, I want to know the OS and store it in a variable!
Hmm, hmm… I like to do those kind of things by sourcing temp files
SQL> def os=”"
SQL> host echo def os=”$(uname -s)” > /tmp/tmpos.sql
SQL> start /tmp/tmpos.sql
SQL> select ‘&os’ from dual;
AIX

Tags: