Delete duplicate row

DELETE FROM my_table
WHERE rowid NOT IN ( SELECT MAX(ROWID) FROM my_table
                     GROUP BY colA,colB,colC );

In the GROUP BY clause, enumerate all of your columns in your table, or the columns you think should be the primary key columns. The subquery will get the max rowid of these groupings. The DELETE will remove all rows that do not have these rowid values.

Using LIKE Instead of SUBSTR

Do you LIKE to SUBSTR?

I need to implement the following requirement:

“If the first three characters of the pet type is CAT, then buy catnip. If the first three characters of the pet type is DOG, then buy milkbones.”

What’s the best way to satisfy this requirement? Well, I know how to identify the first three characters of a string…use SUBSTR. So my first inclination would be to code as follows:

v_first_three := SUBSTR (pet_type, 1, 3);

IF v_first_three = 'CAT'

THEN

   buy_catnip;

ELSIF v_first_three = 'DOG'

THEN

   buy_milkbones;

END IF;

And that certainly does the trick. But is it the only way? Unlikely…in very few situations do you just have a single way to implement a requirement. In this case, I could also use the LIKE operator, as follows:

IF pet_type LIKE 'CAT%'

THEN

   buy_catnip;

ELSIF pet_type LIKE 'DOG%'

THEN

   buy_milkbones;

END IF;

It turns out that using the LIKE operator is more efficient than SUBSTR in this scenario (not by a whole lot, but consistently faster). My conclusion: when checking the contents of a beginning of a string, use LIKE instead of SUBSTR.

From http://www.quest-pipelines.com/pipelines/plsql/tips00.htm#august

Wildcard Characters and the LIKE Operator

Let’s explore a nuance of the LIKE operator…

expression LIKE 'pattern' [ESCAPE 'escape-character']

I bet just about every one of you is aware of the two wildcard characters used in Oracle’s SQL for string comparisons: _ Match to any single character in this position

% Match to any number of characters in this position

(As you can see, “*” is not a wildcard character in SQL.)

These wildcard characters come in handy when using the LIKE operator. If, for example, I want to display the names of everyone who likes any variety of chocolate ice cream, I could write this query:

SELECT name

  FROM general_preference

 WHERE flavor LIKE 'CHOC%'

   AND topic = 'ICE CREAM';

And if I need to identify all companies placing orders in the 3rd quarter of the year, that information (“3″) stored (in a very questionable design decision) in the 16th character of the order number, I could write this query:

SELECT company_id

  FROM order

 WHERE order_id LIKE RPAD ('_', '_', 15) || '3%';

Great stuff! OK, but what if our string contains wildcard characters? It is certainly common, for example, to place underscores in the names of database objects (see “general_preference” above). How do I answer the question “Show me all database objects that contain an underscore?” I’d like to write the query as follows:

SELECT object_name

  FROM user_objects

 WHERE object_name LIKE '%_%';

Yet this query would always return the names of all my objects. To write this query, I need to tell SQL to stop using the underscore character as a wildcard, which I can do with the ESCAPE clause:

SELECT object_name

  FROM user_objects

 WHERE object_name LIKE '%^_%' ESCAPE '^';

From http://www.quest-pipelines.com/pipelines/plsql/tips00.htm#july

Login as SYS to test

select * from tab;
3667

select * from tab where tname like ‘%^_%’ escape ‘^’;
2961

select * from tab where tname not like ‘%^_%’ escape ‘^’;
706

Sending e-mail from an Oracle 10g Server

In order to send e-mail within 10g you must install and set up the UTL_MAIL package.

UTL_MAIL isn’t installed when the database is installed because the SMTP_OUT_SERVER parameter must be configured. The example below shows how to install UTL_MAIL and the results from the script. You must connect to the database as user SYS and run the two scripts identified in the example.

SQL> connect sys/password as sysdba
Connected. 

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql 

Package created. 

Synonym created. 

SQL> @$ORACLE_HOME /rdbms/admin/prvtmail.plb 

Package body created. 

No errors.

Next, the SMTP_OUT_SERVER parameter must be configured. You must connect to SYS and then use the alter system command to configure SMTP_OUT_SERVER parameter as shown here:

SQL> alter system set smtp_out_server = '<ip-address:port' scope=Both; 

System altered.

That’s it! The installation and setup for UTL_MAIL is complete. The UTL_MAIL package only has one procedure, called Send, for sending e-mail (see below for the syntax for the Send procedure). This package bundles the e-mail message and sends it to the UTL_SMTP package, and then the e-mail message is sent to the SMTP server.

Syntax for UTL_MAIL.SEND:

UTL_MAIL.SEND( sender IN VARCHAR2,
               recipients IN VARCHAR2,
               cc IN VARCHAR2 DEFAULT NULL,
               bcc IN VARCHAR2 DEFAULT NULL,
               subject IN VARCHAR2 DEFAULT NULL,
               message IN VARCHAR2,
               mime_type IN VARCHAR2 DEFAULT
               'text/plain; charset=us-ascii',
               priority IN PLS_INTEGER DEFAULT  NULL);

I’ve created a database trigger that will send an e-mail using UTL_MAIL:

Database shutdown using UTL_MAIL

CREATE OR REPLACE TRIGGER SCOTT.db_shutdown
before shutdown on database
begin
 utl_mail.send(
   sender => 'gjwilliams01@yahoo.com',
   recipients => ' gjwilliams01@yahoo.com',
   subject => 'Testing utl_mail',
   message => 'The receipt of this email means'||
    ' that shutting down the database'||
    ' works for UTL_MAIL '
   );
end;
/

There are two other procedures within the UTL_MAIL package that make life easy: UTL_MAIL.ATTACH_VARCHAR2 and UTL_MAIL.ATTACH_RAW. These procedures are for varchar2 and raw attachments, respectively.

From http://www.quest-pipelines.com/pipelines/dba/tips06.htm#september

Test the following DEMO example worked

- VPN open (where SMTP server up and running)

- telnet smtp.domain.com 25

- Create the following PACKAGE

CREATE OR REPLACE PACKAGE demo_mail IS

----------------------- Customizable Section -----------------------

-- Customize the SMTP host, port and your domain name below.
smtp_host VARCHAR2(256) := 'smtp.domain.com';
smtp_port PLS_INTEGER := 25;
smtp_domain VARCHAR2(256) := 'domain.com';

-- Customize the signature that will appear in the email's MIME header.
-- Useful for versioning.
MAILER_ID CONSTANT VARCHAR2(256) := 'Mailer by Jenny';

--------------------- End Customizable Section ---------------------

-- A unique string that demarcates boundaries of parts in a multi-part email
-- The string should not appear inside the body of any part of the email.
-- Customize this if needed or generate this randomly dynamically.
BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
utl_tcp.CRLF;

-- A MIME type that denotes multi-part email (MIME) messages.
MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
BOUNDARY || '"';
MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER := 76 / 4 * 3;

-- A simple email API for sending email in plain text in a single call.
-- The format of an email address is one of these:
-- someone@some-domain
-- "Someone at some domain" <someone@some-domain>
-- Someone at some domain <someone@some-domain>
-- The recipients is a list of email addresses separated by
-- either a "," or a ";"
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2);

-- Extended email API to send email in HTML or plain text with no size limit.
-- First, begin the email by begin_mail(). Then, call write_text() repeatedly
-- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
-- email in non-ASCII or multi-byte character set. End the email with
-- end_mail().
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection;

-- Write email body in ASCII
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);

-- Write email body in non-ASCII (including multi-byte). The email body
-- will be sent in the database character set.
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2);

-- Write email body in binary
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW);

-- APIs to send email with attachments. Attachments are sent by sending
-- emails in "multipart/mixed" MIME format. Specify that MIME format when
-- beginning an email with begin_mail().

-- Send a single text attachment.
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);

-- Send a binary attachment. The attachment will be encoded in Base-64
-- encoding format.
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE);

-- Send an attachment with no size limit. First, begin the attachment
-- with begin_attachment(). Then, call write_text repeatedly to send
-- the attachment piece-by-piece. If the attachment is text-based but
-- in non-ASCII or multi-byte character set, use write_mb_text() instead.
-- To send binary attachment, the binary content should first be
-- encoded in Base-64 encoding format using the demo package for 8i,
-- or the native one in 9i. End the attachment with end_attachment.
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL);

-- End the attachment.
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE);

-- End the email.
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

-- Extended email API to send multiple emails in a session for better
-- performance. First, begin an email session with begin_session.
-- Then, begin each email with a session by calling begin_mail_in_session
-- instead of begin_mail. End the email with end_mail_in_session instead
-- of end_mail. End the email session by end_session.
FUNCTION begin_session RETURN utl_smtp.connection;

-- Begin an email in a session.
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL);

-- End an email in a session.
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);

-- End an email session.
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;
/
CREATE OR REPLACE PACKAGE BODY demo_mail IS

-- Return the next email address in the list of email addresses, separated
-- by either a "," or a ";". The format of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain" <someone@some-domain>
-- Someone at some domain <someone@some-domain>
FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

addr VARCHAR2(256);
i pls_integer;

FUNCTION lookup_unquoted_char(str IN VARCHAR2,
chrs IN VARCHAR2) RETURN pls_integer AS
c VARCHAR2(5);
i pls_integer;
len pls_integer;
inside_quote BOOLEAN;
BEGIN
inside_quote := false;
i := 1;
len := length(str);
WHILE (i <= len) LOOP

c := substr(str, i, 1);

IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := false;
ELSIF (c = '\') THEN
i := i + 1; -- Skip the quote character
END IF;
GOTO next_char;
END IF;

IF (c = '"') THEN
inside_quote := true;
GOTO next_char;
END IF;

IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;

<<next_char>>
i := i + 1;

END LOOP;

RETURN 0;

END;

BEGIN

addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list, ',;');
IF (i >= 1) THEN
addr := substr(addr_list, 1, i - 1);
addr_list := substr(addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char(addr, '<');
IF (i >= 1) THEN
addr := substr(addr, i + 1);
i := instr(addr, '>');
IF (i >= 1) THEN
addr := substr(addr, 1, i - 1);
END IF;
END IF;

RETURN addr;
END;

-- Write a MIME header
PROCEDURE write_mime_header(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2,
value IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
END;

-- Mark a message-part boundary. Set <last> to TRUE for the last boundary.
PROCEDURE write_boundary(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) AS
BEGIN
IF (last) THEN
utl_smtp.write_data(conn, LAST_BOUNDARY);
ELSE
utl_smtp.write_data(conn, FIRST_BOUNDARY);
END IF;
END;

------------------------------------------------------------------------
PROCEDURE mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2) IS
conn utl_smtp.connection;
BEGIN
conn := begin_mail(sender, recipients, subject);
write_text(conn, message);
end_mail(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_mail(sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL)
RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
conn := begin_session;
begin_mail_in_session(conn, sender, recipients, subject, mime_type,
priority);
RETURN conn;
END;

------------------------------------------------------------------------
PROCEDURE write_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(conn, message);
END;

------------------------------------------------------------------------
PROCEDURE write_mb_text(conn IN OUT NOCOPY utl_smtp.connection,
message IN VARCHAR2) IS
BEGIN
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
END;

------------------------------------------------------------------------
PROCEDURE write_raw(conn IN OUT NOCOPY utl_smtp.connection,
message IN RAW) IS
BEGIN
utl_smtp.write_raw_data(conn, message);
END;

------------------------------------------------------------------------
PROCEDURE attach_text(conn IN OUT NOCOPY utl_smtp.connection,
data IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
begin_attachment(conn, mime_type, inline, filename);
write_text(conn, data);
end_attachment(conn, last);
END;

------------------------------------------------------------------------
PROCEDURE attach_base64(conn IN OUT NOCOPY utl_smtp.connection,
data IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
last IN BOOLEAN DEFAULT FALSE) IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN

begin_attachment(conn, mime_type, inline, filename, 'base64');

-- Split the Base64-encoded attachment into multiple lines
i := 1;
len := utl_raw.length(data);
WHILE (i < len) LOOP
IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i,
MAX_BASE64_LINE_WIDTH)));
ELSE
utl_smtp.write_raw_data(conn,
utl_encode.base64_encode(utl_raw.substr(data, i)));
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF);
i := i + MAX_BASE64_LINE_WIDTH;
END LOOP;

end_attachment(conn, last);

END;

------------------------------------------------------------------------
PROCEDURE begin_attachment(conn IN OUT NOCOPY utl_smtp.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL) IS
BEGIN
write_boundary(conn);
write_mime_header(conn, 'Content-Type', mime_type);

IF (filename IS NOT NULL) THEN
IF (inline) THEN
write_mime_header(conn, 'Content-Disposition',
'inline; filename="'||filename||'"');
ELSE
write_mime_header(conn, 'Content-Disposition',
'attachment; filename="'||filename||'"');
END IF;
END IF;

IF (transfer_enc IS NOT NULL) THEN
write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
END IF;

utl_smtp.write_data(conn, utl_tcp.CRLF);
END;

------------------------------------------------------------------------
PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
last IN BOOLEAN DEFAULT FALSE) IS
BEGIN
utl_smtp.write_data(conn, utl_tcp.CRLF);
IF (last) THEN
write_boundary(conn, last);
END IF;
END;

------------------------------------------------------------------------
PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
end_mail_in_session(conn);
end_session(conn);
END;

------------------------------------------------------------------------
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection(smtp_host, smtp_port);
utl_smtp.helo(conn, smtp_domain);
RETURN conn;
END;

------------------------------------------------------------------------
PROCEDURE begin_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL) IS
my_recipients VARCHAR2(32767) := recipients;
my_sender VARCHAR2(32767) := sender;
BEGIN

-- Specify sender's address (our server allows bogus address
-- as long as it is a full email address (xxx@yyy.com).
utl_smtp.mail(conn, get_address(my_sender));

-- Specify recipient(s) of the email.
WHILE (my_recipients IS NOT NULL) LOOP
utl_smtp.rcpt(conn, get_address(my_recipients));
END LOOP;

-- Start body of email
utl_smtp.open_data(conn);

-- Set "From" MIME header
write_mime_header(conn, 'From', sender);

-- Set "To" MIME header
write_mime_header(conn, 'To', recipients);

-- Set "Subject" MIME header
write_mime_header(conn, 'Subject', subject);

-- Set "Content-Type" MIME header
write_mime_header(conn, 'Content-Type', mime_type);

-- Set "X-Mailer" MIME header
write_mime_header(conn, 'X-Mailer', MAILER_ID);

-- Set priority:
-- High Normal Low
-- 1 2 3 4 5
IF (priority IS NOT NULL) THEN
write_mime_header(conn, 'X-Priority', priority);
END IF;

-- Send an empty line to denotes end of MIME headers and
-- beginning of message body.
utl_smtp.write_data(conn, utl_tcp.CRLF);

IF (mime_type LIKE 'multipart/mixed%') THEN
write_text(conn, 'This is a multi-part message in MIME format.' ||
utl_tcp.crlf);
END IF;

END;

------------------------------------------------------------------------
PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.close_data(conn);
END;

------------------------------------------------------------------------
PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
BEGIN
utl_smtp.quit(conn);
END;

END;
/

- Test as follows:

CREATE OR REPLACE procedure MailTest IS
Begin
DEMO_MAIL.mail('sender_email_address',
'receiver_email_address',
'Just a test for sending email via Oracle',
'It is ok.');
end;
/

begin
MailTest;
end;
/

ORA-29278 may signify that, “the same server where you have your Oracle database running, does not have an SMTP server up and running.”