How to do a linefeed inside PL/SQL? 3

[tpbjr]

Hi,

I am trying to figure out the syntax for a linefeed in the UNIX environment for PL/SQL.
I tried the following as a test but the result is not what I want. What I am actually doing is building a variable with a message. In this message I need to move to another line sometimes. I appreciate any help that I can get.

select plant || '\n'
from other_cost_detail

PLANT
-----
075\n
075\n


Instead I want a new line, like so...
PLANT
-----
075

075



Thank you for all your help

http://www.besware.com

Tom

 

[Dagon]

'\n' would be treated as a literal string. To get a line feed, use CHR(12) e.g.

select '075'||chr(12)
from dual

 

[SantaMufasa]

Tom,

To create a new line within an expression, concatenate to the expression (at the point) where you want the new line, a "chr(10)":

select 'Hi'||chr(10)||'my'||chr(10)
           ||'name'||chr(10)||'is'||chr(10)||'Dave' Output;

OUTPUT
------
Hi
my
name
is
Dave

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tpbjr]

Cool that worked for my test.
So is it safe to say that this will work in the PL/SQL code.

gv_hdr_build1 := 'FOUND DUPLICATE OCD RECORD(S) WITH RELATED OJ RECORD(S) ' ||chr(12)



Man, you do not know how much I appreciate this information.

Thank you for all your help

http://www.besware.com

Tom

 

[Dagon]

Yes, it should work in PL/SQL. However, dbms_output.put_line tends to suppress blank lines, so you may get some problems if you use that.

 

[hoinz]

Hello,

I would like to back SantaMufasa's suggestion;
chr(10) is linefeed,
whereas chr(12), as suggested by Dagon, is formfeed.

regards

 

[SantaMufasa]

So is it safe to say that this will work in the PL/SQL code?

Provided that the [Return] character ends up on some device (i.e., printer, screen) that understands [Return] characters, yes.

Also, as you can infer from the combination of Dagon's reply and mine, different character strings can work on different environments. Keep in mind that the hexidecimal representation of a "<carriage return><line feed>" combination is hex "0D0A" (ASCII 13+10), so, that is why I used a CHR(10) (linefeed) in my code...Dagon used a formfeed sequence (CHR(12)).

Let us know if this is useful information.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Isn't "cross posting" fun?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[tpbjr]

You all are great!

So I am going to attempt to use the following code and see what happens.

I guess I am being lazy because I am using VI (I do not prefer) and I am use to stepping through code to watch line by line and I cannot in this environment.

gv_msg_build := 'CANNOT DELETE THE OCD RECORD: ' || gv_OCD_trans_no || ', ' || gv_plant ||
', ' || gv_trans_dt || ', ' || gv_shift || ', ' || gv_item ||
', ' || gv_source_cd || ', ' || gv_fiscal_yr || ', ' || gv_fiscal_period ||
', ' || gv_week_number || ', ' || gv_item_cpc || ', ' || gv_quantity ||
', ' || gv_amount||chr(10);
gv_msg_build := gv_msg_build || ' ----------------------------------- ');
gv_msg_build := gv_msg_build || chr(10);


This would be something of what I would want the result to be:


CANNOT DELETE THE OCD RECORD: bla, bla, bla....
-----------------------------------
CANNOT DELETE THE OCD RECORD: bla, bla, bla....


on and on and on, for example.

Again, Thank you so much

Thank you for all your help

http://www.besware.com

Tom

 

[hoinz]

 

[johnherman]

plant := plant ||chr(13)||chr(10;
chr(13) is CR, chr(10) is LF

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright