Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to send email from pl/sql in 806?
hi saurabh
nops utl_http will not help it's a different procedure
here is the code in utltcp.sql file
hope it solves u'r problem
REM Copyright (c) 1999 by Oracle Corporation.
REM
REM NAME
REM utltcp.sql - PL/SQL Package for TCP/IP communication
REM (Package Specification of UTL_TCP)REM
REM MODIFIED (MM/DD/YY) REM jmuller 09/16/99 - Fix bug 708690: final pass REM rpang 08/11/99 - Added constants for error codes REM rpang 07/28/99 - Added BAD_ARGUMENT exception REM rpang 05/14/99 - Created
CREATE OR REPLACE PACKAGE utl_tcp AS
/*******************************************************************
/* * TCP connection type */
remote_host VARCHAR2(255), -- Remote host name remote_port PLS_INTEGER, -- Remote port number local_host VARCHAR2(255), -- Local host name local_port PLS_INTEGER, -- Local port number charset VARCHAR2(30), -- Character set for on-the-wire comm. newline VARCHAR2(2), -- Newline character sequence private_sd PLS_INTEGER, -- For internal use only private_bf RAW(32767), -- For internal use only private_bfsz PLS_INTEGER, -- For internal use only private_pos PLS_INTEGER, -- For internal use only private_end PLS_INTEGER, -- For internal use only private_mkpos PLS_INTEGER -- For internal use only);
/* * Carriage-return line-feed character sequence. */
/* * Exceptions */ buffer_too_small EXCEPTION; -- Buffer is too small for I/O end_of_input EXCEPTION; -- End of input from the connection network_error EXCEPTION; -- Network error bad_argument EXCEPTION; -- Bad argument passed in API call buffer_too_small_errcode CONSTANT PLS_INTEGER:= -20000; end_of_input_errcode CONSTANT PLS_INTEGER:= -20001; network_error_errcode CONSTANT PLS_INTEGER:= -20002; bad_argument_errcode CONSTANT PLS_INTEGER:= -20003; PRAGMA EXCEPTION_INIT(buffer_too_small, -20000); PRAGMA EXCEPTION_INIT(end_of_input, -20001); PRAGMA EXCEPTION_INIT(network_error, -20002); PRAGMA EXCEPTION_INIT(bad_argument, -20003); /**
/** * Determines the number of bytes available for reading from a TCP/IP * connection. It is the number of bytes that can be read immediately * without blocking. * * PARAMETERS * c TCP/IP connection * RETURN * The number of bytes available for reading without blocking. * EXCEPTIONS * network_error - network error */
/*----------------------- Binary Input/Output API -----------------------*/ /** * Reads binary data from a TCP/IP connection. This function does not * return until the specified number of bytes have been read, or the end * of input has been reached. * * PARAMETERS * c TCP/IP connection * data the data read (OUT) * len the max number of bytes to read * peek should this call be peek-only (i.e. keep the data read * in the input buffer to be read again later)? * RETURN * The number of bytes read. The actual number of bytes read may be * less than specified because the end of input has been reached. * EXCEPTIONS * end_of_input - end of input from the connection
/** * Writes binary data to a TCP/IP connection. This function does not * return until the specified number of bytes have been written. * * PARAMETERS * c TCP/IP connection * data the data to be written * len the number of bytes to write. When len is NULL, the * whole length of data is written. The actual amount of * data written may be less because of network condition * RETURN * The actual number of bytes written to the connection. * EXCEPTIONS * network_error - network error */ FUNCTION write_raw(c IN OUT NOCOPY connection, data IN RAW, len IN PLS_INTEGER DEFAULT NULL) RETURN PLS_INTEGER; /*------------------------- Text Input/Output API ----------------------*/ /**
/** * Writes text data to a TCP/IP connection. This function does not * return until the specified number of characters have been written. * * PARAMETERS * c TCP/IP connection * data the data to be written * len the number of characters to write. When len is NULL, * the whole length of data is written. The amount of * data returned may be less because of network condition * RETURN * Then number of bytes written to the connection. * EXCEPTIONS * network_error - network error * NOTES * Text messages will be converted to the on-the-wire character set, * specified when the connection was opened, before they are transmitted * on the wire. */ FUNCTION write_text(c IN OUT NOCOPY connection, data IN VARCHAR2, len IN PLS_INTEGER DEFAULT NULL) RETURN PLS_INTEGER; /*------------------- Line-oriented Input/Output API ----------------------*/ /** * Reads a text line from a TCP/IP connection. A line is terminated by * a line-feed, a carriage-return or a carriage-return followed by a * line-feed. The function does not return until the end of line or the * end of input is reached. * * PARAMETERS * c TCP/IP connection * data the data read (OUT) * remove_crlf remove the trailing new-line character(s) or not * peek should this call be peek-only (i.e. keep the data read * in the input buffer to be read again later)? * RETURN * The number of characters read. * EXCEPTIONS * end_of_input - end of input from the connection
/** * Writes a text line to a TCP/IP connection. The line is terminated * with the new-line character sequence sepecified when this connection * is opened. * * PARAMETERS * c TCP/IP connection * data the data to be written * RETURN * Then number of bytes written to the connection. * EXCEPTIONS * network_error - network error */ FUNCTION write_line(c IN OUT NOCOPY connection, data IN VARCHAR2 DEFAULT NULL) RETURN PLS_INTEGER; /*----------------- Convenient functions for Input API ------------------*/ /** * A convenient form of the read functions, which return the data read * instead of the amount of data read. * * PARAMETERS * c TCP/IP connection * len the max number of bytes or characters to read * removle_crlf remove the trailing new-line character(s) or not * peek should this call be peek-only (i.e. keep the data read * in the input buffer to be read again later)? * RETURN * The data (or line) read. * EXCEPTIONS * end_of_input - end of data from the connection
/** * Transmits all the output data in the output queue to the connection * immediately. * * PARAMETERS * c TCP/IP connection * RETURN * None. * EXCEPTIONS * network_error - network error */
/** * Closes a TCP/IP connection. After the connection is closed, all the * in the connection will be set to NULL. * * PARAMETERS * c TCP/IP connection * RETURN * None. * EXCEPTIONS * network_error - network error */
/** * Closes all open TCP/IP connections. * * PARAMETERS * None * RETURN * None * EXCEPTIONS * None */
END;
/
GRANT EXECUTE ON sys.utl_tcp TO PUBLIC;
DROP PUBLIC SYNONYM utl_tcp;
CREATE PUBLIC SYNONYM utl_tcp FOR sys.utl_tcp;
"Saurabh Sharma" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> <saurabhs_at_fcsl cc: td.com> Subject: Re: How to send email from pl/sql in 806? Sent by: root_at_fatcity.c om 05/25/2001 08:20 PM Please respond to ORACLE-L
hi,
i've oracle 8.1.5 on NT. but could'nt find the utltcp.sql , infact it had
the procedure utl_http
could it help?
> hi sharma > The builtin packages UTL_SMTP and UTL_TCP are available with oracle 8.1.5 > onwards , thats the reason you were getting compilation errors. > Ramana > > > > ---------- > > From: Saurabh Sharma[SMTP:saurabhs_at_fcsltd.com] > > Reply To: ORACLE-L_at_fatcity.com > > Sent: Friday, May 25, 2001 12:40 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: How to send email from pl/sql in 806? > > > > HI, > > i tried creating this procedure. but it results into compilationerrors.
> > says.. > > utl_tcp.connection must be declared, and so does for > > utl_tcp.get_line and > > utl_tcp.write_line > > > > how do we fefine them. where is this package utl_tcp > > > > pls explore. > > > > thanks. > > ----- Original Message ----- > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> > > Sent: Wednesday, May 23, 2001 11:05 AM > > > > > > > > > > try this procedure it works just replace the IP address in the line > > > c := utl_tcp.open_connection('192.168.1.1', 25); > > > with the IP address of u'r mail server > > > > > > regards > > > ______________________________________ > > > Omar Khalid > > > Software Engineer > > > LMKResources > > > A LANDMARK AFFILIATE > > > (Formerly Mathtech Pakistan Pvt. Ltd) > > > 18, F-8/3, Main Margalla Road, > > > Islamabad, Pakistan > > > Voice: 111-101-101*780 > > > Fax: 92-051-2255989 > > > Email: okhalid_at_lmkr.com > > > Web: www.lmkr.com > > > > > > > > > > > > > > rem > > ----------------------------------------------------------------------- > > > rem Filename: smtp.sql > > > rem Purpose: Send e-mail messages from PL/SQL > > > rem Notes: From Oracle8i release 8.1.6 one can send e-mail messages > > > rem directly from PL/SQL using either the UTL_TCP or UTL_SMTP > > > rem packages. No pipes or external procedures required. > > > rem Date: 27-Mar-2000 > > > rem Author: Frank Naude (frank_at_ibi.co.za) > > > > > rem > > ----------------------------------------------------------------------- > > > > > > CREATE OR REPLACE PROCEDURE SEND_MAIL ( > > > msg_from varchar2 := 'oracle', > > > msg_to varchar2, > > > msg_subject varchar2 := 'E-Mail message from your database', > > > msg_text varchar2 := '' ) > > > IS > > > c utl_tcp.connection; > > > rc integer; > > > BEGIN > > > c := utl_tcp.open_connection('192.168.1.1', 25); -- open the > > SMTP > > > port 25 on local machine > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'HELO 192.168.1.1'); > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from); > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to); > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'DATA'); -- Startmessage
> > > body > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'Subject: '||msg_subject); > > > rc := utl_tcp.write_line(c, ''); > > > rc := utl_tcp.write_line(c, msg_text); > > > rc := utl_tcp.write_line(c, '.'); -- End ofmessage
> > > body > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > rc := utl_tcp.write_line(c, 'QUIT'); > > > dbms_output.put_line(utl_tcp.get_line(c, TRUE)); > > > utl_tcp.close_connection(c); -- Close the > > > connection > > > EXCEPTION > > > when others then > > > raise_application_error(-20000,'Unable to send e-mail message > > from > > > pl/sql'); > > > END; > > > / > > > show errors > > > > > > -- Examples: > > > set serveroutput on > > > > > > exec send_mail(msg_to =>'Omar Khalid/IT/LotusCert/Pk'); > > > exec send_mail(msg_to =>'omar_khalid_at_mathtechonline.com'); > > > > > > exec send_mail(msg_to =>'omar_khalid_at_mathtechonline.com', > > > msg_text=>'Look Ma I can send mail from plsql'); > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Oracle DBA > > > <acur8dba_at_yaho To: Multiple recipientsof
> > list ORACLE-L <ORACLE-L_at_fatcity.com> > > > o.com> cc: > > > Sent by: Subject: How to sendemail
> > from pl/sql in 806? > > > root_at_fatcity.c > > > om > > > > > > > > > 05/23/2001 > > > 11:35 AM > > > Please respond > > > to ORACLE-L > > > > > > > > > > > > > > > > > > Hi, > > > > > > I am aware that 817 supports UTL_SMTP for this same > > > functionality. But how can one send email from pl/sql > > > in 806? > > > > > > > > > thanx > > > > > > ===== > > > Vicky D. Foster, > > > Senior Oracle DBA: > > > email: acur8dba_at_yahoo.com > > > > > > __________________________________________________ > > > Do You Yahoo!? > > > Yahoo! Auctions - buy the things you want at great prices > > > http://auctions.yahoo.com/ > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Oracle DBA > > > INET: acur8dba_at_yahoo.com > > > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California -- Public Internet access / MailingLists
> > > -------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > > > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: > > > INET: OKhalid_at_lmkr.com > > > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California -- Public Internet access / MailingLists
> > > -------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Saurabh Sharma > > INET: saurabhs_at_fcsltd.com > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Venkata Ramana Kanchinadam > INET: vkanchinadam_at_sierraopt.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L> (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Saurabh Sharma INET: saurabhs_at_fcsltd.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: OKhalid_at_lmkr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri May 25 2001 - 07:31:42 CDT