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 :-)
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.
Have you configured a dispatcher for the XDB service?
No. I wasn’t aware I needed one to use httpuritype. I know utl_http doesn’t need one.
Will have a look, thanks for the pointer!
It looks like httpuritype does not work for https urls. Or is there a way.
Don’t know haven’t checked… but docs might help :)