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

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('http://blog.tanelpoder.com/files/scripts/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 ( http://blog.tanelpoder.com )

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    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

    

--  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 :-)

This entry was posted in Cool stuff, Oracle. Bookmark the permalink.

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

  1. Noons says:

    Hmmm, not working for me. From my DMZ OracleXE test instance:
    SQL> select
    2 httpuritype(‘http://blog.tanelpoder.com/files/scripts/snapper.sql’).getCLOB()
    3 from dual;
    ERROR:
    ORA-29273: HTTP request failed
    ORA-06512: at “SYS.UTL_HTTP”, line 1819
    ORA-12541: TNS:no listener
    ORA-06512: at “SYS.HTTPURITYPE”, line 34
    I wonder wehat the issue is? Of course, there is a listener there, as well as the access being open as far as ACLs are concerned. Rel 11.2.0.3.

  2. Utpal says:

    It looks like httpuritype does not work for https urls. Or is there a way.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>