Home » SQL & PL/SQL » SQL & PL/SQL » use CLOB or BLOD for storing html generated text
use CLOB or BLOD for storing html generated text [message #264253] Sun, 02 September 2007 07:52 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

First of all, I did try to search in this forum, but cou;dn't find for similiar problem. More are on reading images data. I'm using varchar for storing the result that generated for mailing purpose. The result is a listed of records. After compiling and executing my block, error occur, after that only realise that the result generated was too long to assign into the my variable name mail_html varchar;

mail_html varchar(32767);
mail_html := mail_html ||'<font face=Arial, Helvetica, sans-serif size=2>To whom it may concern,</font><br><br>';
mail_html := mail_html ||'<font face=Arial, Helvetica, sans-serif size=2>Below is the listed of students who has registered.</font><br><br>';
mail_html := mail_html ||'<table width=% border=1 cellspacing=0 cellpadding=0 bordercolor=#666600>';
mail_html := mail_html ||'<tr bgcolor=#FFFF66>';
mail_html := mail_html ||'<td width=23% height=29>';
mail_html := mail_html ||'<div align=center><b><font face=Arial, Helvetica, sans-serif size=2>Student Name</font></b></div></td>';
mail_html := mail_html ||'<td width=26% height=29>';
mail_html := mail_html ||'<div align=center><b><font face=Arial, Helvetica, sans-serif size=2>Student Number</font></b></div></td>';
mail_html := mail_html ||'<td width=34% height=29>';
mail_html := mail_html ||'<div align=center><b><font face=Arial, Helvetica, sans-serif size=2>Course Name</font></b></div></td>';
mail_html := mail_html ||'<td width=17% height=29>';
mail_html := mail_html ||'<div align=center><b><font face=Arial, Helvetica, sans-serif size=2>Date</font></b></div></td></tr>'..........and continue with the result loop from the For Loop by using cursor

I have been tried to chang from mail_html varchar(32767);

to mail_html CLOB; or mail_html CLOB;

But still fail with error....as below:
ORA-06550: line 15, column 15:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 15, column 1:
and so on..

pls advise...........

ying

[Updated on: Sun, 02 September 2007 17:26]

Report message to a moderator

Re: use CLOB or BLOD for storing html generated text [message #264353 is a reply to message #264253] Mon, 03 September 2007 01:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
VARCHAR is not a recommended data type. Make it VARCHAR2. VARCHAR might change in the future.

But I don't see any problems with your CLOB. Here's my test:
SQL> set serverout on
SQL> declare
  2    v_test CLOB;
  3  begin
  4    v_test := 'Hello';
  5    v_test := v_test||' world!';
  6
  7    dbms_output.put_line(v_test);
  8  end;
  9  /
Hello world!

PL/SQL procedure successfully completed.
Try to identify the line that's causing the problem.

MHE
Re: use CLOB or BLOD for storing html generated text [message #264367 is a reply to message #264253] Mon, 03 September 2007 02:24 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi Maaher,

Thanks so much for your reply....
Well, I did try before with a very simple sample like what you have provided. I cut and paste your code, But have error:

ORA-06550: line 4, column 11:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
ORA-06550: line 5, column 11:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored

Same problem....we are using Oracle 8i.

Pls advise....reallt urgent.

Ying

[Updated on: Mon, 03 September 2007 02:26]

Report message to a moderator

Re: use CLOB or BLOD for storing html generated text [message #264381 is a reply to message #264367] Mon, 03 September 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 8i, you have to use dbms_lob package.

Regards
Michel

[Updated on: Mon, 03 September 2007 02:40]

Report message to a moderator

Re: use CLOB or BLOD for storing html generated text [message #264386 is a reply to message #264253] Mon, 03 September 2007 02:54 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I did try also before by removing the dbms_output, still the same, so it is not because of the dbms package.

Sad(

Pls advise...

Ying
Re: use CLOB or BLOD for storing html generated text [message #264392 is a reply to message #264386] Mon, 03 September 2007 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did try also before by removing the dbms_output

Which dbms_output? there is no dbms_output in your code.
Quote:
so it is not because of the dbms package.

I talked about dbms_LOB, did you try it?

Regards
Michel


Re: use CLOB or BLOD for storing html generated text [message #264395 is a reply to message #264253] Mon, 03 September 2007 03:20 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

declare
v_test CLOB;
begin
v_test := 'Hello';
v_test := v_test||' world!';

end;

I cut and paste your sample and remove the dbms_output.

pls advise.....

Ying
Re: use CLOB or BLOD for storing html generated text [message #264397 is a reply to message #264395] Mon, 03 September 2007 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Advise on what?
Use DBMS_LOB.
Please follow advice.

Regards
Michel
Re: use CLOB or BLOD for storing html generated text [message #264399 is a reply to message #264395] Mon, 03 September 2007 03:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Mon, 03 September 2007 10:19
Which dbms_output? there is no dbms_output in your code.

My bad, Michel. I posted a small code sample.

ying wrote on Mon, 03 September 2007 10:20
I cut and paste your sample and remove the dbms_output.

That won't work in Oracle 8i. You would use something like this:
set serverout on
declare
  v_test    CLOB;
  v_size    pls_integer := 5;
  v_pos     pls_integer := 1;
begin
  -- prepare the clob
  dbms_lob.createtemporary(v_test,TRUE);
  dbms_lob.open(v_test,dbms_lob.lob_readwrite);
  
  -- write to the clob
  dbms_lob.write(v_test,v_size, v_pos, 'Hello');
  v_pos := v_pos + v_size;
  v_size := LENGTH(' world!');
  dbms_lob.write(v_test,v_size, v_pos, ' world!');
  
  -- close the clob
  dbms_lob.close(v_test);
  
  -- use the clob elsewhere...
end;
/
WARNING: untested code!

MHE
Re: use CLOB or BLOD for storing html generated text [message #264414 is a reply to message #264253] Mon, 03 September 2007 04:25 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

THis is really new to me, never thought that the CLOB cannot be used like varchar by just assign the text to the variable.

Well, I lost, have no idea on how to apply to my case....
How do I output my mail_html message? I pass it as varchar2, my function for my email message is as below:

procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_cc in varchar2 default null,
p_bcc in varchar2 default null,
p_subject in varchar2 default null,
p_text in varchar2 default null,
p_html in varchar2 default null
)

Begin

others coding here.....
End;
/

can you please advise....

Ying
Re: use CLOB or BLOD for storing html generated text [message #264428 is a reply to message #264414] Mon, 03 September 2007 05:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
When you say 'output', what do you mean? Are you trying to send an email through the UTL_SMTP package?

MHE
Re: use CLOB or BLOD for storing html generated text [message #264593 is a reply to message #264253] Mon, 03 September 2007 21:43 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Yup, MHE, you are obsolutely correct, I'm using UTL_SMTP. So my concern is :

1.) Is there any limitation in size when sending via UTL_SMTP?
2.) As you can see that one of the argument of the html_email procedure is "p_html" which is used for storing the html formatted message. This "p_html" will receive the "message" that I assign to my mail_html:

mail_html := mail_html ||'<font face=Arial, Helvetica, sans-serif size=2>To whom it may concern,</font><br><br>';
mail_html := mail_html ||'<font face=Arial, Helvetica, sans-serif size=2>Below is the listed of students who has registered.</font><br><br>'....


Since we are using CLOB, just wonder how can I modify from the existing procedure as below:

procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_cc in varchar2 default null,
p_bcc in varchar2 default null,
p_subject in varchar2 default null,
p_text in varchar2 default null,
p_html in varchar2 default null - can this change to CLOB?
)


pls advise...

Ying
Re: use CLOB or BLOD for storing html generated text [message #264635 is a reply to message #264593] Tue, 04 September 2007 01:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ying wrote on Tue, 04 September 2007 04:43
pls advise...
Instead of copying code and fiddling with it, let me point you to asktom.oracle.com. I searched for 'UTL_SMTP' and 'CLOB' and I found these threads you might find interesting:

Sending e-mail! -- Oracle 8i specific response
Sending HTML using UTL_SMTP
How to send more than 32K message

MHE
Re: use CLOB or BLOD for storing html generated text [message #265238 is a reply to message #264253] Wed, 05 September 2007 21:25 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Thank you so much...now be able to send my html message by using CLOB.

Thanks again!
Previous Topic: Please help in refining/modifying the sql.
Next Topic: Droping subpartitions of a partition
Goto Forum:
  


Current Time: Thu Dec 08 14:40:08 CST 2016

Total time taken to generate the page: 0.08709 seconds