OraFAQ Forum: SQL & PL/SQL » CHR(13)||CHR(10) at the end of the sql

Home » SQL & PL/SQL » SQL & PL/SQL » CHR(13)||CHR(10) at the end of the sql (Oracle 8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CHR(13)||CHR(10) at the end of the sql [message #326242]

Tue, 10 June 2008 17:42

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What happens if you eliminate the extraneous {LF} from your code?

Report message to a moderator

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

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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!

Report message to a moderator

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

apps_user
Messages: 35
Registered: May 2008

Member

I am asking how to handle in SQL and I know how to handle it unix.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I know how to handle it unix.
Then what is the problem?
$ man unix2dos

Report message to a moderator

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

apps_user
Messages: 35
Registered: May 2008

Member

I NEED IT TO BE FIXED IN SQL.(NO UNIX TALKS,PLEASE)
ANY IDEAS?
THANKS

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

apps_user
Messages: 35
Registered: May 2008

Member

Anyway,I found the solution myself.
Closing the thread.

Report message to a moderator

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

Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Great you thought to post it for others.

Regards
Michel

Report message to a moderator

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

apps_user
Messages: 35
Registered: May 2008

Member

Thanks Michel.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

so what was the SQL that solved your problem?

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

apps_user
Messages: 35
Registered: May 2008

Member

Blank line went away.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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!

Report message to a moderator

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

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);

Report message to a moderator

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

Frank
Messages: 7901
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

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Create Partition in Existing Table
Next Topic:	Where clause and a plus sign

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 29 21:40:11 CDT 2026