Skip to main content

Export timed query results from SQL*Plus

Published: 2019-01-15 • Last updated: 2019-09-12

At work, we have to SSH into an ubuntu box in order to run queries. Since this box has no GUI, all work must be done through command line and SQL*Plus.

Sadly, pasting multi-line queries into SQL*Plus is a no-go. And copying query results, if they’re more than a few dozen lines, isn’t much better. So, after trial and error, here’s my solution:

  • Copy the multi-line query from my host (macOS)
  • Create a file on ubuntu box using vi, then paste in the query. Save it as file.sql
  • Open SQL*Plus and make a connection to the Oracle database
  • Run the query file by entering @file.sql
  • The timed results are exported to a file named output.txt
  • Use SCP to copy output.txt from the ubuntu box to my host

Here’s an example query file. Note, the query must end with a semicolon.

SPOOL output.txt
TIMING START timer

<query_goes_here>

TIMING STOP timer
SPOOL OFF

And an example SCP command.

 scp <username>@<host>:/path/to/output.txt ~/Desktop