Using @http command with SQL*PLUS

By using the SQL*PLUS ‘@http’ command, we can access scripts that are located in a central repository – in this case residing on a machine where the 10g Oracle Apache Http Server is running. Rather than have local copies of SQL scripts residing on all the client machines, we can store them in one location and then run them from any client. If we need to add new scripts or edit scripts, similarly we do it in just the one location.

These scripts can be called from all kinds of clients including tools like Toad or from any server in the same network as the Apache web server.

The set up is very straight forward.

In the HTTP server Oracle Home we create a sub directory to store the scripts.

$:/u02/oradata/product/apache/Apache/Apache/> mkdir scripts

We now copy all the *.sql files containing the scripts which we would like to run in this location

We then add the alias ‘scripts’ in the http.conf file

$:/u02/oradata/product/apache/Apache/Apache> pwd
/u02/oradata/product/apache/Apache/Apache

$:/u02/oradata/product/apache/Apache/Apache> cd conf

$:/u02/oradata/product/apache/Apache/Apache/conf> vi httpd.conf
……
………
Alias /icons/ “/u02/oradata/product/apache/Apache/Apache/icons/”
Alias /javacachedocs/ “/u02/oradata/product/apache/javacache/javadoc/”
Alias /i/ “/u01/oracle/product/11.0/apex/images/”
Alias /scripts/ “/u02/oradata/product/apache/Apache/Apache/scripts/”

Using the opmnctl stopall and startall commands we restart the Apache web server so that our changes can now be picked up.

We then launch SQL*PLUS client from any machine – connect to a database and then call the stored scripts via http as shown below .

gnu1d:/u02/oradata/product/apache/Apache/Apache/conf> sql

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 6 14:39:43 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> @http://devu007:7782/scripts/active_users.sql

O/S                                                 Oracle  Oracle
Login                  Oracle             Session  Session  Serial
ID                     User ID            Status        ID      No Process
---------------------- ------------------ -------- ------- ------- --------
oracle                 SYS                ACTIVE        10   14638  757982