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
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.
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
Oracle Database 12c Enterprise Edition Release 126.96.36.199.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: 188.8.131.52.355.0402
SQL> history clear
SQL> select sysdate from dual
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
set echo on trimspool on
select sysdate from dual
repeat 2 1 ;
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.
>>But when I try to use REPEAT inside a SQL script
No. It was designed to be used interactively.
the repeat function seems not to work, if the select uses variables.
seems to be a bug
I’m getting an error when executing this statement:
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.
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?
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.