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

column width change in 12c

In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME                     COLUMN_NAME —————————— —————————— BONUS                          COMM BONUS                          SAL BONUS                          JOB BONUS                          ENAME DEPT                           LOC DEPT                           DNAME DEPT                           DEPTNO EMP                            DEPTNO EMP                            COMM EMP                            SAL EMP                            HIREDATE TABLE_NAME                     COLUMN_NAME —————————— —————————— EMP                            MGR EMP                            JOB EMP                            ENAME EMP                            EMPNO SALGRADE                       HISAL SALGRADE                       LOSAL SALGRADE                       GRADE 18 rows selected. Which was […]

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

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

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

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

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

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

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

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

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

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