Home » SQL & PL/SQL » SQL & PL/SQL » File separator creates problem (Oracle 10g)
File separator creates problem [message #413889] Sat, 18 July 2009 03:15 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
IN a plsql block there is

EXECUTE IMMEDIATE 'insert into demand_out select  rownum as sort_ordr ,PLN_DMND_NAME ||'~'||PLN_CAT ||'~'||
DMND_SHIP_NAME ||'~'||DUE_DATE 
||'~'||ADJ_DUE_DATE_TIME||'~'||DMND_LINE_ITEM_NAME ||'~'||REQ_ITEM||'~'||REQ_QTY||'~'||DC ||'~'||
       BA_LIMIT||'~'||BL_LIMIT ||'~'||SORT_KEY||'~'||
CUST_NO ||'~'||DOMN_NAME||'~'||SEQ_CTR||'~'||
STYLE_COLOR  as text,cnt||'_file_nm_'||r_c1.entity_name
        from temp_sort order by ' || r_c1.srt_order1||',' ||
 r_c1.srt_order2||','||
                              r_c1.srt_order3||','|| r_c1.srt_order4||','||r_c1.srt_order5||','
||r_c1.srt_order6||','||
                              r_c1.srt_order7 ;

it is causing problem due to ||'~'
my objective is to get
insert into demand_out 
select  rownum as sort_ordr ,PLN_DMND_NAME ||'~'||PLN_CAT ||'~'||DMND_SHIP_NAME ||'~'||DUE_DATE 
||'~'||ADJ_DUE_DATE_TIME||'~'||DMND_LINE_ITEM_NAME ||'~'||
REQ_ITEM||'~'||REQ_QTY||'~'||DC ||'~'||
       BA_LIMIT||'~'||BL_LIMIT ||'~'||SORT_KEY||'~'||
CUST_NO ||'~'||DOMN_NAME||'~'||SEQ_CTR||'~'||STYLE_COLOR
  as text,cnt||'_file_nm_'||r_c1.entity_name
        from temp_sort order by 
--and the order by clause through varibles.

pls suggest how to get it fix

[Updated on: Sat, 18 July 2009 03:37] by Moderator

Report message to a moderator

Re: File separator creates problem [message #413891 is a reply to message #413889] Sat, 18 July 2009 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Double the ' inside the string.

And keep your lines in 80 character width.
Also don't explain what you get, SHOW US, use SQL*Plus and copy and paste your session and always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Sat, 18 July 2009 03:38]

Report message to a moderator

Re: File separator creates problem [message #413894 is a reply to message #413891] Sat, 18 July 2009 06:10 Go to previous messageGo to next message
kartangle1
Messages: 1
Registered: July 2009
Junior Member
Such a wonderful information give me i like your post
Re: File separator creates problem [message #413901 is a reply to message #413889] Sat, 18 July 2009 09:31 Go to previous message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>--and the order by clause through variables.
Just curious, what is purpose of ORDER BY clause used with INSERT

Rows in a (HEAP) table have no inherent order.
Previous Topic: retrieveing two values from table 1 and displaying it one record after joining it with table 2 (merg
Next Topic: NO DATA FOUND error
Goto Forum:
  


Current Time: Tue Dec 06 10:46:34 CST 2016

Total time taken to generate the page: 0.12932 seconds