sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

This error usually while you do something wrong. Wait, what’s an error when you do everything right? Okay, here it is: You install the instantclient 32 rpm oracle-instantclient12.1-sqlplus- On that server, you switch home using oraenv $ . oraenv ORACLE_SID = [oracle] ? DB01 The Oracle base has been set to /u01/app/oracle You start sqlplus… Continue reading sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

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… Continue reading super-long-lines in CLOB

Categorized as sqlplus Tagged

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… Continue reading dynamic linesize in 18.1

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… Continue reading column width change in 12c

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… Continue reading Drop table if exists

Categorized as sql, sqlplus

bypass ora-20

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

Categorized as dba, sqlplus

switch user in Oracle

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

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

return code and sqlplus

Calling a shell script from within sqlplus is buggy… I have reported bug 3798918 in (back in 2004) and bug 13349119 in 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 =… Continue reading return code and sqlplus

Categorized as sqlplus, unix

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… Continue reading What does # mean in sqlplus?

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… Continue reading EURO symbol, sqlplus, cmd.exe and various issues

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);… Continue reading On implicit 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… Continue reading sqlplus -prelim

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… Continue reading CSV part 4, fast !!

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… Continue reading On using Toad against a database

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… Continue reading What is the current setting of NLS_LANG in sqlplus?

Send sqlplus reports in Excel format

I posted earlier about sending html mail. Here an example on how to send -fake- excel per mail echo “select * from emp;”| sqlplus -M “HTML ON” -s scott/tiger| uuencode emp.xls| mail laurentschneider@example.com May this be of some help to my readers !

Categorized as sqlplus Tagged

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… Continue reading List events in session, process or system

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>… Continue reading What’s your favorite shell in Windows?

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… Continue reading How to check if I have a pending transaction?

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… Continue reading return size of to_char


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… Continue reading SET LONGCHUNKSIZE

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… Continue reading take care of minus !