News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Using @http command with SQL*PLUS

  • Posted by Gavin Soorma
  • On April 6, 2010
  • 6 Comments
  • @http

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
 

6 Comments

Surachart Opun
  • Apr 7 2010
Good Example to use @http Thank You -)
Jim Gillespie
  • Apr 10 2010
Gavin, How does //devu007:7782 equal /u02/oradata/product/apache/Apache/Apache? Jim
Gavin Soorma
  • Apr 10 2010
Hi Jim - the Oracle Home for the HTTP server is "/u02/oradata/product/apache" - the port of the apache web server is 7782 and the hostname is devu007 where the HTTP server Oracle Home is located
Roy Hayrosa
  • Apr 11 2010
Hi Gavin, Great example! just a question, can we secure this process? use https instead? -roy
Gavin Soorma
  • Apr 12 2010
Hi Roy - if your Apache server is configured to run as https instead of http, then the scripts can be run over https - all you are doing is creating directory mapping for the scripts physical location via the httpd.conf file ....
Andrey Goryunov
  • Apr 13 2010
Gavin, as far as I know and tried - execution of scripts does not work through https Andrey.

Leave Reply

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