Re: Sending email using PL/SQL

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Wed, 17 Feb 1999 16:36:17 +0800
Message-ID: <36CA7F81.1B66@bhp.com.au>

Peizhong Wu wrote:
>
> Hi, Chris
>
> as I know, DBMS_PIPE is used for communication between differenct session
> within ORACLE, how does it handle email?
>
> There is a package: DBMS_MAIL, it is said with Oracle Office, I do not know
> how it works and how to get it.
>
> I am using Oracle 8.
>
> Thanks
>
> Peizhong
>
> "Christopher M. Day" wrote:
>
> > Peizhong,
> >
> > You can interact with your underlying OS using either DBMS_PIPE on
> > Oracle7 or External Procedures on Oracle8. In Oracle8 it's much simpler
> > to implement.
> >
> > Chris.
> >
> > Oracle PL/SQL Programming 2nd Edition has an example in.
> >
> > peizhong wu wrote:
> > >
> > > Hi,
> > >
> > > Does anyone know if we can send email using PLSQL? Do we have to use the
> > >
> > > package mentioned in the book? How can I get that package.
> > >
> > > Thanks
> > >
> > > Peizhong Wu
> > > University of Michigan

Go to govt.us.oracle.com

In the downloadable utilities section, find PLEX - there is a good manual there about how to run OS commands from PL/SQl...

A quick hack way is to have it in SQL Plus:

Here is a PL/SQL subroutine you can install in your schema:

create or replace procedure host( cmd in varchar2 ) as

    status number;
begin

    dbms_pipe.pack_message( cmd );
    status := dbms_pipe.send_message( 'HOST_PIPE' );     if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );

    end if;
end;
/

Here is a C-Shell script you can run in the background (make sure it is named
host.csh)

• bof ---------------------------- #!/bin/csh -f

sqlplus tkyte/tkyte <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh  

set serveroutput on  

declare

        status  number;
        command varchar2(255);
begin
        status := dbms_pipe.receive_message( 'HOST_PIPE' );
        if ( status <> 0 ) then
                dbms_output.put_line( '#exit' );
        else
                dbms_pipe.unpack_message( command );
                dbms_output.put_line( '##!/bin/csh -f' );
                dbms_output.put_line( '#' || command );
                dbms_output.put_line( '#exec host.csh' );
        end if;

end;
/
spool off
"EOF"   chmod +x tmp.csh
exec tmp.csh

----------------------- EOF ---------------------------------

If you run this in the background (The script), you'll be able to have it
execute any host command you want. Run this in one window for example and in
anther window go into sql*plus and try:

SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );

You'll see the output of ls -l, uptime, and echo happen on the other window
where the shell script is running (shows you a way to debug pl/sql routines, use
"host( echo some string )" and you'll get real time feedback from your pl/sql
procedure).....

--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Wed Feb 17 1999 - 02:36:17 CST