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 🙂
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.
Then run the repeat command.
9 Comments
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
>>But when I try to use REPEAT inside a SQL script
No. It was designed to be used interactively.
hi !
the repeat function seems not to work, if the select uses variables.
seems to be a bug
regards
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”
Are you 12c? Fetch first rows is a 12c new feature.
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
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!
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…
We’re working on getting an escape sequence working now actually.