history feature requests

mUday-Oracle

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!

thatJeffSmith-Oracle

>>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

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

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

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

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

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

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.