Forum Stats

  • 3,836,775 Users
  • 2,262,185 Discussions
  • 7,900,105 Comments

Discussions

Categories

SQLcl 4.2 - Issues with sql*plus set options and column formating

Gunther Pippèrr
Gunther Pippèrr Member Posts: 35 Blue Ribbon
edited May 18, 2016 9:34AM in SQLcl

Hello Team,

a very good idea to create a new sql command interpreter.

I use  the version sqlcl-4.2.0.16.112.0616-no-jre.zip (fresh download from today).

Fist I  have under Linux and Windows in the beginning some trouble with the call scripts, the usage of JAVA_HOME is not really completely implemented. 

I have to fix all java calls to a full qualified path with variables, on hosted System you often have no installed java and no java in the path!

Also I test my daily scripts, written over the last year and I get a lot of errors like this:

SQL> column status    format A8  heading "Status"

SQL> column name      format A8  heading "Instance|Name"

SQL> column startzeit format A15 heading "Start|Time"

SQL> column host_name format A35 heading "Server|Name"

SQL> select  status  , instance_name as name   , to_char(STARTUP_TIME, 'dd.mm.YY hh24:mi') as startzeit , host_name   from v$instance order by 1;

Mai 02, 2016 2:24:00 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run

SCHWERWIEGEND: java.util.Formatter$FormatSpecifier.checkGeneral(Formatter.java:2954)

java.util.MissingFormatWidthException: %-01$s

        at java.util.Formatter$FormatSpecifier.checkGeneral(Formatter.java:2954)

        at java.util.Formatter$FormatSpecifier.<init>(Formatter.java:2725)

        at java.util.Formatter.parse(Formatter.java:2560)

        at java.util.Formatter.format(Formatter.java:2501)

        at java.util.Formatter.format(Formatter.java:2455)

        at java.lang.String.format(String.java:2928)

And are all set Options implemented?

I get errors with this Options:

SQL> set recsep off

SP2-0158: Unbekannte SET-Option "recsep"

SQL> SET UNDERLINE '='

SP2-0158: Unbekannte SET-Option "underline"

SQL> column mb_total format 999G999G999D00 heading "MegaByte |Total used on disk"

SP2-0246: Ung├╝ltige FORMAT-Zeichenfolge "999G999G999D00"

SQL> column host_name format A18 heading "Inst Server|Name"

SQL> column edition   like host_name  heading "DB|Version"

SP2-0158: Unbekannte COLUMN-Option "like"

All my scripts as test examples you can find here https://orapowershell.codeplex.com/SourceControl/latest#sql/database.sql

Also you should try this script, it crashes the SQLcl completely .-) => https://orapowershell.codeplex.com/SourceControl/latest#sql/login.sql

Thanks

Best Regards

Gunther



· Share on TwitterShare on Facebook

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,682 Employee
    edited May 3, 2016 10:49AM

    I can reproduce your error on your script on the start up time of your database, thanks for the example.

    No, we have not supported every SET command. You can run 'HELP SET' to see which ones we are support, and also which ones we've added.

    · Share on TwitterShare on Facebook
  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 3, 2016 12:17PM

    Hello Jeff,

    do you plan to implement  all the other set operators and formatting possibilities?

    The problem is, that in older scripts or in script that should be used in SQLcl AND SQL*Plus , no errors should occur.

    If the set operator has no effect, may be nobody need any more to define stuff like the record separator "recsep" , but the script should work.


    May be a “ show deprecated Message” setting/switch will be helpful.

    If true a message show with part of the script will be ignored, if false nothing will be written to the console.

    For example if you use “set” to set a options and one option is not implemented , you get an error that the first option is not correct:


    SQL> set linesize 130 pagesize 300 recsep off
    SP2-0158: unknown SET option "linesize"


    But only the “recsep off” Part is invalid.


    For my opinion it is very important for  the acceptance in the DBA community to have a real 100% SQL*Plus feeling + the new cool features that will help us a lot in the daily work.


    I like the concept of the tool, real scripting and SQL*Plus together will help a lot with deployment scripts.


    Thanks


    Best Regards
    Gunther

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,682 Employee
    edited May 3, 2016 12:59PM

    ALL of them, no. Most of what you see in the tool now is what we plan on shipping with.

    That might mean some of your SQL*Plus scripts would need to be re-written if you wanted to use SQLcl. But, you might want to re-write them ANYWAY to take advantage of things like /*csv*/ or newer commands like DDL/CTAS.

    We've gone over ALL of the SQL*Plus SET items and have decided which ones make sense to carry over or not. That doesn't mean we can't re-evaluate any of those.

    What formatting possibilities are you looking for?

    · Share on TwitterShare on Facebook
  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 4, 2016 5:11AM

    Hello Jeff,

    I check all my scripts and I grep out all set statements I use and I only missing  relay underline and markup.

    Result of my checks over my settings in SQL*Plus scripts:

    -------------------------------

    -- working .-)

    set verify on

    set verify off

    set trimspool on

    set trimspool off

    set trimout on

    set trim on

    set timing on

    set timing off

    set time on

    set time off

    set termout on

    set termout off

    set serveroutput on size 1000000

    set serveroutput on

    set serveroutput off

    set pagesize 1000

    set pages 100

    set pages 0

    set numwidth  14

    set numwidth  12

    set longchunksize 1000000

    set long 650000

    set long 64000;

    set linesize 300

    set lines 1000

    set heading on

    set heading off

    set head on

    set feedback on

    set feedback off

    set echo on

    set echo off

    set concat off

    set verify on

    set verify off

    ---------

    ttitle left  "char set of the database" skip 2

    ttitle off

    -----------------------------

    Not Working

    -------------------------------

    -- not working but for my opinion still useful

    -- but may be a better solution with new SQLcl possible??

    -- I create html reports with this feature to send to customer

    set markup html on spool on preformat off entmap on -

    set markup html on

    set markup html off

    -------------------------------

    -- Will be nice to have this

    set underline '='

    set underline '-'

    -------------------------------

    -- not working but obsolete

    --

    set recsep off

    set recsep wrap

    set linesize 500 pagesize 9000 recsep off

    But it will be better to ignore this settings like the trim as throwing an error

    SQL> set trim on  line 1: "set trim on" is Obsolete.

    -------------------------------

    Is there a replacement for the  "set markup html" ?


    Do you see a chance to get "set underline" implemented?


    Thanks

    Gunther

    · Share on TwitterShare on Facebook
  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 4, 2016 5:40AM

    Hello Jeff,

    I also test all my different column format definitions and I miss the G in the number formatting of a column for the NLS_LANG depended thousand separators and the like format shortcut.

    I test a lot of combination, most are working, but this is the outcome of the test:

    --- Working

    column col format 9999999

    column col format 9999999D99

    -- Not working

    SQL>  column col format 9G999G999D99

    SP2-0246: Illegal FORMAT string "9G999G999D99"

    -- Missing

    SQL>column col2 like col

    --- not so important

    SQL> column  access_parameters format a80  heading "access|parameter" fold_before word_wrapped

    Column commands FOLD_BEFORE and FOLD_AFTER are not supported.


    I think the G is important to have a language depended separator , the like will be helpful but more a nice to have.

    What do you think?

    Thanks


    Best Regards

    Gunther

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,682 Employee
    edited May 4, 2016 10:30AM

    Not really a replacement for SET MARKUP html, but there's

    select /*html*/ * from table...the output comes back formatted as HTML code.

    I'll see what's up with the UNDERLINE support.

    -- Not working

    SQL>  column col format 9G999G999D99

    SP2-0246: Illegal FORMAT string "9G999G999D99"

    I'll investigate this as well, could be a bug.

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,682 Employee
    edited May 4, 2016 11:02AM

    yeah the column format issue is a bug

    · Share on TwitterShare on Facebook
  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 17, 2016 9:57AM

    Hello,

    I test the version 4.2.0.16.131.1023  from May 13, 2016 and  column formatting with the G like  “col format 9G999G999D99” is now working!

    Very nice, -) I like it!

    Best Regards

    Gunther

    · Share on TwitterShare on Facebook
  • Gunther Pippèrr
    Gunther Pippèrr Member Posts: 35 Blue Ribbon
    edited May 18, 2016 9:34AM

    Hello Jeff,

    do you see the possibility to map the old set markup command from sql*plus 1 to 1 to the new set set sqlformat as new feature?

    SQL*Plus             SQLcl

    ---------------      --------------------------            

    set markup html on   => set sqlformat html

    set markup html off  => set sqlformat default

    Only as synonym, alle internal features are from the new feature, all other settings set markup features will be ignored.

    At the end it is mostly the same.


    "set sqlformat html" is realy a cool feature for add hoc reporting, I like it!


    Much better then set markup!


    I have the problem that I have to work a lot in hosted environments, and a new java version is there not easy to get,

    Most of the scripts should still work in both environments.


    Best Regards

      Gunther

    · Share on TwitterShare on Facebook