super-long-lines in CLOB

Sometimes you use sqlplus and your line is longer than your linesize SQL> select n||’;’||x from t2; 1;one 2;twoPxMQztzLaqjWjGKOXIVIVrrHC fJkTLbRgCPiENfWrrCjUMRSkmCfLUY RdVASFacGtyEnUplOAXspDJZmSLPfg ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy lZLQROFxIxOzqeQeFMGXcGkuJwnGGg zvNSuCFMVxyQgboLAUpDcYnBsuVyXX vFtWPICwqmcEdmzRRkOzzPethrqjRR aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh ZcxJBvSCVXwONmS prVzTykEfSsePyYwyLVoyYrVLynUzs MLFWQxwUKNsVcYzUOAhslNldnBpITS rxPlpJbLSjJqgxNxsGVsrYhkWAMufk QnRayieEkSDYrNqyLejJuggADNxcgV tszjJIYKCxPweNGhXsOFKGbMkTBPCf DXwjBNgQYswbaNWBOEtSTHjIhdLAyM nbhyhRKKdfaTTpTgHqQelVWmnkBHjA ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR SjdVlRwMas 3;three SQL> There are two extra line breaks, one after ONmS and one after wMas An easy solution is […]

dynamic linesize in 18.1

Whenever you select and describe in sqlplus it looks ugly default: pagesize 14 linesize 80 change the default: it is often too large or too narrow Let’s try WINDOW in sqlplus 18.1, which is available for download on Solaris / Linux / Windows SQL> set lin window SQL> sho lin linesize 95 WINDOW SQL> sho […]

execute sql script from pl/sql

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

column width change in 12c

In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME COLUMN_NAME —————————— —————————— BONUS COMM BONUS SAL BONUS JOB BONUS ENAME DEPT LOC DEPT DNAME DEPT DEPTNO EMP DEPTNO EMP COMM EMP SAL EMP HIREDATE TABLE_NAME COLUMN_NAME —————————— —————————— EMP MGR EMP JOB EMP ENAME EMP […]

Drop table if exists

The syntax that you are looking for is docs.oracle.com/…/drop-table.html DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] … [RESTRICT | CASCADE] Wait, this does not work ! SQL> drop table if exists t; drop table if exists t * ERROR at line 1: ORA-00933: SQL command not properly ended Okay. It was the Oracle MySQL […]

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba. Test case (21 is a not something to do in real life): SQL> alter system set processes=21 scope=spfile; System altered. SQL> startup force quiet ORACLE instance started. […]

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo. By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user. SQL> ALTER USER app_user PROXY ONLY CONNECT; SQL> CONNECT app_user/xyz ERROR:ORA-28058: login […]

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, […]

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 = […]

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

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 […]

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 […]

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 […]

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); […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

What’s your favorite shell in Windows?

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC. PS> $stmt = “set hea off`n” PS> $stmt += “select ‘hello world’ from dual;” PS> $res = ($stmt | sqlplus -s scott/tiger) PS> […]

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 […]

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 ———- […]

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 […]

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 […]

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 […]

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 […]