SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Error attach internal BLOB to message.

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Error attach internal BLOB to message.
Author Message
Vickie Nguyen



Joined: 03 May 2001
Posts: 5

Post Error attach internal BLOB to message. Reply with quote

Hi,

I'm trying to attach an internal BLOB to an email message
by running the following sql statements:

create table vtn_blob_table
(blob_index number(8),
blob_col BLOB,
PRIMARY KEY (blob_index)
using index tablespace indexspace)
tablespace wrmsdata
pctfree 10
pctused 80
storage (initial 256k next 128k)
/

insert into vtn_blob_table
values (1, utl_raw.cast_to_raw('test BLOB'));
commit;

DECLARE

attach_id INTEGER;

ret_code INTEGER;

ablob BLOB;
BEGIN

SELECT blob_col

INTO ablob

FROM vtn_blob_table

WHERE blob_index = 1;

attach_id := Attach_Data(NULL, 1, 'testablob', ablob);

if (attach_id < 1) then

ret_code := send_mail('vnguyen@ocwd.com',

'Attach_id < 1',

'DB Mail: Attach_id < 1',

'vnguyen@ocwd.com');

else

ret_code := send_mail('vnguyen@ocwd.com',

'Test message with attachments',

'This message has one attachment.',

NULL,

NULL,

NULL,

attach_id) ;

end if;

END;
/

and what I have received was the 'Attach_id < 1' message
without the attachment. I know that I'm having problem
with the attach_data function. Please help!!!

Thank you,
Vickie

Thu May 03, 2001 7:39 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Re: Error attach internal BLOB to message. Reply with quote

Check if a copy of your blob was inserted in to system.attach_data table. Also Check the current value for system.mail_attach_seq sequence

This will give an idea where the exeption has occured

: Hi,

: I'm trying to attach an internal BLOB to an email message
: by running the following sql statements: create table vtn_blob_table
: (blob_index number(8),
: blob_col BLOB,
: PRIMARY KEY (blob_index)
: using index tablespace indexspace)
: tablespace wrmsdata
: pctfree 10
: pctused 80
: storage (initial 256k next 128k)
: /

: insert into vtn_blob_table
: values (1, utl_raw.cast_to_raw('test BLOB'));
: commit;

: DECLARE

: attach_id INTEGER;

: ret_code INTEGER;

: ablob BLOB;
: BEGIN

: SELECT blob_col

: INTO ablob

: FROM vtn_blob_table

: WHERE blob_index = 1;

: attach_id := Attach_Data(NULL, 1, 'testablob', ablob);

: if (attach_id < 1) then

: ret_code := send_mail('vnguyen@ocwd.com',

: 'Attach_id < 1',

: 'DB Mail: Attach_id < 1',

: 'vnguyen@ocwd.com');

: else

: ret_code := send_mail('vnguyen@ocwd.com',

: 'Test message with attachments',

: 'This message has one attachment.',

: NULL,

: NULL,

: NULL,

: attach_id) ;

: end if;

: END;
: /

: and what I have received was the 'Attach_id < 1' message
: without the attachment. I know that I'm having problem
: with the attach_data function. Please help!!!

: Thank you,
: Vickie

Thu May 03, 2001 10:20 pm View user's profile Send private message
Vickie Nguyen



Joined: 03 May 2001
Posts: 5

Post Re: Error attach internal BLOB to message. Reply with quote

: Check if a copy of your blob was inserted in to system.attach_data table.
: Also Check the current value for system.mail_attach_seq sequence

: This will give an idea where the exeption has occured

I have done what you suggested and no record was found in system.mail_attach table. I guess this table got clean up after every run? Because I also had executed the attach_id function to attach the external BLOB files to the messages many times without any problem, and I don't see any of those record in system.attach_mail table. Anyway, I'm still having problem using the attach_data function. Please help!!!

SQL> select system.mail_attach_seq.currval
2 from dual;

CURRVAL
----------

181

SQL> select blob_index, dbms_lob.getlength(blob_col)
2 from vtn_blob_table
3 /

BLOB_INDEX DBMS_LOB.GETLENGTH(BLOB_COL)
---------- ----------------------------

1 9

SQL> select count(*)
2 from system.mail_attach;

COUNT(*)
----------

0

Fri May 04, 2001 11:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Re: Error attach internal BLOB to message. Reply with quote

Only attachments for already processed messages are removed from the SYSTEM.MAIL_ATTACH table. It sounds as if your attachment was not saved in that table. So that you have either no INSERT/UPDATE permissions or insufficient space in that table or your blob was empty or invalid causing DBMS_LOB.COPY function to fail when it was called internally within ATTACH_DATA function. Please check these possibilities and let us know what you found out.

: I have done what you suggested and no record was found in system.mail_attach
: table. I guess this table got clean up after every run? Because I also had
: executed the attach_id function to attach the external BLOB files to the
: messages many times without any problem, and I don't see any of those
: record in system.attach_mail table. Anyway, I'm still having problem using
: the attach_data function. Please help!!!

: SQL> select system.mail_attach_seq.currval
: 2 from dual;

: CURRVAL
: ----------

: 181

: SQL> select blob_index, dbms_lob.getlength(blob_col)
: 2 from vtn_blob_table
: 3 /

: BLOB_INDEX DBMS_LOB.GETLENGTH(BLOB_COL)
: ---------- ----------------------------

: 1 9

: SQL> select count(*)
: 2 from system.mail_attach;

: COUNT(*)
: ----------

: 0

Fri May 04, 2001 12:10 pm View user's profile Send private message
Vickie Nguyen



Joined: 03 May 2001
Posts: 5

Post Re: Error attach internal BLOB to message. Reply with quote

: Only attachments for already processed messages are removed from the
: SYSTEM.MAIL_ATTACH table. It sounds as if your attachment was not saved in
: that table. So that you have either no INSERT/UPDATE permissions or
: insufficient space in that table or your blob was empty or invalid causing
: DBMS_LOB.COPY function to fail when it was called internally within
: ATTACH_DATA function. Please check these possibilities and let us know
: what you found out.

You was right! It has something to do with the permission.
It did work when I log-on as SYSTEM user but not the owner of the BLOB_TABLE.
I re-grant the INSERT/UPDATE permssions on system.mail_attach table to PUBLIC and it still does not work any other users beside SYSTEM.

Fri May 04, 2001 2:31 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Re: Error attach internal BLOB to message. Reply with quote

Then you are also don't have permissions for the SYSTEM.MAIL_ATTACH_SEQ sequence and should also grant SELECT for system.mail_attach table

: You was right! It has something to do with the permission.
: It did work when I log-on as SYSTEM user but not the owner of the BLOB_TABLE.
: I re-grant the INSERT/UPDATE permssions on system.mail_attach table to PUBLIC
: and it still does not work any other users beside SYSTEM.

Fri May 04, 2001 4:32 pm View user's profile Send private message
Vickie Nguyen



Joined: 03 May 2001
Posts: 5

Post Re: Error attach internal BLOB to message. Reply with quote

: Then you are also don't have permissions for the SYSTEM.MAIL_ATTACH_SEQ
: sequence and should also grant SELECT for system.mail_attach table

I grant all on both mail_attach_seq and mail_attach table to public and it did not help.

Fri May 04, 2001 6:22 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Re: Error attach internal BLOB to message. Reply with quote

Does it work for the SYSTEM user?

: I grant all on both mail_attach_seq and mail_attach table to public and it
: did not help.

Fri May 04, 2001 9:39 pm View user's profile Send private message
Vickie Nguyen



Joined: 03 May 2001
Posts: 5

Post Re: Error attach internal BLOB to message. Reply with quote

: Does it work for the SYSTEM user?

Yes, it does work for SYSTEM user.

Mon May 07, 2001 5:44 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Re: Error attach internal BLOB to message. Reply with quote

Try inserting something manually into SYSTEM.MAIL_ATTACH as a non-SYSTEM user.
You should get an error message which will tell you what's wrong with the insert.

: Yes, it does work for SYSTEM user.

Mon May 07, 2001 5:57 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.