Sqlplus is my second home: Part 7 – Downloading files via sqlplus :-)

Tanel Poder

2013-01-24

Some years ago I wrote about how sqlplus allows you to run sqlplus scripts directly from HTTP and FTP locations instead of the local filesystem. By the way, I didn’t even notice – my blog is over 5 years old already! :)

I a recent email thread Marco Gralike just showed the simplest way I to open a HTTP URL and download + list its contents in a CLOB datatype. It’s the HTTPURITYPE and its getCLOB (and getBLOB) methods.

So, basically if you want to download and save a (text) file like a script without a browser for some reason – and assuming that your database server can make outgoing HTTP connections, you can use this trick:

SQL> SET LINES 1000 TRIMSPOOL ON TRIMOUT ON PAGESIZE 0 LONG 99999999 LONGCHUNKSIZE 99999999 FEEDBACK OFF HEAD OFF
SQL> SPOOL $HOME/snapper_download.sql
SQL> 
SQL> select httpuritype('https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql').getCLOB() from dual;
--------------------------------------------------------------------------------
--
-- File name:   snapper.sql
-- Purpose:     An easy to use Oracle session-level performance measurement tool
--              which does NOT require any database changes nor creation of any
--              database objects!
--
--              This is very useful for ad-hoc performance diagnosis in environments
--              with restrictive change management processes, where creating

... lots of output snipped ...

SQL> SPOOL OFF

Now you have to open the spool file (snapper_download.sql) and remove any garbage (sqlplus commands) from the top of the spool file and there you go:

SQL> @snapper_download all 5 1 3
Sampling SID 3 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v3.62 by Tanel Poder (  )

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      3, SYS       , STAT, opened cursors cumulative                                 ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, recursive calls                                           ,            44,        8.8,         ,             ,          ,           ,
      3, SYS       , STAT, recursive cpu usage                                       ,             9,        1.8,         ,             ,          ,           ,
      3, SYS       , STAT, CPU used by this session                                  ,             8,        1.6,         ,             ,          ,           ,
      3, SYS       , STAT, in call idle wait time                                    ,           412,       82.4,         ,             ,          ,           ,
      3, SYS       , STAT, session uga memory                                        ,         65512,      13.1k,         ,             ,          ,           ,
      3, SYS       , STAT, session pga memory                                        ,        327680,     65.54k,         ,             ,          ,           ,
      3, SYS       , STAT, calls to get snapshot scn: kcmgss                         ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, session cursor cache hits                                 ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, session cursor cache count                                ,             1,         .2,         ,             ,          ,           ,
      3, SYS       , STAT, workarea executions - optimal                             ,            48,        9.6,         ,             ,          ,           ,
      3, SYS       , STAT, execute count                                             ,            43,        8.6,         ,             ,          ,           ,
      3, SYS       , STAT, sorts (memory)                                            ,            45,          9,         ,             ,          ,           ,
      3, SYS       , STAT, sorts (rows)                                              ,          1867,      373.4,         ,             ,          ,           ,
      3, SYS       , TIME, PL/SQL execution elapsed time                             ,          5398,     1.08ms,      .1%, [          ],          ,           ,
      3, SYS       , TIME, DB CPU                                                    ,        435933,    87.19ms,     8.7%, [@         ],          ,           ,
      3, SYS       , TIME, sql execute elapsed time                                  ,        129913,    25.98ms,     2.6%, [#         ],          ,           ,
      3, SYS       , TIME, DB time                                                   ,        449166,    89.83ms,     9.0%, [#         ],          ,           ,
      3, SYS       , WAIT, PL/SQL lock timer                                         ,       4127427,   825.49ms,    82.5%, [WWWWWWWWW ],        41,        8.2,   100.67ms

--  End of Stats snap 1, end=2013-01-24 22:45:53, seconds=5

    <No active sessions captured during the sampling period>

--  End of ASH snap 1, end=2013-01-24 22:45:53, seconds=5, samples_taken=41
 

Of course this technique is more for fun and is not reliable for binary files (unless you use something like UTL_ENCODE.BASE64_ENCODE or UUENCODE first). But still pretty fun :) I was happy to see that accessing a HTTP resource within the database has become so simple that it’s just a one-liner (as opposed to all the UTL_HTTP code lines needed for HTTP access).

Have fun (and thanks Marco for the tip :-)


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS