Home » SQL & PL/SQL » SQL & PL/SQL » Is there any way to store the value more then 32760 in a variable. (oracle 9i)
Is there any way to store the value more then 32760 in a variable. [message #357346] Wed, 05 November 2008 00:31 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
right now i m using long variable to store the string.Is there any way to store the value more then 32760 in a variable. i tried clob but that also doesnt let me to store more then 32786.
Re: Is there any way to store the value more then 32760 in a variable. [message #357350 is a reply to message #357346] Wed, 05 November 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure?
SQL> declare t clob;
  2  begin
  3    for i in 1..100 loop t := t || lpad(' ',1000); end loop;
  4    dbms_output.put_line(length(t));
  5  end;
  6  /
100000

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 9.2.0.4.0

What is your version (4 decimals)?

Regards
Michel

[Updated on: Wed, 05 November 2008 00:41]

Report message to a moderator

Re: Is there any way to store the value more then 32760 in a variable. [message #357352 is a reply to message #357346] Wed, 05 November 2008 00:40 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CLOB can store up to 4 gigabytes of data (according to Oracle 9i documentation) (while LONG stores up to 2 gigabytes); so, where did you go wrong?

By the way, LONG will probably be desupported in the future, so you'd rather stick to CLOB.
Re: Is there any way to store the value more then 32760 in a variable. [message #357358 is a reply to message #357350] Wed, 05 November 2008 00:53 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Ya i m also printing the error with legnth. It give like

ORA-06502: PL/SQL: numeric or value error:32786

version is 9.2.0.6.0.
Re: Is there any way to store the value more then 32760 in a variable. [message #357364 is a reply to message #357358] Wed, 05 November 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*PLus and COPY AND PASTE what you did as I posted it.

Regards
Michel
Re: Is there any way to store the value more then 32760 in a variable. [message #357370 is a reply to message #357364] Wed, 05 November 2008 01:31 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
here is the code i wrote in pl/sql i couldnt figure out the problem. I exectued your code in sql plus. it is working fine.

CREATE OR REPLACE PROCEDURE test_email_send IS

THIS_EMAILMESSAGE clob;
THIS_EMAILSUBJECT VARCHAR2 (200)
:= 'Email Alert : HAND BIOMETRY :SEV 2 :Rejecton level reach to maximum level';
L_MAICON UTL_SMTP.CONNECTION;
checklength number;
countrec number;
CURSOR C_REJ_LEVEL (v_Max_Rej_LVL number) IS
select e.emp_staffno,e.emp_fullname,e.internal_dept_code,e.internal_loc_code,
t.tmp_rej_lvl,t.tmp_desc,t.tmp_auth_lvl,t.updt_dt from table1 e, table2 t
where e.emp_serial_no=t.emp_serial_no
and e.internal_dept_code like 'ECG%'
and t.tmp_rej_lvl>=v_Max_Rej_LVL
and t.tmp_auth_lvl <> 5
--and rownum<50
order by internal_dept_code,tmp_rej_lvl desc;

P_Rej_Level NUMBER(5) := DFUNC_GET_PARA('MAXREJLVL');
EKAS_CONTACTS varchar2(4000) := DFUNC_GET_PARA('EKASCONTACTS');
-- DNATA_CONTACTS varchar2(2000) := DFUNC_GET_PARA('DNATA');

BEGIN

countrec:=0;
THIS_EMAILMESSAGE :=
'<HTML>
<BODY>
<DIV><SPAN CLASS=316434313-07112005>
<TABLE
style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: windowtext 0pt
solid; FONT-SIZE: 9pt; BORDER-LEFT: medium NONE; BORDER-BOTTOM:
medium NONE; FONT-FAMILY:
''Verdana''; BORDER-COLLAPSE: collapse"
cellSpacing=0 cellPadding=2 border=1>
<TBODY>
<TR bgColor=#ffcc99>
<TD width=170><B>SUMMARY </B>;</TD>
<TD colSpan=4> '
|| THIS_EMAILSUBJECT
|| ' ; </TD></TR>
<TR bgColor=#edf7e7>
<TD colSpan=1><B>Staff No</B></TD>
<TD colSpan=1><B>Name</B></TD>
<TD colSpan=1><B>Cost Code</B></TD>
<TD colSpan=1><B>Location</B></TD>
<TD colSpan=1><B>Last Updated Date </B></TD>
</TR>';
FOR C1 IN C_REJ_LEVEL(P_Rej_Level)
LOOP
checklength:=length (THIS_EMAILMESSAGE);
countrec:=countrec+1;
THIS_EMAILMESSAGE := THIS_EMAILMESSAGE ||
'<TR>
<TD colSpan=1>' ||c1.emp_staffno ||'</TD>
<TD colSpan=1>' ||c1.emp_fullname ||'</TD>
<TD colSpan=1>' ||c1.internal_dept_code ||'</TD>
<TD colSpan=1>' ||c1.internal_loc_code ||'</TD>
<TD colSpan=1>' ||c1.updt_dt|| '</TD>
</TR>';
--dbms_output.put_line('I am here');
end loop;
-- dbms_output.put_line('I am here1');
THIS_EMAILMESSAGE := THIS_EMAILMESSAGE ||
'<TR bgColor=#edf7e7>
<TD colSpan=1></TD>
<TD colSpan=1></TD>
</TR>
</TBODY></TABLE></SPAN></DIV></BODY></HTML>';
EXCEPTION
WHEN OTHERS THEN
DPROC_ERROR_LOG ('DPROC_REJ_LEVEL_EMAIL', SQLERRM||checklength||':'||countrec, 'ALTAR');
END;
Re: Is there any way to store the value more then 32760 in a variable. [message #357372 is a reply to message #357370] Wed, 05 November 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Repost formatted and with the usage you made of this procedure.
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

We want to see when you compile and when you get the error.

Regards
Michel
Re: Is there any way to store the value more then 32760 in a variable. [message #357373 is a reply to message #357346] Wed, 05 November 2008 01:37 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
here is the code i wrote in pl/sql i couldnt figure out the problem. I exectued your code in sql plus. it is working fine.

CREATE OR REPLACE PROCEDURE test_email_send IS

THIS_EMAILMESSAGE clob;
THIS_EMAILSUBJECT VARCHAR2 (200)
:= 'Email Alert : HAND BIOMETRY :SEV 2 :Rejecton level reach to maximum level';
L_MAICON UTL_SMTP.CONNECTION;
checklength number;
countrec number;
CURSOR C_REJ_LEVEL (v_Max_Rej_LVL number) IS
select e.emp_staffno,e.emp_fullname,e.internal_dept_code,e.internal_loc_code,
t.tmp_rej_lvl,t.tmp_desc,t.tmp_auth_lvl,t.updt_dt from table1 e, table2 t
where e.emp_serial_no=t.emp_serial_no
and e.internal_dept_code like 'ECG%'
and t.tmp_rej_lvl>=v_Max_Rej_LVL
and t.tmp_auth_lvl <> 5
--and rownum<50
order by internal_dept_code,tmp_rej_lvl desc;

P_Rej_Level NUMBER(5) := DFUNC_GET_PARA('MAXREJLVL');
EKAS_CONTACTS varchar2(4000) := DFUNC_GET_PARA('EKASCONTACTS');
-- DNATA_CONTACTS varchar2(2000) := DFUNC_GET_PARA('DNATA');

BEGIN

countrec:=0;
THIS_EMAILMESSAGE :=
'<HTML>
<BODY>
<DIV><SPAN CLASS=316434313-07112005>
<TABLE
style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: windowtext 0pt
solid; FONT-SIZE: 9pt; BORDER-LEFT: medium NONE; BORDER-BOTTOM:
medium NONE; FONT-FAMILY:
''Verdana''; BORDER-COLLAPSE: collapse"
cellSpacing=0 cellPadding=2 border=1>
<TBODY>
<TR bgColor=#ffcc99>
<TD width=170><B>SUMMARY </B>;</TD>
<TD colSpan=4> '
|| THIS_EMAILSUBJECT
|| ' ; </TD></TR>
<TR bgColor=#edf7e7>
<TD colSpan=1><B>Staff No</B></TD>
<TD colSpan=1><B>Name</B></TD>
<TD colSpan=1><B>Cost Code</B></TD>
<TD colSpan=1><B>Location</B></TD>
<TD colSpan=1><B>Last Updated Date </B></TD>
</TR>';
FOR C1 IN C_REJ_LEVEL(P_Rej_Level)
LOOP
checklength:=length (THIS_EMAILMESSAGE);
countrec:=countrec+1;
THIS_EMAILMESSAGE := THIS_EMAILMESSAGE || 
'<TR>
<TD colSpan=1>' ||c1.emp_staffno ||'</TD>
<TD colSpan=1>' ||c1.emp_fullname ||'</TD>
<TD colSpan=1>' ||c1.internal_dept_code ||'</TD>
<TD colSpan=1>' ||c1.internal_loc_code ||'</TD>
<TD colSpan=1>' ||c1.updt_dt|| '</TD>
</TR>';
--dbms_output.put_line('I am here'); 
end loop; 
-- dbms_output.put_line('I am here1'); 
THIS_EMAILMESSAGE := THIS_EMAILMESSAGE || 
'<TR bgColor=#edf7e7>
<TD colSpan=1></TD>
<TD colSpan=1></TD>
</TR>
</TBODY></TABLE></SPAN></DIV></BODY></HTML>'; 
EXCEPTION
WHEN OTHERS THEN
DPROC_ERROR_LOG ('DPROC_REJ_LEVEL_EMAIL', SQLERRM||checklength||':'||countrec, 'ALTAR');
END;




there is no compliation error. It gives exception when values reaches to more then 32786. here is the out put in the error log.
ORA-06502: PL/SQL: numeric or value error:32786.
I want this text to be constructed and send it to email.
Re: Is there any way to store the value more then 32760 in a variable. [message #357374 is a reply to message #357373] Wed, 05 November 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again POST WHAT YOU DID WHEN YOU GOT THE ERROR.

Regards
Michel
Re: Is there any way to store the value more then 32760 in a variable. [message #357378 is a reply to message #357346] Wed, 05 November 2008 01:57 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
EXCEPTION
  WHEN OTHERS THEN
    DPROC_ERROR_LOG ('DPROC_REJ_LEVEL_EMAIL', SQLERRM||checklength||':'||countrec, 'ALTAR');
END;

Catching exception without re-raising them is a bug (if you are interested in successful execution of the code; but if you would not mind it, you would probably not post here).
Moreover, it hides the line number where the error occurred. Remove it (at least for debugging purpose).

Also, formatting is not only using code tags; if not indented, the code is hardly readable too.
Re: Is there any way to store the value more then 32760 in a variable. [message #357379 is a reply to message #357346] Wed, 05 November 2008 02:01 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Michel,

when i executed this procedure it logs the error in a table. i m still trying to figure out why this error is coming. i am not sure what else you need.
Re: Is there any way to store the value more then 32760 in a variable. [message #357383 is a reply to message #357379] Wed, 05 November 2008 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that what you're posting is actualy what you got on screen, you're already past the limit on a varchar2, which is 32767 bytes - you've got a posted length of 32786

What we need is to see the line and procedure at which this error occurred, and that is information that your when others trigger hides.
Please remove the When Others trigger, run your code from SQL*PLus and copy/paste the output from DSql*Plus here so we can see what actually happened.
Re: Is there any way to store the value more then 32760 in a variable. [message #357395 is a reply to message #357346] Wed, 05 November 2008 03:32 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
SQL> declare
  2    
  3      THIS_EMAILMESSAGE             clob;
  4       THIS_EMAILSUBJECT             VARCHAR2 (200)
  5                        := 'Email Alert : HAND BIOMETRY :SEV 2 :Rejecton level reach to maximum l
evel';
  6       L_MAICON                      UTL_SMTP.CONNECTION;
  7      checklength number;
  8      countrec number;
  9       
 10       CURSOR C_REJ_LEVEL (v_Max_Rej_LVL number) IS
 11    select e.emp_staffno,e.emp_fullname,e.internal_dept_code,e.internal_loc_code,
 12    t.tmp_rej_lvl,t.tmp_desc,t.tmp_auth_lvl,t.updt_dt from tars_emp_mst e, tars_emp_template t
 13    where e.emp_serial_no=t.emp_serial_no
 14    and e.internal_dept_code like 'ECG%'
 15    and t.tmp_rej_lvl>=v_Max_Rej_LVL
 16    and t.tmp_auth_lvl <> 5
 17    --and rownum<50
 18    order by internal_dept_code,tmp_rej_lvl desc;
 19    
 20             P_Rej_Level NUMBER(5) := DFUNC_GET_PARA('MAXREJLVL');
 21             EKAS_CONTACTS varchar2(4000) := DFUNC_GET_PARA('EKASCONTACTS');
 22            --  DNATA_CONTACTS varchar2(2000) := DFUNC_GET_PARA('DNATA');
 23       
 24       BEGIN
 25            
 26    countrec:=0;
 27            THIS_EMAILMESSAGE :=
 28                       '<HTML>
 29                                       <BODY>
 30                                      <DIV><SPAN CLASS=316434313-07112005>
 31                                     <TABLE
 32                                        style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: window
text 0pt
 33                                        solid; FONT-SIZE: 9pt; BORDER-LEFT: medium NONE; BORDER-B
OTTOM:
 34                                        medium NONE; FONT-FAMILY:
 35                                        ''Verdana''; BORDER-COLLAPSE: collapse"
 36                                        cellSpacing=0 cellPadding=2 border=1>
 37                                       <TBODY>
 38                                       <TR bgColor=#ffcc99>
 39                                       <TD width=170><B>SUMMARY </B>;</TD>
 40                                       <TD colSpan=4> '
 41                       || THIS_EMAILSUBJECT
 42                       || ' ; </TD></TR>
 43                       <TR bgColor=#edf7e7>
 44                                           <TD colSpan=1><B>Staff No</B></TD>
 45                                           <TD colSpan=1><B>Name</B></TD>
 46                                           <TD colSpan=1><B>Cost Code</B></TD>
 47                                           <TD colSpan=1><B>Location</B></TD>
 48                                       <TD colSpan=1><B>Last Updated Date </B></TD>
 49                                       </TR>';
 50             FOR C1 IN C_REJ_LEVEL(P_Rej_Level)
 51             LOOP
 52             checklength:=length (THIS_EMAILMESSAGE);
 53             countrec:=countrec+1;
 54              dbms_output.put_line(length(THIS_EMAILMESSAGE));
 55               THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                       
         
 56                                       '<TR>
 57                                       <TD colSpan=1>' ||c1.emp_staffno ||'</TD>
 58                                       <TD colSpan=1>' ||c1.emp_fullname ||'</TD>
 59                                       <TD colSpan=1>' ||c1.internal_dept_code ||'</TD>
 60                                       <TD colSpan=1>' ||c1.internal_loc_code ||'</TD>
 61                                       <TD colSpan=1>' ||c1.updt_dt|| '</TD>
 62                                       </TR>'; 
 63               end loop;        
 64                 dbms_output.put_line(length(THIS_EMAILMESSAGE));              
 65                 THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                     
           
 66                                       '<TR bgColor=#edf7e7>
 67                                       <TD colSpan=1></TD>
 68                                       <TD colSpan=1></TD>
 69          </TR>
 70                                       </TBODY></TABLE></SPAN></DIV></BODY></HTML>';             
   
 71    dbms_output.put_line(length(THIS_EMAILMESSAGE));
 72    END;
 73  /


PL/SQL procedure successfully completed


i dont know at sql plus it just showing successfull.
Re: Is there any way to store the value more then 32760 in a variable. [message #357401 is a reply to message #357346] Wed, 05 November 2008 03:53 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
but in plsql developer i m still getting error. Even i exculded the reocord where error was coming. but at the very next record it gives the same error. it seems to be legth issue.
Re: Is there any way to store the value more then 32760 in a variable. [message #357405 is a reply to message #357401] Wed, 05 November 2008 04:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you doing exactly the same thing in Plsql Developer that you are doing in SQL*Plus?
If you're calling the procedure you created, can you try calling that from SQL*Plus (nce you've removed the error handler)
Re: Is there any way to store the value more then 32760 in a variable. [message #357406 is a reply to message #357346] Wed, 05 November 2008 04:20 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
yea i am running exactly the the same code. and not calling from anywhere.

At sql plus it gives me sucessfull msg but
can you tell me why its not printing the output at sqlplus?
Re: Is there any way to store the value more then 32760 in a variable. [message #357408 is a reply to message #357406] Wed, 05 November 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
yea i am running exactly the the same code.

It seems from your posts that you don't run exactly the same code. It seems that before you're calling a procedure and not executing this PL/SQL block.

Quote:
can you tell me why its not printing the output at sqlplus?

Because you didn't execute "set serveroutput on".

Regards
Michel


Re: Is there any way to store the value more then 32760 in a variable. [message #357409 is a reply to message #357406] Wed, 05 November 2008 04:29 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
can you tell me why its not printing the output at sqlplus?

Did you SET SERVEROUTPUT ON?
Re: Is there any way to store the value more then 32760 in a variable. [message #357410 is a reply to message #357346] Wed, 05 November 2008 04:43 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
michel in the begining i was calling the procedure but later on i changed it and running exactly the same.

and after "SET SERVEROUTPUT ON" it is now printing values 1370 and
1599.
Re: Is there any way to store the value more then 32760 in a variable. [message #357411 is a reply to message #357410] Wed, 05 November 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
in the begining i was calling the procedure but later on i changed it and running exactly the same.

And did you ever get the error with the PL/SQL block?

Regards
Michel
Re: Is there any way to store the value more then 32760 in a variable. [message #357413 is a reply to message #357346] Wed, 05 November 2008 04:53 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
In sql plus i m not getting any error but running at plsql developer it gives me ORA-06502: PL/SQL: numeric or value error.
just see the attached print screen. thats what i get when i run in plsql developer
  • Attachment: error.JPG
    (Size: 58.90KB, Downloaded 90 times)
icon9.gif  Re: Is there any way to store the value more then 32760 in a variable. [message #357417 is a reply to message #357346] Wed, 05 November 2008 05:09 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
and also if you run this procedure in plsql developer for less records (like 50 records) then it works fine.
Re: Is there any way to store the value more then 32760 in a variable. [message #357420 is a reply to message #357417] Wed, 05 November 2008 05:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Are you sure that the exception is in this procedure
2) Are yuo sure you've posted us the correct code for the procedure - the code you've posted does nothing with the CLOB it builds up - doesnt' return it, doesn't use it.
Re: Is there any way to store the value more then 32760 in a variable. [message #357430 is a reply to message #357346] Wed, 05 November 2008 05:33 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
yea i m sure that there is some exception.
secondly i am constructing this clob to send in email. but this code should work even i build clob for just to check the length? isnt it? anyway i just wrote a code for email also. let me send it to you.

declare
  
    THIS_EMAILMESSAGE             clob;
     THIS_EMAILSUBJECT             VARCHAR2 (200)
                      := 'Email Alert : HAND BIOMETRY :SEV 2 :Rejecton level reach to maximum level';
     L_MAICON                      UTL_SMTP.CONNECTION;
    checklength number;
    countrec number;
     
     CURSOR C_REJ_LEVEL (v_Max_Rej_LVL number) IS
  select e.emp_staffno,e.emp_fullname,e.internal_dept_code,e.internal_loc_code,
  t.tmp_rej_lvl,t.tmp_desc,t.tmp_auth_lvl,t.updt_dt from tars_emp_mst e, tars_emp_template t
  where e.emp_serial_no=t.emp_serial_no
  and e.internal_dept_code like 'ECG%'
  and t.tmp_rej_lvl>=v_Max_Rej_LVL
  and t.tmp_auth_lvl <> 5
  --and rownum<50
  order by internal_dept_code,tmp_rej_lvl desc;
  
           P_Rej_Level NUMBER(5) := DFUNC_GET_PARA('MAXREJLVL');
           EKAS_CONTACTS varchar2(4000) := DFUNC_GET_PARA('EKASCONTACTS');
     
     BEGIN
          
  countrec:=0;
          THIS_EMAILMESSAGE :=
                     '<HTML>
                                     <BODY>
                                    <DIV><SPAN CLASS=316434313-07112005>
                                   <TABLE
                                      style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: windowtext 0pt
                                      solid; FONT-SIZE: 9pt; BORDER-LEFT: medium NONE; BORDER-BOTTOM:
                                      medium NONE; FONT-FAMILY:
                                      ''Verdana''; BORDER-COLLAPSE: collapse"
                                      cellSpacing=0 cellPadding=2 border=1>
                                     <TBODY>
                                     <TR bgColor=#ffcc99>
                                     <TD width=170><B>SUMMARY </B>;</TD>
                                     <TD colSpan=4> '
                     || THIS_EMAILSUBJECT
                     || ' ; </TD></TR>
                     <TR bgColor=#edf7e7>
                                         <TD colSpan=1><B>Staff No</B></TD>
                                         <TD colSpan=1><B>Name</B></TD>
                                         <TD colSpan=1><B>Cost Code</B></TD>
                                         <TD colSpan=1><B>Location</B></TD>
                                     <TD colSpan=1><B>Last Updated Date </B></TD>
                                     </TR>';
           FOR C1 IN C_REJ_LEVEL(P_Rej_Level)
           LOOP
           checklength:=length (THIS_EMAILMESSAGE);
           countrec:=countrec+1;
            dbms_output.put_line(length(THIS_EMAILMESSAGE));
             THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                                
                                     '<TR>
                                     <TD colSpan=1>' ||c1.emp_staffno ||'</TD>
                                     <TD colSpan=1>' ||c1.emp_fullname ||'</TD>
                                     <TD colSpan=1>' ||c1.internal_dept_code ||'</TD>
                                     <TD colSpan=1>' ||c1.internal_loc_code ||'</TD>
                                     <TD colSpan=1>' ||c1.updt_dt|| '</TD>
                                     </TR>'; 
             end loop;        
               dbms_output.put_line(length(THIS_EMAILMESSAGE));              
               THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                                
                                     '<TR bgColor=#edf7e7>
                                     <TD colSpan=1></TD>
                                     <TD colSpan=1></TD>
                                     </TR>
                                     </TBODY></TABLE></SPAN></DIV></BODY></HTML>';                
  dbms_output.put_line(length(THIS_EMAILMESSAGE));
  
      BEGIN
                     L_MAICON := UTL_SMTP.OPEN_CONNECTION ('10.20.84.20');
                     UTL_SMTP.HELO (L_MAICON, '10.20.84.20');
                     UTL_SMTP.MAIL (L_MAICON, 'S727204@emirates.com');
                     UTL_SMTP.RCPT (L_MAICON, EKAS_CONTACTS);
                     UTL_SMTP.OPEN_DATA (L_MAICON);
                     UTL_SMTP.WRITE_DATA (L_MAICON,
                                           'From' || ':' || 'HAND BIOMETRY_SUPPORT'
                                           || UTL_TCP.CRLF);
                     UTL_SMTP.WRITE_DATA (L_MAICON,
                                           'To' || ':' || EKAS_CONTACTS
                                           || UTL_TCP.CRLF);
                     UTL_SMTP.WRITE_DATA (L_MAICON,
                                           'Subject' || ':'
                                           || 'Email Alert : HAND BIOMETRY : SEV 2 : Rejection level reach to maximum level'
                                           || UTL_TCP.CRLF);
                     UTL_SMTP.WRITE_DATA (L_MAICON,
                                           'Content-Transfer-Encoding:'
                                           || '8 Bit' || UTL_TCP.CRLF
                                           || 'Content-Type:'
                                           || 'text/html; CHARSET=UTF-8'
                                           || UTL_TCP.CRLF);
                     UTL_SMTP.WRITE_DATA (L_MAICON,
                                           UTL_TCP.CRLF || THIS_EMAILMESSAGE);
                     UTL_SMTP.CLOSE_DATA (L_MAICON);
                     UTL_SMTP.QUIT (L_MAICON);
		  EXCEPTION
                  WHEN OTHERS THEN
                      DPROC_ERROR_LOG ('DPROC_REJ_LEVEL_EMAIL', SQLERRM||' Error in Sending Mail', 'ALTAR');
                  END;
  END;


there are only 480 records returned by the cursor. and i think its not too much. isnt it? this code works fine if i restict the number on rows in cursor to 50 records.
Re: Is there any way to store the value more then 32760 in a variable. [message #357433 is a reply to message #357430] Wed, 05 November 2008 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I don't think you're telling us everything.

I do not believe that the same piece of code, run against the same database, connected as the same user will run fine under SQL*PLus, but will give an Oracle numeric or value error when run under Pl/Sql Developer.
The main reson for this disbelief is that server side execution of code is not dependant on the client used to execute it.

what is the code at line 55 of the pl/sql block tha tyou executed in Pl/Sql developer and sent us an image of the error from?
Re: Is there any way to store the value more then 32760 in a variable. [message #357436 is a reply to message #357430] Wed, 05 November 2008 05:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My best guess will be the exception you are getting because of dbms_output. I believe you are exceeding 32767 characters in a line and you are trying to output it using dbms_output. It has a limitation of 32767 characters in a line. Also I think by default serveroutput is enabled in pl/sql developer and sql*plus you have to explicitly enable it either in your current session and in the glogin.sql

Check this link for more information.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#BABEHIEG

Probably to verify what I have said try to insert the value in a table instead of using dbms_output or alternatively append a line feed after every single line and see if that works.

Hope this helps.

Regards

Raj

[Updated on: Wed, 05 November 2008 05:49]

Report message to a moderator

Re: Is there any way to store the value more then 32760 in a variable. [message #357449 is a reply to message #357436] Wed, 05 November 2008 06:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've got it.

I had't noticed that the last block of code you posted had calls to UTL_SMTP in it.
specifically, it's got this line:
UTL_SMTP.WRITE_DATA (L_MAICON,
                                           UTL_TCP.CRLF || THIS_EMAILMESSAGE);


If yuo look at the Docs you'll notice that Write_data takes a Varchar2 as a parameter, not a Clob.
That's why you're getting the error, and why the different code (missing this call) works in Sql*Plus.

You'll need to loop and write data from the clob in 32k chunks using either Substr and Instr, or the related functions from DBMS_LOB


Re: Is there any way to store the value more then 32760 in a variable. [message #357454 is a reply to message #357449] Wed, 05 November 2008 06:35 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Hi Raj,

It does not work even i remove all the dbms_output from the code.

JRowbottom:

this email code i just wrote. if i remove this code it should work fine and constuct a clob. isnt it? but it does not construnct it and gives error after reaching the value of 32786.(ORA-06502: PL/SQL: numeric or value error:32786).

and also i think this code also doesnt seems to be work fine in sql pllus though it is giving successful msg.

Re: Is there any way to store the value more then 32760 in a variable. [message #357455 is a reply to message #357454] Wed, 05 November 2008 06:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Remove the when others exception block. Run the code in sql*plus . Copy and paste the query and the error you are getting with the line numbers. Don't forget to apply the code formatting around it. Could you please do this ? By doing it this way atleast we know which line the error has happened .

Regards

Ra
Re: Is there any way to store the value more then 32760 in a variable. [message #357457 is a reply to message #357455] Wed, 05 November 2008 07:00 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
I have done it many times. Code without email part runs and give no error in sql plus. it says susscessfuly completed. i have run this code in debug mode in plsql developer and found that error is coming at this line and when value reaces to 32786 Sad

THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                                
                                       '<TR>
                                       <TD COLSPAN=1>' ||C1.EMP_STAFFNO ||'</TD>
                                       <TD COLSPAN=1>' ||C1.EMP_FULLNAME ||'</TD>
                                       <TD COLSPAN=1>' ||C1.INTERNAL_DEPT_CODE ||'</TD>
                                       <TD COLSPAN=1>' ||C1.INTERNAL_LOC_CODE ||'</TD>
                                       <TD COLSPAN=1>' ||C1.UPDT_DT|| '</TD>
                                       </TR>'; 


Re: Is there any way to store the value more then 32760 in a variable. [message #357465 is a reply to message #357457] Wed, 05 November 2008 07:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about trying the following code segment and see if it works at your end.
declare
  
    THIS_EMAILMESSAGE             clob;
     THIS_EMAILSUBJECT             VARCHAR2 (200)  := 'Email Alert : HAND BIOMETRY :SEV 2 :Rejecton level reach to maximum level';
   checklength number;
    countrec number;
     
     CURSOR C_REJ_LEVEL (v_Max_Rej_LVL number) IS
     select table_name, tablespace_name from user_tables ;
  
  BEGIN
          
  countrec:=0;
          THIS_EMAILMESSAGE :=
                     '<HTML>
                                     <BODY>
                                    <DIV><SPAN CLASS=316434313-07112005>
                                   <TABLE
                                      style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: windowtext 0pt
                                      solid; FONT-SIZE: 9pt; BORDER-LEFT: medium NONE; BORDER-BOTTOM:
                                      medium NONE; FONT-FAMILY:
                                      ''Verdana''; BORDER-COLLAPSE: collapse"
                                      cellSpacing=0 cellPadding=2 border=1>
                                     <TBODY>
                                     <TR bgColor=#ffcc99>
                                     <TD width=170><B>SUMMARY </B>;</TD>
                                     <TD colSpan=4> '
                     || THIS_EMAILSUBJECT
                     || ' ; </TD></TR>
                     <TR bgColor=#edf7e7>
                                         <TD colSpan=1><B>Staff No</B></TD>
                                         <TD colSpan=1><B>Name</B></TD>
                                         <TD colSpan=1><B>Cost Code</B></TD>
                                         <TD colSpan=1><B>Location</B></TD>
                                     <TD colSpan=1><B>Last Updated Date </B></TD>
                                     </TR>';
           FOR C1 IN C_REJ_LEVEL(1)
           LOOP
           checklength:=length (THIS_EMAILMESSAGE);
           countrec:=countrec+1;
            dbms_output.put_line(length(THIS_EMAILMESSAGE));
             THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                                
                                     '<TR>
                                     <TD colSpan=1>' ||c1.table_name ||'</TD>
                                     <TD colSpan=1>' ||c1.tablespace_name ||'</TD>
                                     <TD colSpan=1>' ||c1.table_name ||'</TD>
                                     <TD colSpan=1>' ||c1.tablespace_name ||'</TD>
                                     <TD colSpan=1>' ||c1.table_name|| '</TD>
                                     </TR>'; 
             end loop;        
               dbms_output.put_line(length(THIS_EMAILMESSAGE));              
               THIS_EMAILMESSAGE :=   THIS_EMAILMESSAGE ||                                                
                                     '<TR bgColor=#edf7e7>
                                     <TD colSpan=1></TD>
                                     <TD colSpan=1></TD>
                                     </TR>
                                     </TBODY></TABLE></SPAN></DIV></BODY></HTML>';                
  dbms_output.put_line(length(THIS_EMAILMESSAGE));
  
  END;

All I have changed is the driving cursor selecting from user_tables and I have tested it in oracle apex website and it works fine no problem at all. Unfortunately I don't have oracle installed on the pc where I can access internet hence I am not able to post the output. Try this piece of code and see if it works and try to add step by step. Very old way of debugging but it is efficient.

Regards

Raj
icon7.gif  Re: Is there any way to store the value more then 32760 in a variable. [message #357598 is a reply to message #357465] Thu, 06 November 2008 00:29 Go to previous message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
Hi guys,

Thank you so much for your help n support.
it took me loong to figure out the problem. it seems to be a date issue. now i m picking it in to_char format in cursor.


thanks again.
Previous Topic: select count(1)
Next Topic: Tables used in stored procedure
Goto Forum:
  


Current Time: Tue Dec 06 16:16:35 CST 2016

Total time taken to generate the page: 0.10845 seconds