Home » SQL & PL/SQL » Client Tools » SQL Plus Format (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
SQL Plus Format [message #684884] Tue, 21 September 2021 11:02 Go to next message
wtolentino
Messages: 349
Registered: March 2005
Senior Member
i have this line of code on a script:

set linesize 90;

select substr(user,1,15) user_name, 
       substr(sys_context('userenv','db_name'),1,15) database_name
  from dual;

it works fine only the formatting is somewhat not right. when run on this one database format is not correct this was the result:
USER_NAME
------------------------------------------------------------
DATABASE_NAME
------------------------------------------------------------
TUSERSRV
DBUESC

when run on the other database the formatting was correct:
USER_NAME       DATABASE_NAME
--------------- ---------------
PUSERSRV        PBUESC                                                     

i am using SQL*Plus: Release 19.0.0.0.0 Version 19.3.0.0.0.

[Updated on: Tue, 21 September 2021 11:40]

Report message to a moderator

Re: SQL Plus Format [message #684885 is a reply to message #684884] Tue, 21 September 2021 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68050
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, behaviour depends on version, OS and NLS options (including character sets used!). Sad

The safe option is to define yourself the format of the columns in your script:
col USER_NAME format a15
col DATABASE_NAME format a15
Re: SQL Plus Format [message #684886 is a reply to message #684885] Tue, 21 September 2021 11:48 Go to previous messageGo to next message
wtolentino
Messages: 349
Registered: March 2005
Senior Member
thanks Michel that works.
Re: SQL Plus Format [message #684887 is a reply to message #684884] Tue, 21 September 2021 13:37 Go to previous messageGo to next message
EdStevens
Messages: 1351
Registered: September 2013
Senior Member
Just to explain that behavior a bit more ..

'linesize' specifies the total line length sqlplus will write before it wraps. If the next column to be written causes the line to exceed LINESIZE, then it will break at the column - it won't break in the middle of a column just to fill LINESIZE.

And in dealing with the individual columns. it will, as you saw, allocate space for the maximum of the column size. Thus it can easily fill LINESIZE even though none of the actual data is that long. It has to make those decisions about space allocation before it starts pulling the data, because at that point it has no way of knowing the max length of the actual data - vs, its defined column size.

Another thing that a lot of people miss is that the standard terminal width is 80 characters. This is essentially a hardware limit (or of the terminal software), so sqlplus can do nothing about it. If you set linesize 150, but your output is to a terminal (rather than a file) it will still wrap at 80 characters even though sqlplus has not yet inserted a carriage return.
Re: SQL Plus Format [message #684890 is a reply to message #684887] Wed, 22 September 2021 00:46 Go to previous message
Michel Cadot
Messages: 68050
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case, the difference is more likely in the Oracle environment parameters.
For instance, changing "cursor_sharing" parameter can change the way SQL*Plus sees and shows the result:
SQL> select substr(user,1,15) user_name,
  2         substr(sys_context('userenv','db_name'),1,15) database_name
  3    from dual;
USER_NAME       DATABASE_NAME
--------------- ---------------
MICHEL          MIKB2

1 row selected.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select /* force parsing */ substr(user,1,15) user_name,
  2         substr(sys_context('userenv','db_name'),1,15) database_name
  3    from dual;
USER_NAME
------------------------------
DATABASE_NAME
---------------------------------------------------------------------------------------------------
MICHEL
MIKB2

1 row selected.
Previous Topic: SQL Developer not displaying sql for views
Next Topic: PL/SQL Developer settings FAQ
Goto Forum:
  


Current Time: Mon Dec 06 17:28:53 CST 2021