Home » SQL & PL/SQL » SQL & PL/SQL » CHR(13)||CHR(10) at the end of the sql (Oracle 8i)
CHR(13)||CHR(10) at the end of the sql [message #326242] Tue, 10 June 2008 17:42 Go to next message
apps_user
Messages: 35
Registered: May 2008
Member
HI
I am creating a sql extract for Windows format from unix.
In my sql I added CHR(13)||CHR(10) to the sql in order to add {CR}{LF} at the end of each row for Windows format.{CR} stands for Carriage Return and {LF} starts for line feed.

But once I added CHR(13)||CHR(10)to the sql,it is creating a blank line between {CR} and {LF}.
Previously the extract used to be
Quote:

ABC,123,CDE


Now it is
Quote:

ABC,123,CDE CR
LF



Please help me.
Re: CHR(13)||CHR(10) at the end of the sql [message #326243 is a reply to message #326242] Tue, 10 June 2008 17:51 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
What happens if you eliminate the extraneous {LF} from your code?
Re: CHR(13)||CHR(10) at the end of the sql [message #326244 is a reply to message #326243] Tue, 10 June 2008 18:01 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
If I eliminate CHR(10) or {LF} then the output is fine.
But they want {LF} at the end of the each row so that it complies with Windows format.

[Updated on: Tue, 10 June 2008 18:01]

Report message to a moderator

Re: CHR(13)||CHR(10) at the end of the sql [message #326245 is a reply to message #326242] Tue, 10 June 2008 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
You don't know what you don't know.

The final solution depends upon how the resultant file is transfered from Unix to Windoze.

On Unix BEFORE transfer you can see what is in the file by:
od -c my_output.csv

If you transfer the file using the ftp utility & specify ASCII mode, then no extra characters are needed!
Re: CHR(13)||CHR(10) at the end of the sql [message #326247 is a reply to message #326245] Tue, 10 June 2008 18:30 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
I am asking how to handle in SQL and I know how to handle it unix.
Re: CHR(13)||CHR(10) at the end of the sql [message #326248 is a reply to message #326242] Tue, 10 June 2008 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
> I know how to handle it unix.
Then what is the problem?
$ man unix2dos
Re: CHR(13)||CHR(10) at the end of the sql [message #326249 is a reply to message #326248] Tue, 10 June 2008 18:45 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
I NEED IT TO BE FIXED IN SQL.(NO UNIX TALKS,PLEASE)
ANY IDEAS?
THANKS
Re: CHR(13)||CHR(10) at the end of the sql [message #326250 is a reply to message #326242] Tue, 10 June 2008 18:47 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You need to fix the problem with your Caps Lock key before proceeding.
Re: CHR(13)||CHR(10) at the end of the sql [message #326251 is a reply to message #326250] Tue, 10 June 2008 18:50 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
I donot know why I should go to read guidelines.
My question is simple,How do I fix it in SQL?
I donot want people to suggest me to fix it in Unix.
Did I break any guidelines?
If so then please suggest.
Re: CHR(13)||CHR(10) at the end of the sql [message #326267 is a reply to message #326251] Tue, 10 June 2008 23:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Till your last 2 posts I think about answering your question, now it is OVER.
You lose.
First read rules then follow them then you have answer.

Regards
Michel
Re: CHR(13)||CHR(10) at the end of the sql [message #326268 is a reply to message #326267] Tue, 10 June 2008 23:14 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Anyway,I found the solution myself.
Closing the thread.
Re: CHR(13)||CHR(10) at the end of the sql [message #326273 is a reply to message #326268] Tue, 10 June 2008 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Great you thought to post it for others.

Regards
Michel
Re: CHR(13)||CHR(10) at the end of the sql [message #326274 is a reply to message #326273] Tue, 10 June 2008 23:29 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Thanks Michel.
Re: CHR(13)||CHR(10) at the end of the sql [message #326275 is a reply to message #326242] Tue, 10 June 2008 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
so what was the SQL that solved your problem?
Re: CHR(13)||CHR(10) at the end of the sql [message #326284 is a reply to message #326242] Tue, 10 June 2008 23:56 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
declare a variable of char type.
in the begin section
assign the variable with the concatenated string of CHR(13) and CHR(10).
Then call the variable in the select.
Re: CHR(13)||CHR(10) at the end of the sql [message #326287 is a reply to message #326284] Wed, 11 June 2008 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
apps_user wrote on Tue, 10 June 2008 21:56
declare a variable of char type.
in the begin section
assign the variable with the concatenated string of CHR(13) and CHR(10).
Then call the variable in the select.

and how exactly did this improve the situation with the extra blank link which was initially reported?

Please post a complete & functioning example with both before & after reproducible SQL.

[Updated on: Wed, 11 June 2008 00:03] by Moderator

Report message to a moderator

Re: CHR(13)||CHR(10) at the end of the sql [message #326290 is a reply to message #326287] Wed, 11 June 2008 00:04 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Blank line went away.
Re: CHR(13)||CHR(10) at the end of the sql [message #326293 is a reply to message #326290] Wed, 11 June 2008 00:09 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
apps_user wrote on Tue, 10 June 2008 22:04
Blank line went away.

what change was made (both before & after) that solved the problem?

Please NOTE that never once in this whole thread has anyone (other than you) has ever seen the SQL you keep discussing!
Re: CHR(13)||CHR(10) at the end of the sql [message #326299 is a reply to message #326293] Wed, 11 June 2008 00:29 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Here is the link and details which I followed and accordingly changed my code to remove the blank lines.

http://dizwell.com/2008/04/28/automatically-emailing-excel-spreadsheets-from-oracle/

Here is how it is used in the article.

linefeed varchar(2) := chr(13)||chr(10);
Re: CHR(13)||CHR(10) at the end of the sql [message #326304 is a reply to message #326299] Wed, 11 June 2008 00:47 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
How can concatenating a variable, containing the value chr(13)||chr(10) ever generate a different result then concatenating char(13)||chr(10)?!

Howard probably only used a variable for readability
Re: CHR(13)||CHR(10) at the end of the sql [message #326312 is a reply to message #326299] Wed, 11 June 2008 01:16 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
apps_user wrote on Tue, 10 June 2008 22:29
Here is the link and details which I followed and accordingly changed my code to remove the blank lines.

http://dizwell.com/2008/04/28/automatically-emailing-excel-spreadsheets-from-oracle/

Here is how it is used in the article.

linefeed varchar(2) := chr(13)||chr(10);

So what was the SQL that did not work & what was the solution?
Re: CHR(13)||CHR(10) at the end of the sql [message #326518 is a reply to message #326312] Wed, 11 June 2008 20:30 Go to previous message
dizwell
Messages: 3
Registered: May 2007
Junior Member
Got it in one: readability.

When I have lengthy text messages before the XML/Excel component, I have a lot of linebreaks to insert. The code looks appalling with all those chr(xx) bits in there. I just insert the variable 'linefeed' instead, and it looks rather tidier (at least to my eyes).

I've never experienced the problem the OP originally reported.

Neither, like you, do I understand how using a variable containing concatenated values differ from using those concatenated values directly.

Oh well...
HJR
Previous Topic: Create Partition in Existing Table
Next Topic: Where clause and a plus sign
Goto Forum:
  


Current Time: Tue Sep 02 05:32:09 CDT 2014

Total time taken to generate the page: 0.20108 seconds