• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7814
  • Last Modified:

Calling a URL from within an Oracle PL/SQL procedure

Hi,

How can I be able to open a URL from an Oracle PL/SQL procedure/package? Does anybody have an idea? TIA...

Cheers!
V.
0
Nakuru1234
Asked:
Nakuru1234
  • 3
  • 2
4 Solutions
 
schwertnerCommented:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adgweb.htm


CREATE OR REPLACE PROCEDURE show_url
(
    url      IN VARCHAR2,
    username IN VARCHAR2 DEFAULT NULL,
    password IN VARCHAR2 DEFAULT NULL
) AS
    req       utl_http.req;
    resp      utl_http.resp;
    name      VARCHAR2(256);
    value     VARCHAR2(1024);
    data      VARCHAR2(255);
    my_scheme VARCHAR2(256);
    my_realm  VARCHAR2(256);
    my_proxy  BOOLEAN;
BEGIN
-- When going through a firewall, pass requests through this host.
-- Specify sites inside the firewall that don't need the proxy host.
  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');

-- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
-- rather than just returning the text of the error page.
  utl_http.set_response_error_check(FALSE);

-- Begin retrieving this web page.
  req := utl_http.begin_request(url);

-- Identify ourselves. Some sites serve special pages for particular browsers.
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

-- Specify a user ID and password for pages that require them.
  IF (username IS NOT NULL) THEN
    utl_http.set_authentication(req, username, password);
  END IF;

  BEGIN
-- Now start receiving the HTML text.
    resp := utl_http.get_response(req);

-- Show the status codes and reason phrase of the response.
    dbms_output.put_line('HTTP response status code: ' || resp.status_code);
    dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);

-- Look for client-side error and report it.
    IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN

-- Detect whether the page is password protected, and we didn't supply
-- the right authorization.
      IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN
        utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy);
        IF (my_proxy) THEN
          dbms_output.put_line('Web proxy server is protected.');
          dbms_output.put('Please supply the required ' || my_scheme ||
            ' authentication username/password for realm ' || my_realm ||
            ' for the proxy server.');
        ELSE
          dbms_output.put_line('Web page ' || url || ' is protected.');
          dbms_output.put('Please supplied the required ' || my_scheme ||
            ' authentication username/password for realm ' || my_realm ||
            ' for the Web page.');
        END IF;
      ELSE
        dbms_output.put_line('Check the URL.');
      END IF;

      utl_http.end_response(resp);
      RETURN;

-- Look for server-side error and report it.
    ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN

      dbms_output.put_line('Check if the Web site is up.');
      utl_http.end_response(resp);
      RETURN;

    END IF;
   
-- The HTTP header lines contain information about cookies, character sets,
-- and other data that client and server can use to customize each session.
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
      utl_http.get_header(resp, i, name, value);
      dbms_output.put_line(name || ': ' || value);
    END LOOP;

-- Keep reading lines until no more are left and an exception is raised.
    LOOP
      utl_http.read_line(resp, value);
      dbms_output.put_line(value);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
  END;

END;
/
SET serveroutput ON
-- The following URLs illustrate the use of this procedure,
-- but these pages do not actually exist. To test, substitute
-- URLs from your own web server.
exec show_url('http://www.oracle.com/no-such-page.html')
exec show_url('http://www.oracle.com/protected-page.html')
exec show_url('http://www.oracle.com/protected-page.html', 'scott', 'tiger')


0
 
Mark GeerlingsDatabase AdministratorCommented:
OK, so it may be possible to call a URL from a PL\SQL stored procedure, but this seems like an unusual request.  PL\SQL stored procedures are good at manipulating data without needing human interaction.  Most web pages are designed for human interaction.  This sounds like an attempt to connect two rather dis-similar tasks.  Do you have some URLs that lend themselves to automated data transfers?  Or, what business problem would this solve?
0
 
Nakuru1234Author Commented:
exec show_url('http://www.oracle.com/no-such-page.html')
exec show_url('http://www.oracle.com/protected-page.html')
exec show_url('http://www.oracle.com/protected-page.html', 'scott', 'tiger')
 
The pages above do not exist...also I just want to call/open a url from a procedure/package wheather its via browser or maybe in Unix...

Cheers!
V.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Mark GeerlingsDatabase AdministratorCommented:
You "want to call/open a URL from a procedure/package" to do what?

If the stored procedure is called from a client program of some kind, isn't it much easier to also call/open a URL from the client program?

If the stored procedure is being run unattended like by a scheduled job, what do want PL\SQL to be able to do with the URL?
0
 
Nakuru1234Author Commented:
I want to fire up a cognos report from Glovia.  Govia is an ERP application that runs on Oracle Database. I can if the url is less than 255 bytes long, but with parameters I may need to send, it could be bigger than 255 characters.  I was looking for a way around this limitation by using either pl/sql or unix. TIA

Cheers!
V.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Oracle PL\SQL stored procedures execute in the context (and RAM) of the server, so even if you could get a PL\SQL stored procedure to call a url which runs a Cognos report, how would that report become available to a particular user?
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now