Re: col widths in sql in ora 9

malcom,

if you're using SQL*Plus to implement your ad-hoc query system, then you need to use SQL*Plus' formatting commands -- which operate exactly the same no matter what database version you connect to.

if you are generating sql statements (with column wrapped in the substr function), you should just as well be able to generate the SQL*Plus formatting commands

regarding resetting the session, the idea is that you have standard settings that you invoke -- you are generating the scripts, you are in control; it just takes a little thought and a little design

• mcs "Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:3fb40384_at_news.victoria.tc.ca...
  > mcstock (mcstockspamplug_at_spamdamenquery.com) wrote:
  > : as noted in the other post, you need to use the SQL*Plus COLUMN command
  to
  > : control SQL*Plus output
  >
  > Actually, I have realized that the substr still controls the width, it's
  > just that it isn't clear how the width is determined, though I suspect
  > something to do with the width of characters.
  >
  > substr(xx,1,30) produces a 90 character column (which I thought was the
  > underlying column width, but that was just a close coincidence).
  >
  > substr(xx,1,20) produces a 60 character column
  >
  > so it appears that the displayed width is three times the selected width,
  > so the question is - what is controlling this?
  >
  >
  > ----
  >
  > : this is best done in the script that contains the SQL statement
  >
  > which is not so useful for interactive queries
  >
  > : these setting remain in effect for the SQL*Plus (not database server)
  > : session -- so they don't change if you connect to another database
  >
  > Yes, but the difference in column widths from these queries do change
  > based on the server, even though the underlying tables and the
  > queries appear to be the same.
  >
  > : so best practice is issue explicit COLUMN commands at the beginning of a
  > : script, perhaps setting common ones via a START <scriptfile> command,
  then
  > : at the end of the script, reset the SQL*Plus environment -- preferably
  via
  > : another START <scriptfile> command
  >
  > Unfortunately, I haven't yet seen the sql plus command that "resets"
  > variables to a previous state.
  >
  > So, if you interactively type "COLUMN whatever" (or "SET whatever"), and
  > then run a sql batch file that also sets that same value then there is no
  > easy way for the sql batch file to restore the previous value.
  >
  >