I was going to talk about our new IMPORT command in SQLcl. This will allow you to feed a delimited text file of data to our command line interface and have it automatically imported to a table.

But Kris Rice beat me to it. Woo-hoo, less work for me 🙂

So go read that now.

If you read far enough, you’ll see a really cool video on how to create a session monitor in SQLcl.

By the way, we updated it, and have a new EA build for you to play with. DOWNLOAD IT NOW

I’m going to show a simpler example – just a timer using SYSTIMESTAMP.

SET sqlformat ansiconsole
SELECT systimestamp FROM dual;
repeat 15 0.55

Run that.

And you’ll get this.

Pretty cool, huh?

Just run your query – that will put it into the buffer. As in, if I hit ‘/’ – it will run again.

You can also put a query into the buffer using the history command.

Saying ‘history 2’ would put the 2nd item in your SQL History into the buffer.

You're gonna love, love, love the history command in SQLcl
You’re gonna love, love, love the history command in SQLcl

Then run the repeat command.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

9 Comments

  1. Mikhail Velikikh Reply

    Hi Jeff,

    Is the REPEAT command supported within SQL scripts running non-interactively?
    It’s working like a charm when I paste each command by hand.
    But when I try to use REPEAT inside a SQL script, it simply does not work as expected.
    Here is what I got into a terminal (sorry for verbosity, but I want to provide full output just to make sure that you get thorough understanding of this problem):
    — CUT >
    localhost$ SQLPATH=”” sqlcl/bin/sql /@ora12 @sqlcl_repeat_nonworking

    SQLcl: Release 4.2.0 Production on Пт фев 17 12:06:03 2017

    Copyright (c) 1982, 2017, Oracle. All rights reserved.

    Last Successful login time: Пт Фев 17 2017 13:06:03 +07:00

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
    Real Application Testing and Unified Auditing options

    Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.355.0402
    SQL>
    SQL> history clear
    History Cleared
    SQL>
    SQL> select sysdate from dual
    2 /

    SYSDATE
    ———
    17-FEB-17

    SQL>
    SQL> repeat 2 1 ;

    — 10 EMPTY LINES

    Running 1 of 2 @ 0:6:4.350 with a delay of 1s

    — 40 EMPTY LINES

    Running 2 of 2 @ 0:6:5.351 with a delay of 1s

    — CUT
    spool sqlcl_repeat

    version

    set echo on trimspool on

    history clear

    select sysdate from dual
    /

    repeat 2 1 ;

    history

    spool off

    prompt CAT
    host cat sqlcl_repeat.lst

    — CUT <

    It seems that sqlcl repeated the "history clear" command and totally ignored subsequent commands.
    The spool file has zero length.

    Regards

    • thatjeffsmith

      >>But when I try to use REPEAT inside a SQL script
      No. It was designed to be used interactively.

  2. hi !
    the repeat function seems not to work, if the select uses variables.
    seems to be a bug
    regards

  3. Edgar Sinsuan Reply

    I’m getting an error when executing this statement:
    select fname,lname
    from employees
    order by entry_date desc
    fetch first 10 rows only;

    Error at Command Line : 4 Column : 1
    Error report –
    SQL Error: ORA-00933: SQL command not properly ended
    00933. 00000 – “SQL command not properly ended”

  4. Hi Jeff,

    Thanks, I have it installed and I will try this now. With SQL*Plus, I have a nice login.sql script that sets all my favorite SQL*Plus system variables.

    Do you have a nice login.sql script for SQLcl already that you could share with us?

    Thanks,

    Patrick

    • thatjeffsmith

      my login.sql is very simple – it just sets my prompt at the moment. but just add yours to your sqlcl/bin directory and you should be good to go!

  5. Przemek Zawadzki Reply

    Nice. But it would be even nicer to write something like:

    repeat inf 1

    and have infinite loop of execution until any key is pressed…

Write A Comment