Oracle SQLPlus – Extend Prompt Beyond 50 Characters

oraclesqlplus

I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error:

String beginning "truncated_prompt..." is too long. Maximum size is 50 characters.
SQL>

…and yes, my prompt is really truncated to 50 characters.

What to do? Is it possible to somehow make this limit higher? (I already tried SET LONG and SET LONGC without any change.)

(Reacting comment)
I have this long prompt, because I have some control sequences in it, which make it so beautiful. And I am calling it through rlwrapper, which makes the normal keys (page up/down, history search, tab completion, etc) also working, just as in a good command line application.

Best Answer

Finally, I found an answer!

The trick is, that the 50 char limit is for the string before the variable substitution! The size of the substituted string isn't limited. In my case, shorting _connect_identifier to a temporal variable _ci was enough:

DEFINE _ci=&_connect_identifier
SET SQLPROMPT "(long things)_user'@'_ci'(long things)"

Related Solutions

Retype information every time I make a mistake in SQLPlus

Several ways.

You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):

export EDITOR=vi

Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.

If you don't wish to set an environment variable, you can add define _editor=vi to your $ORACLE_HOME/sqlplus/admin/glogin.sql file instead.

My favourite way of achieving SQL*Plus edit heaven (in Unix) is by using rlwrap. This adds readline support to SQL*Plus and allows you to easily edit within SQL*Plus itself.

Install rlwrap from your distributions package repository (or download & compile from the website here), then add the following to your shells startup script (.bashrc/.bash_profile or similar):

alias sqlplus="rlwrap -i sqlplus"

How to limit select output from sqlplus

I don't think you can. The only thing I know that sort of does that (but is slightly annoying IMO) is the PAUSE option which waits for you to press enter between pages. The annoying part is that it pauses before the first row... You can Ctrl C to interrupt when you've seen enough.

SQL> set pause on
SQL> set pages 10 pause 'press enter to continue'
SQL> select object_name from dba_objects;
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
I_IND1
I_CDEF2
I_OBJ5
I_PROXY_ROLE_DATA$_1
FILE$
UET$
I_FILE#_BLOCK#
press enter to continue

I_FILE1
ERROR:
ORA-01013: user requested cancel of current operation

15 rows selected.

Best Answer

Related Solutions

Retype information every time I make a mistake in SQLPlus

How to limit select output from sqlplus

Related Question