Oracle | DBMS

Oracle DBMS_AQELM Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Provides subprograms to manage the configuration of Oracle Streams Advanced Queuing (AQ) asynchronous notification by e-mail and HTTP

AUTHID DEFINER

Dependencies

DBMS_AQELM_LIB	UTL_SMTP	UTL_TCP
UTL_HTTP

Documented Yes: Packages and Types Reference

First Available 9.0

Security Model Owned by SYS with EXECUTE granted to SYSTEM and the AQ_ADMINISTRATOR_ROLE and EXECUTE_CATALOG_ROLE roles

Source {ORACLE_HOME}/rdbms/admin/dbmsaqem.plb

Subprograms

GET_MAILHOST	GET_TXTIMEOUT	SET_MAILPORT
GET_MAILPORT	HTTP_SEND	SET_PROXY
GET_PROXY	SEND_EMAIL	SET_SENDFROM
GET_SENDFROM	SET_MAILHOST	Demo

GET_MAILHOST

Returns the host name for the SMTP server that the database will uses send out e-mail notifications dbms_aqelm.get_mailhost(mailhost OUT VARCHAR2);

See the AQELM Demo Below

GET_MAILPORT

Returns the port number for the SMTP server dbms_aqelm.get_mailport(mailport OUT NUMBER);

See the AQELM Demo Below

GET_PROXY

Returns the name of the proxy server dbms_aqelm.get_proxy( proxy OUT VARCHAR2, no_proxy_domains OUT VARCHAR2);

See the AQELM Demo Below

GET_SENDFROM

Returns the sent-from e-mail address dbms_aqelm.get_sendfrom(sendfrom OUT VARCHAR2);

See the AQELM Demo Below

GET_TXTIMEOUT

Returns the email timeout value: The default is 30000 dbms_aqelm.get_txtimeout(timeout OUT NUMBER);

DECLARE

       tOut NUMBER;

      BEGIN

        dbms_aqelm.get_txtimeout(tOut);

        dbms_output.put_line(TO_CHAR(tOut));

      END;

      /

HTTP_SEND

Undocumented

Appears to send a specified line "WHAT" at a specified line number "WHATL" for a named URL

Returns 404 if the URL does not exist. dbms_aqelm.http_send( url IN VARCHAR2, what IN VARCHAR2, whatl IN NUMBER, status_code OUT VARCHAR2);

set serveroutput on

      

      DECLARE

       sc VARCHAR2(30);

       ls VARCHAR2(100) := 'TEST';

      BEGIN

        dbms_aqelm.http_send('www.morganslibrary.org/index.html', ls, 2, sc);

        dbms_output.put_line(sc);

      END;

      /

      

      DECLARE

       sc VARCHAR2(30);

       ls VARCHAR2(100) := 'TEST';

      BEGIN

        dbms_aqelm.http_send('www.morganslibrary.org/test.html', ls, 30, sc);

        dbms_output.put_line(sc);

      END;

      /

SEND_EMAIL

Undocumented dbms_aqelm.send_email(sendto IN VARCHAR2, text IN VARCHAR2);

TBD

SET_MAILHOST

Sets the host name for the SMTP server that the database will uses send out e-mail notifications dbms_aqelm.set_mailhost(mailhost IN VARCHAR2);

See the AQELM Demo Below

SET_MAILPORT

Sets the port number for the SMTP server dbms_aqelm.set_mailport(mailport IN NUMBER);

See the AQELM Demo Below

SET_PROXY

Sets the name of the proxy server dbms_aqelm.set_proxy( proxy IN VARCHAR2, no_proxy_domains IN VARCHAR2);

See the AQELM Demo Below

SET_SENDFROM

Sets the sent-from e-mail address dbms_aqelm.set_sendfrom(sendfrom IN VARCHAR2);

See the AQELM Demo Below

Demo

Demo Code set serveroutput on DECLARE mh VARCHAR2(100); mp NUMBER; sf VARCHAR2(50); np VARCHAR2(50); px VARCHAR2(50); BEGIN dbms_aqelm.set_mailhost('morganslibrary.org'); dbms_aqelm.set_mailport(25); dbms_aqelm.set_sendfrom('mailsys@morganslibrary.org'); dbms_aqelm.set_proxy('proxyserver@morganslibrary.org'); COMMIT; dbms_aqelm.get_mailhost(mh); dbms_output.put_line('Mail Host: ' || mh); dbms_aqelm.get_mailport(mp); dbms_output.put_line('Mail Port: ' || mp); dbms_aqelm.get_sendfrom(sf); dbms_output.put_line('Send From: ' || sf); dbms_aqelm.get_proxy(np, px); dbms_output.put_line('No Proxy: ' || np); dbms_output.put_line('Proxy: ' || px); END;
/