OraFAQ Forum: Client Tools » SQL Plus Format

Home » SQL & PL/SQL » Client Tools » SQL Plus Format (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Plus Format [message #684884]

Tue, 21 September 2021 11:02

wtolentino
Messages: 373
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

Michel Cadot
Messages: 68453
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

Report message to a moderator

Re: SQL Plus Format [message #684886 is a reply to message #684885]

Tue, 21 September 2021 11:48

wtolentino
Messages: 373
Registered: March 2005

Senior Member

thanks Michel that works.

Report message to a moderator

Re: SQL Plus Format [message #684887 is a reply to message #684884]

Tue, 21 September 2021 13:37

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: SQL Plus Format [message #684890 is a reply to message #684887]

Wed, 22 September 2021 00:46

Michel Cadot
Messages: 68453
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.

Report message to a moderator

Previous Topic:	SQL Developer not displaying sql for views
Next Topic:	PL/SQL developer Command Prompt accepts input from user only one time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 02 02:58:18 CDT 2023