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

/

Related Topics
Advanced Queuing Demo 1
Advanced Queuing RAC Demo
Built-in Functions
Built-in Packages
DBMS_ALERT
DBMS_AQ
DBMS_AQADM
DBMS_AQIN
DBMS_AQ_EXP_CMT_TIME_TABLES
DBMS_AQ_EXP_DEQUEUELOG_TABLES
DBMS_AQ_EXP_HISTORY_TABLES
DBMS_AQ_EXP_INDEX_TABLES
DBMS_AQ_EXP_TIMEMGR_TABLES
DBMS_AQ_IMP_ZECURITY
UTL_MAIL
UTL_MAIL_INTERNAL
UTL_SMTP
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx