Oracle | DBMS

Oracle DBMS_AQELM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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;
/