developers

    Forum Stats

  • 3,873,726 Users
  • 2,266,634 Discussions
  • 7,911,622 Comments

Discussions

history feature requests

mUday-Oracle
mUday-Oracle Posts: 11 Employee
edited Mar 27, 2018 9:14AM in SQLcl

1) search history: Only feature making me not move here at this point. Long history or several long SQLs (which we have) will cause pain as no pagination is present for history command. I use rlwrap which supports searching.

2) execute directly: right now I've to list sql (h #) and then execute. Would be great to have command to execute directly, as simple as h#

3) edit history command and execute: I've to use arrows, edit, and execute. But if history is long, then, I've to:  1) history #n 2) execute 3) edit 4) execute ... currently, I cannot do: 1) history #n 2) edit command 3) execute

Thanks!

Comments

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,878 Employee
    edited Mar 27, 2018 9:14AM

    >>making you not move....from what?

    I too want a search history feature.

    @mUday-Oracle - do you work for Oracle? If so, then please send me an email, and we'll take this offline.

    User_AMSSX
  • User_AMSSX
    User_AMSSX Member Posts: 3 Red Ribbon

    Three and a half year later: It seems this nice feature to search the history is still missing.

    Can we vote for it ?

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    SQL> select 123 xyz from dual;
    
       XYZ
    ______
       123
    
    SQL> select sysdate xyz from dual;
    
              XYZ
    _____________
    2021-12-16
    
    SQL> script sehi xyz
    2145: select 123 xyz from dual;
    2146: select sysdate xyz from dual;
    2147: script sehi xyz
    

    script:

    var cs = ctx.getProperty("script.runner.jline");
    var hs = cs.getHistoryService();
    hs.moveToFirst();
    while(hs.next()) {
      s = hs.current();
      if(s.match(args[1]))
        ctx.write(hs.index()+": "+s+"\n");
    }
    
    User_AMSSX
  • User_AMSSX
    User_AMSSX Member Posts: 3 Red Ribbon

    Very nice! Thanks a lot !

    I tried to use an alias as a shortcut, but this leads to some undesired results when querying:

    alias sh = script sehi :1 ;


    sh xxxx (never used)

    258: alias sh=scriptsehi :1 ;

    259: alias sh=script sehi :1 ;

    SQL>

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    alias sh = script sehi :1 ;

    Bind variables are a database sql feature.

    SQLcl creates a variable 1, but binding does not occur and script gets the string value :1 as is, so sh xxxx searches for :1.

    The hybrid solution to use literal or bind.

    // Search history (case insensitive regex).
    // The index returned by this command is unstable,
    // may change after repeating the previous commands.
    // Usage.
    // :abc      : value of bind variable (null value is 'null' string).
    //             Use \: or [:] to escape leading :.
    //   x  y    : (more one spaces before argument) string ' '.
    //             sqlcl passes each spaces as an argument, only first argument is processed.
    // "a b c"   : string 'a b c' with spaces. This does not escape the binds (leading :).
    try {
      var cs = ctx.getProperty("script.runner.jline");
      var hs = cs.getHistoryService();
      var se = args[1];
      if (se.startsWith(":")) {
        se = se.substring(1).toUpperCase();
        var bi = ctx.getVarMap().get(se);
        if (bi) se = bi.getValue();
        else throw "Bind "+se+" not defined";
      }
      if (!se) throw "Args is empty";
      hs.moveToFirst();
      while(hs.next()) {
        var s = hs.current();
        if(s.match(new RegExp(se,"i"))) ctx.write(hs.index()+": "+s+"\n");
      }
      ctx.write("Searched for: "+se+".\n");
    } catch (err) {        // (SQLcl bug) throw without substring <eval>:n:m produce java error ArrayIndexOutOfBoundsException
      ctx.write(err+"\n"); // replace the exception with plain output.
    }
    


    User_AMSSX
  • User_AMSSX
    User_AMSSX Member Posts: 3 Red Ribbon

    Nice job! Thanks again. this version does work using alias and a literal


    I can't tell why, but ( today?) the index given back does not match the history (both scripts)

    My history has about 115 lines, but the script gives back a much higher line number and gives back one more after each call.

    Indeed the line number increases with every search.

    Could someone reproduce this behavior ?

    I'm using latest version: SQLcl: Release 21.4.0.0 Production Build: 21.4.0.348.1716


    >script sh files

    223: select * from dba_data_files; --> correct line is 36

    224: select * from dba_temp_files;


    >script sh files

    224: select * from dba_data_files; --> correct line ist 36

    225: select * from dba_temp_files;

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    Indeed the line number increases with every search.

    The history command filters and renumbers the displayed entries from beginning. Due to the filter by command state the history displays fewer entries than the actual limit (set history fails displays more entries, but still not all). When you add a new command to the end of history after reach set history limit, all commands displays a new numbers (-1). When you execute the command already presented in history, the command is deleted from the middle and is appended to the end.

    The script sehi.js shows an internal index which does not change after adding a new command to end (sliding window over limit). But repeating the command will reindex all entries in the history +1  even if the command is filtered by set history filter. This is undesirable behavior, as result there is no stable index and it cannot be used for recall by number.

developers