Skip to content
≡

Administrator

Developer

Tools

External Tools

UTL SMTP

UTL_SMTP#

UTL_SMTP can execute SMTP for SMTP server to send an E-mail. Procedures and functions comprising UTL_SMTP is as follows.

Procedures/Functions Description
OPEN_CONNECTION Creates TCP socket and connects to SMTP server.
HELO Sends default command of SMTP, HELO domain.
MAIL Sends a command of SMTP specifying the sender, MAIL FROM:\
RCPT Sends a command of SMTP specifying the receiver, MAIL FROM:\
OPEN_DATA Sends a command of SMTP starting the data transmission, DATA.
WRITE_DATA Sends data using SMTP.
WRITE_RAW_DATA Sends RAW data using SMTP.
CLOSE_DATA Sends a command of SMTP ending the data transmission, \ . \.
QUIT Sends a command of SMTP ending the connection, QUIT.

OPEN_CONNECTION#

Creates TCP socket and connects to SMTP server using the inputted IP and PORT.

Syntax#

UTL_SMTP.OPEN_CONNECTION (
  host IN VARCHAR(64),
  port IN INTEGER DEFAULT 25,
  tx_timeout IN INTEGER DEFAULT NULL );

Parameters#

Name In/Output Data type Description
host IN VARCHAR(64) IP address of SMTP server
port IN INTEGER Port number of SMTP server
tx_timeout IN INTEGER This parameter is for compatibility, the value of this parameter is ignored.

Return Value#

CONNECT_TYPE returns connection handle when successed.

Exception#

CONNECT_TYPE returns NULL when failed.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

HELO#

Sends HELO domain command to the connected SMTP server to execute reset handshaking.

Syntax#

UTL_SMTP.HELO (
  c IN OUT CONNECT_TYPE,
  domain IN VARCHAR(64) );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
domain IN VARCHAR(64) The domain name

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_CONNECTION function has to be called first in order to call the HELO function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

MAIL#

Sends MAIL FORM:\ command to the connected SMTP server to specify the sender.

Syntax#

UTL_SMTP.MAIL (
  c IN OUT CONNECT_TYPE,
  sender IN VARCHAR(256),
  parameters IN VARCHAR DEFAULT NULL );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
sender IN VARCHAR(256) The sender address
parameters IN VARCHAR This parameter is for compatibility, the value of this parameter is ignored.

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. HELO function has to be called first in order to call the MAIL function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, '[email protected]', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

RCPT#

Sends RCPT TO:\ command to the connected SMTP server to specify the receiver.

Syntax#

UTL_SMTP.RCPT (
  c IN OUT CONNECT_TYPE,
  recipient IN VARCHAR(256),
  parameters IN VARCHAR DEFAULT NULL );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
recipient IN VARCHAR(256) The receiver address
parameters IN VARCHAR This parameter is for compatibility, the value of this parameter is ignored.

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. MAIL function has to be called first in order to call the RCPT function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

OPEN_DATA#

Sends DATA command to the connected SMTP server to start the data transmission.

Syntax#

UTL_SMTP.DATA (
  c IN OUT CONNECT_TYPE,
  body IN VARCHAR DEFAULT NULL );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
body IN VARCHAR This parameter is for compatibility, the value of this parameter is ignored.

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. RCPT function has to be called first in order to call the DATA function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V2 := CLOSE_CONNECT( V1 );
END;
/

WRITE_DATA#

Transmits the data to the connected SMTP server.

Syntax#

UTL_SMTP.WRITE_DATA (
  c IN OUT CONNECT_TYPE,
  data IN VARCHAR(65534) );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
data IN VARCHAR(65534) Data to transmit

Return Value#

Returns the length of the transmitted data when succeeded. Returns -1 when failed.

Exception#

Exception occurred when it violates the SMTP. OPEN_DATA function has to be called first in order to call the WRITE_DATA function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_DATA( V1, 'test' );
  V2 := CLOSE_CONNECT( V1 );
END;
/

WRITE_RAW_DATA#

Transmits RAW data to the connected SMTP server.

Syntax#

UTL_SMTP.WRITE_DATA (
  c IN OUT CONNECT_TYPE,
  data IN RAW(65534) );

Parameters#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server
data IN RAW(65534) RAW data to transmit

Return Value#

Returns the length of the transmitted data when succeeded. Returns -1 when failed.

Exception#

Exception occurred when it violates the SMTP. OPEN_RAW_DATA function has to be called first in order to call the WRITE_RAW_DATA function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V2 := CLOSE_CONNECT( V1 );
END;
/

CLOSE_DATA#

Sends \.\ command to the connected SMTP server and ends the data transmission.

Syntax#

UTL_SMTP.CLOSE_DATA (
  c IN OUT CONNECT_TYPE );

Parameter#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_DATA function has to be called first in order to call the CLOSE_DATA function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V2 INTEGER;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V3 := SMTP.CLOSE_DATA( V1 );
  V2 := CLOSE_CONNECT( V1 );
END;
/

QUIT#

Sends QUIT command to the connected SMTP server to end the connection between SMTP session and SMTP server.

Syntax#

UTL_SMTP.QUIT (
  c IN OUT CONNECT_TYPE );

Parameter#

Name In/Output Data Type Description
c IN OUT CONNECT_TYPE Connection handle of SMTP server

Return Value#

Returns the VARCHAR type result value including response code and message of the SMTP server. In case the connection to the server fails, it returns NULL.

Exception#

Exception occurred when it receives failed response code from the SMTP server or violates the SMTP. OPEN_CONNECTION function has to be called first in order to call the QUIT function.

Example#

CREATE OR REPLACE PROCEDURE PROC1
AS
  V1 CONNECT_TYPE;
  V3 VARCHAR(65534);
BEGIN
  V1 := SMTP.OPEN_CONNECTION( '127.0.0.1', 25, null );
  V3 := SMTP.HELO( V1, '127.0.0.1', null );
  V3 := SMTP.MAIL( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.RCPT( V1, ['[email protected]](http://nok.altibase.com/mailto:)', null );
  V3 := SMTP.OPEN_DATA( V1, null );
  V3 := SMTP.WRITE_RAW_DATA( V1, TO_RAW( 'test' ) );
  V3 := SMTP.CLOSE_DATA( V1 );
  V3 := SMTP.QUIT( V1 );
END;
/