Loading....

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
Please follow and like us:

Last Update: April 6, 2010  

April 6, 2010 223 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

6 thoughts on “Using @http command with SQL*PLUS

  1. Jim Gillespie

    Gavin,

    How does //devu007:7782 equal /u02/oradata/product/apache/Apache/Apache?

    Jim

  2. Gavin Soorma

    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

  3. Roy Hayrosa

    Hi Gavin,

    Great example! just a question, can we secure this process? use https instead?

    -roy

  4. Gavin Soorma

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

  5. Andrey Goryunov

    Gavin,

    as far as I know and tried – execution of scripts does not work through https

    Andrey.

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?