Hi: I can't attach an external file to a message. The e-mail is sent, I receive it, but it comes with no attachments. I have installed all the functions, the table, sequence, and created an Oracle Directory, mapping e:\orant\net80\admin. I try to send a message, attaching the file tnsnames.ora, located in the mentioned directory (E: is a server's local HDD logical partition, there is located the Oracle_home: e:\orant).

The DB Mail console shows an error: error retrieving file (the attachment file). The file exists, the directory exists, what's wrong? I use a variation of an example given in DB Mail help:
DECLARE
attach_id INTEGER;
ret_code INTEGER;
BEGIN
attach_id := system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'adjuntos2');
ret_code := send_mail('lrozas@lemans.cl', 'Message with attachments. By LEO',
'This message has one attachment... says LEO', NULL, NULL, NULL, attach_id);
END;
/

The attach_id value when system.attach_file is called, is -1. I see it as an error, that's because the DB Mail console shows 'error retrieving file'. Definetively, I think that the Oracle Directory is wrong. What can I do? Is the server local 'path to orant\net80\admin' correct. The Oracle server is on Windows NT.

Thanks and sorry about my english (I'm from Chile). Bye.

Try specifying directory name in upper case as 'ADJUNTOS2'
Let us know if it does not help.

: Hi: I can't attach an external file to a message. The e-mail is sent, I
: receive it, but it comes with no attachments. I have installed all the
: functions, the table, sequence, and created an Oracle Directory, mapping
: e:\orant\net80\admin. I try to send a message, attaching the file
: tnsnames.ora, located in the mentioned directory (E: is a server's local
: HDD logical partition, there is located the Oracle_home: e:\orant).

: The DB Mail console shows an error: error retrieving file (the attachment
: file). The file exists, the directory exists, what's wrong? I use a
: variation of an example given in DB Mail help: DECLARE
: attach_id INTEGER;
: ret_code INTEGER;
: BEGIN
: attach_id := system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'adjuntos2');
: ret_code := send_mail('lrozas@lemans.cl', 'Message with attachments. By LEO',
: 'This message has one attachment... says LEO', NULL, NULL, NULL, attach_id);
: END;
: /

: The attach_id value when system.attach_file is called, is -1. I see it as an
: error, that's because the DB Mail console shows 'error retrieving file'.
: Definetively, I think that the Oracle Directory is wrong. What can I do?
: Is the server local 'path to orant\net80\admin' correct. The Oracle server
: is on Windows NT.

: Thanks and sorry about my english (I'm from Chile). Bye.

: Try specifying directory name in upper case as 'ADJUNTOS2'
: Let us know if it does not help.

I tried it, but the result is the same. The attach_id valued returned by the attach_file function, is -1. The Oracle directory name is 'ADJUNTO', without the '2', that was a test, I dropped it.

All objects are ownered by 'SYSTEM', including the Directory 'ADJUNTO'. I'll be waiting for more help, thank you.

Use SQL*Plus to run

SELECT system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'ADJUNTO')
FROM dual

This could give you more details on the error

After that please run DELETE FROM system.mail_attach to clean up the attachment table.

: I tried it, but the result is the same. The attach_id valued returned by the
: attach_file function, is -1. The Oracle directory name is 'ADJUNTO',
: without the '2', that was a test, I dropped it.

: All objects are ownered by 'SYSTEM', including the Directory 'ADJUNTO'. I'll
: be waiting for more help, thank you.

: Use SQL*Plus to run

: SELECT system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'ADJUNTO')
: FROM dual

: This could give you more details on the error

: After that please run DELETE FROM system.mail_attach to clean up the
: attachment table.

I did what you suggest me. These were the results:
SQL> SELECT system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'ADJUNTO')
2 FROM dual;
ERROR at line 1:
ORA-06571: Function ATTACH_FILE does not guarantee not to update database

SQL> delete from system.mail_attach;

0 rows deleted.

I see it, the function attach_file does not guarantee not to update database, but what does it mean? if I look at the code of that function, there is a 'select ... for update' statement. What will be the problem? is this the first time that you have notice of a problem with this? Thanks again for your time.

Have you installed the correct DB Mail interface functions?

There are 3 files provided with DB Mail

db_mail_install_7.sql for Oracle 7
db_mail_install_8.sql for Oracle 8.0.0 to 8.1.4
db_mail_install_8i.sql for Oracle 8.1.5 and later

The error you are getting could be a result of db_mail_install_8.sql used for Oracle 8i database.

: I did what you suggest me. These were the results: SQL> SELECT
: system.Attach_File(NULL, 1, 'TNSNAMES.ORA', 'ADJUNTO')
: 2 FROM dual;
: ERROR at line 1: ORA-06571: Function ATTACH_FILE does not guarantee not to
: update database

: SQL> delete from system.mail_attach;

: 0 rows deleted.

: I see it, the function attach_file does not guarantee not to update database,
: but what does it mean? if I look at the code of that function, there is a
: 'select ... for update' statement. What will be the problem? is this the
: first time that you have notice of a problem with this? Thanks again for
: your time.

: Have you installed the correct DB Mail interface functions?

: There are 3 files provided with DB Mail

: db_mail_install_7.sql for Oracle 7
: db_mail_install_8.sql for Oracle 8.0.0 to 8.1.4
: db_mail_install_8i.sql for Oracle 8.1.5 and later

: The error you are getting could be a result of db_mail_install_8.sql used for
: Oracle 8i database.

Well, we have Oracle version 8.0.6 for Windows NT, so I used the scripts in db_mail_install_8.sql to create DB Mail
interface functions.

Do you think that it is better to use ...install_7.sql in this case (Oracle 8.0.6), because it's easy to see that the script that I
used is the script indicated by SoftTree for versions 8.0.0 to 8.1.4. More thanks...