OraFAQ Forum: SQL & PL/SQL » Carriage return Line feed multiple times in the same CLOB

Home » SQL & PL/SQL » SQL & PL/SQL » Carriage return Line feed multiple times in the same CLOB (Oracle 10.2)

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

Carriage return Line feed multiple times in the same CLOB [message #541403]

Tue, 31 January 2012 02:18

El_Coto
Messages: 9
Registered: January 2012

Junior Member

Hello
I'm looking for a way to make CRLFs show in a CLOB.
I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string..

Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

Sorry if I missed any information!

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541406 is a reply to message #541403]

Tue, 31 January 2012 02:22

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

Senior Member
Account Moderator

Why not directly put the CRLF in your string data?

SQL> select 'a
  2  line
  3  feed
  4  ' from dual;
'ALINEFEED'
------------
a
line
feed

1 row selected.

Regards
Michel

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541416 is a reply to message #541406]

Tue, 31 January 2012 02:50

El_Coto
Messages: 9
Registered: January 2012

Junior Member

Thanks for you quick answer,
I'm not sure how you want me to directly insert the CRLF?

Can I somehow select a CRLF and then put it in the end of MyConc?

Here's some more info about MyConc:
I'm running a select-querry that reply about 10 columns which I then concatinate in another application. Depending on a few things this cocatinated string will then either be inserted in a DB or concatinated again with the next row from the querry (along with a CRLF between the two rows) Thus, I don't know how many CRLF will be needed..

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541420 is a reply to message #541416]

Tue, 31 January 2012 03:02

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

Senior Member
Account Moderator

If you concatenate several rows then you know you have to concatenate them so when you concatenate them but a string with '
'.

Regards
Michel

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541425 is a reply to message #541420]

Tue, 31 January 2012 03:37

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Carriage Return

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541426 is a reply to message #541425]

Tue, 31 January 2012 03:40

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you're getting [x] in the string then just use replace to replace it with chr(13) || chr(10).

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541439 is a reply to message #541426]

Tue, 31 January 2012 05:47

El_Coto
Messages: 9
Registered: January 2012

Junior Member

Michael: I've tried this too, but the CRLF (the enter key?) can't be inserted or copied.. Do you know how too?

Cookiemonster:
So if I would run this command:
insert into table(MyTable) values('1;blue;Woodstock;;chr(13) || chr(10) 34;giant;squid;attack;')

this would result in?
1;blue;Woodstock;;
34;giant;squid;attack;

I think I tried this but didn't succeed..

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541440 is a reply to message #541439]

Tue, 31 January 2012 05:54

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

It'd have to be:

insert into MyTable values('1;blue;Woodstock;;'||chr(13) || chr(10) ||'34;giant;squid;attack;');

I was suggesting this:

insert into MyTable values(REPLACE('1;blue;Woodstock;;[X]34;giant;squid;attack;', '[X]', (CHR(13)||CHR(10))));

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541446 is a reply to message #541439]

Tue, 31 January 2012 06:45

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

Senior Member
Account Moderator

Quote:

Michael: I've tried this too, but the CRLF (the enter key?) can't be inserted or copied.. Do you know how too?

Of course it can, just like I showed in my previous post.

Regards
Michel

Report message to a moderator

Re: Carriage return Line feed multiple times in the same CLOB [message #541447 is a reply to message #541440]

Tue, 31 January 2012 06:50

El_Coto
Messages: 9
Registered: January 2012

Junior Member

Mr.Cookie you deserve all the dough you can get your hands on!
It worked excellent, a question regarding performance:
Should I do the REPLACE on each insert or would it be better if I first insert all my rows and then do an UPDATE Table SET DATA = REPLACE(DATA,'[X]', chr(13) || chr(10) ))

Thanks a million!

Report message to a moderator