CODEWITHSUNDEEP
sqloracle-databaselinebreak
James
James

Reputation: 21

How to use CHR (10) to start a new line using Oracle SQL

I'm trying to use CHR (10) to start a new line in my oracle SQL view. Where should I put it and how should it be formatted? I've seen it used in example before I just can't get it to work.

    SELECT
    CUSTOMER.FIRST_NAME ||' '||
    CUSTOMER.SURNAME ||' sold their '||
    EXTRACT (YEAR FROM CAR.FIRST_REG_DATE) ||' '|| 
    CAR.MAKE ||' '||
    CAR.MODEL ||' with '||
    CAR.CURRENT_MILEAGE ||' miles for £'|| 
    CAR.PURCHASE_PRICE ||' which was then bought on '||
    CAR.SOLD_DATE ||' for £'||
    CAR.SOLD_PRICE ||' making a profit of £'||
    (CAR.SOLD_PRICE - CAR.PURCHASE_PRICE) ||' for staff member '||
    STAFF.FIRST_NAME ||' '||
    STAFF.SURNAME

Upvotes: 1

Views: 46278

Answers (2)

Ajay Kumar Aadhi
Ajay Kumar Aadhi

Reputation: 1

create or replace FUNCTION GET_XMER_CODE (P_shipment_num IN VARCHAR2,P_ITEM_id VARCHAR2)
RETURN VARCHAR2
AS
CURSOR C_MAIN IS 
select xmer_code from 
mtl_material_transactions mmt,
xxinv_rca_tbl xrt
where xrt.TRANSACTION_ID=mmt.ATTRIBUTE15
--and  xrt.RCA_NUMBER=mmt.ATTRIBUTE10
and mmt.ATTRIBUTE15=P_shipment_num--'RCA-31152'
and xrt.ITEM_CODE=(Select segment1 from mtl_system_items_b where inventory_item_id=P_ITEM_id and rownum=1)
and mmt.INVENTORY_ITEM_ID=P_ITEM_id;
V_XMER_DATA VARCHAR2(5000):= null;
BEGIN
FOR C_REC IN C_MAIN 

LOOP
V_XMER_DATA := V_XMER_DATA|| ','|| CHR(10) ||C_REC.XMER_CODE ;

END LOOP;
RETURN ltrim(rtrim(V_XMER_DATA,'`enter code here`,'),',');


EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('XMER CODE'||sqlerrm);
END;

Upvotes: 0

cableload
cableload

Reputation: 4375

Wherever you want the line break, use CHR(10)

So for a string 'I want a line break. This goes on the second line'

You want something like this

select 'I want a line break.' || CHR(10) || 'This goes on the second line' from ...

Upvotes: 5

Related Questions