Home » SQL & PL/SQL » SQL & PL/SQL » using chr(10) properly
using chr(10) properly [message #250468] Tue, 10 July 2007 03:00 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a piece of code


declare
l_Retention_Date    DATE;
l_purgedate date :=sysdate;
l_RetentionCategory varchar2(20):='mycategory';

l_linedata          VARCHAR2(32767);

n_Retention_Days number:=5;
begin
 
l_Retention_Date := TRUNC(SYSDATE) - 5;



l_linedata := ' Purge for my Database on : '||l_purgedate||chr(10);
--calling a proc

l_linedata:=l_linedata||chr(10)||l_linedata;

l_linedata := ' Retention Category : '||l_RetentionCategory||chr(10);
--calling a proc

l_linedata:=l_linedata||chr(10)||l_linedata;

l_linedata :=' Retention Days     : '||n_Retention_Days||chr(10);
--calling a proc

l_linedata:=l_linedata||chr(10)||l_linedata;

dbms_output.put_line(l_linedata);

end;





i want output something like this
Purge for my Database on : 12-DEC-2006 17:10:54
Retention Category : mycategory
Retention Days : 5


but with above code, am not getting it, can someone point out
where the mistake is?


Re: using chr(10) properly [message #250470 is a reply to message #250468] Tue, 10 July 2007 03:06 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Try with this:
declare
l_Retention_Date    DATE;
l_purgedate date :=sysdate;
l_RetentionCategory varchar2(20):='mycategory';

l_linedata          VARCHAR2(32767);

n_Retention_Days number:=5;
begin

l_Retention_Date := TRUNC(SYSDATE) - 5;



l_linedata := ' Purge for my Database on : '||l_purgedate||chr(10);
--calling a proc

l_linedata:=l_linedata||chr(10);

l_linedata := l_linedata||' Retention Category : '||l_RetentionCategory||chr(10);
--calling a proc

l_linedata:=l_linedata||chr(10);

l_linedata :=l_linedata||' Retention Days     : '||n_Retention_Days||chr(10);
--calling a proc

dbms_output.put_line(l_linedata);

end;


Regards
Sanka
Re: using chr(10) properly [message #250490 is a reply to message #250468] Tue, 10 July 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but with above code, am not getting it, can someone point out
where the mistake is?

What do you get?
Why don't you call dbms_output.put_line for each line?

Regards
Michel
Re: using chr(10) properly [message #250538 is a reply to message #250470] Tue, 10 July 2007 06:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
remove the l_linedata:=l_linedata||chr(10); lines
Re: using chr(10) properly [message #250844 is a reply to message #250468] Wed, 11 July 2007 09:39 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
What are you getting? If the only problem is the date/time info, you need to format your date/time variable with to_char, like this...

l_linedata := ' Purge for my Database on : '||to_char(l_purgedate,'MM/DD/YYYY HH24:MI:SS')||chr(10);


Also, what does the "--calling a proc" comment mean? Are you actually executing additional code in here or are these just placeholder reminders for you?

Based on what I see, you should be getting...

 Purge for my Database on : 12-DEC-2006
 Retention Category : mycategory
 Retention Days : 5


although, because of the "chr(10)" only, you might be getting...

 Purge for my Database on : 12-DEC-2006
                                       Retention Category : mycategory
                                                                            Retention Days : 5



My other recommendation would be to change "chr(10)" to use one of the following...

 v_crlf   varchar2(2)  := chr(10)||chr(13);

  ....

 l_linedata := ' Purge for my Database on : '||l_purgedate||v_crlf;



since chr(10) is only a line feed and chr(13) is a carriage return. Or, you can use the utl_tcp function shown below...

  l_linedata := ' Purge for my Database on : '||l_purgedate||utl_tcp.CRLF;


Hope this helps,
Ron
Previous Topic: Calculating age
Next Topic: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Goto Forum:
  


Current Time: Wed Dec 07 20:39:11 CST 2016

Total time taken to generate the page: 0.28965 seconds