I love SQLcl but one must admit that it’s not widely available yet at customers’. So getting the hang of good ol’ SQL*Plus is still a must-have skill for Oracle DBAs. Here’s an interesting challenge, and an easy solution.

 

I’m selecting the lines that were input in the last 24 hours :

select * from nit.spri where moment > sysdate-1/24 order by moment ;

MOMENT NUM NOTES

----------- ---------- --------------------------------------------------

24-07 13:50 100 insertion auto via KSH

24-07 13:50 101 insertion auto via KSH

24-07 13:50 102 insertion auto via KSH

24-07 13:50 103 insertion auto via KSH

24-07 13:50 104 insertion auto via KSH

24-07 13:50 105 insertion auto via KSH

24-07 13:51 106 insertion auto via KSH

24-07 13:51 107 insertion auto via KSH

24-07 13:51 108 insertion auto via KSH

24-07 13:51 100 insertion auto via KSH

24-07 14:00 200 insertion auto via KSH

24-07 14:00 201 insertion auto via KSH

24-07 14:00 202 insertion auto via KSH

24-07 14:00 203 insertion auto via KSH

24-07 14:00 204 insertion auto via KSH

24-07 14:00 205 insertion auto via KSH

24-07 14:00 206 insertion auto via KSH

24-07 14:06 200 insertion auto via KSH

24-07 14:06 201 insertion auto via KSH

24-07 14:06 202 insertion auto via KSH

24-07 14:06 203 insertion auto via KSH

24-07 14:06 204 insertion auto via KSH

24-07 14:06 205 insertion auto via KSH

24-07 14:06 206 insertion auto via KSH

24-07 14:06 207 insertion auto via KSH

24-07 14:07 208 insertion auto via KSH

24-07 14:07 209 insertion auto via KSH

24-07 14:10 400 insertion auto via KSH

24-07 14:10 401 insertion auto via KSH

24-07 14:10 402 insertion auto via KSH

24-07 14:10 403 insertion auto via KSH

24-07 14:10 404 insertion auto via KSH

24-07 14:10 405 insertion auto via KSH

24-07 14:10 406 insertion auto via KSH

24-07 14:10 407 insertion auto via KSH

24-07 14:10 408 insertion auto via KSH

24-07 14:13 400 insertion auto via KSH

24-07 14:13 401 insertion auto via KSH

24-07 14:13 402 insertion auto via KSH

24-07 14:13 403 insertion auto via KSH

24-07 14:13 404 insertion auto via KSH

24-07 14:13 405 insertion auto via KSH

24-07 14:13 406 insertion auto via KSH

24-07 14:14 407 insertion auto via KSH

24-07 14:14 408 insertion auto via KSH

24-07 14:14 409 insertion auto via KSH

24-07 14:15 500 insertion auto via KSH

24-07 14:15 501 insertion auto via KSH

24-07 14:15 502 insertion auto via KSH

24-07 14:15 503 insertion auto via KSH

24-07 14:15 504 insertion auto via KSH

24-07 14:15 505 insertion auto via KSH

24-07 14:15 506 insertion auto via KSH

24-07 14:15 507 insertion auto via KSH

24-07 14:17 600 insertion auto via KSH

24-07 14:22 601 insertion auto via KSH


 

56 rows selected.


56 rows is too many for my need, so I decide to select the lines that were input in the last 20 minutes. This means that I must change

where moment > sysdate-1/24

to

where moment > sysdate-20/1440


As you probably know, in SQL*Plus, a character string can be changed into another with the change keyword (c for short):

 

select log_mode,open_mode,status from v$database

*

ERROR at line 1:

ORA-00904: "STATUS": invalid identifier

 

SQL> c/status/db_unique_name

1* select log_mode,open_mode,db_unique_name from v$database

SQL> /

LOG_MODE OPEN_MODE DB_UNIQUE_NAME

------------ -------------------- ------------------------------

NOARCHIVELOG READ WRITE orcl


 

As you can see in the example above, the typical field separator is a slash. So how am I going to turn  sysdate-1/24 into sysdate-20/1440 ?

Fortunately, SQL*Plus allows for any field separator you like. Let’s choose an exclamation point:

 

>-c!1/24!20/1440

  1* select * from nit.spri where moment > sysdate-20/1440 order by moment

  >-/

MOMENT NUM NOTES

----------- ---------- --------------------------------------------------

24-07 14:06 200 insertion auto via KSH

24-07 14:06 201 insertion auto via KSH

24-07 14:06 202 insertion auto via KSH

24-07 14:06 203 insertion auto via KSH

24-07 14:06 204 insertion auto via KSH

24-07 14:06 205 insertion auto via KSH

24-07 14:06 206 insertion auto via KSH

24-07 14:06 207 insertion auto via KSH

24-07 14:07 208 insertion auto via KSH

24-07 14:07 209 insertion auto via KSH

24-07 14:10 400 insertion auto via KSH

24-07 14:10 401 insertion auto via KSH

24-07 14:10 402 insertion auto via KSH

24-07 14:10 403 insertion auto via KSH

24-07 14:10 404 insertion auto via KSH

24-07 14:10 405 insertion auto via KSH

24-07 14:10 406 insertion auto via KSH

24-07 14:10 407 insertion auto via KSH

24-07 14:10 408 insertion auto via KSH

24-07 14:13 400 insertion auto via KSH

24-07 14:13 401 insertion auto via KSH

24-07 14:13 402 insertion auto via KSH

24-07 14:13 403 insertion auto via KSH

24-07 14:13 404 insertion auto via KSH

24-07 14:13 405 insertion auto via KSH

24-07 14:13 406 insertion auto via KSH

24-07 14:14 407 insertion auto via KSH

24-07 14:14 408 insertion auto via KSH

24-07 14:14 409 insertion auto via KSH

24-07 14:15 500 insertion auto via KSH

24-07 14:15 501 insertion auto via KSH

24-07 14:15 502 insertion auto via KSH

24-07 14:15 503 insertion auto via KSH

24-07 14:15 504 insertion auto via KSH

24-07 14:15 505 insertion auto via KSH

24-07 14:15 506 insertion auto via KSH

24-07 14:15 507 insertion auto via KSH

24-07 14:17 600 insertion auto via KSH

24-07 14:22 601 insertion auto via KSH


 

39 rows selected.


 

Now I’ve got 39 rows instead of 56 and I’m happy: mostly because I know how to change a character string with a slash in it!


 

NOTE: this does not work in SQLcl:

 

05-09 08:23 dsvm1644 SYSTEM > c/status/sql_idSP2-0023: String not found
17095doesnotworkinSQLcl.jpeg