Syntax to use for Export in Oracle using PIPE "|" delimited format???

MIKE
MIKE used Ask the Experts™
on
Experts,

I need a simple sample syntax code showing HOW to create a SELECT statement in Oracle that will create a PIPE DELIMITED file for export.

What is the correct SELECT SYNTAX?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select col1 || '|' || col2 || '|' || col3 from yourtable
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If the resulting string can be greater than 4000 characters and you are on 10g or above try this:


SELECT EXTRACT(XMLAGG(XMLELEMENT("s",
                 col1|| '|',
                 col2 || '|',
                 col3
         )), '/s/text()').getstringval()
           column1
FROM yourtable;
Most Valuable Expert 2011
Top Expert 2012

Commented:
actually, if it'll be greater than 4000 characters


    )), '/s/text()').getstringval()


won't work, that still has the 4000 limit,  try


    )), '/s/text()').getclobval()


but...  that query will put your entire table on a single row.  I'm not sure that's what you're really looking for
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Yes.  I forgot to make that change when I pasted it.  Thanks for the correction!

>>that query will put your entire table on a single row

If you add a group by it will separate it out.
Most Valuable Expert 2011
Top Expert 2012

Commented:
you'd have to group by rowid to be reliable, but that's not legal for all queries,


however, the problem there is xmlagg which isn't needed, just skip that step


EXTRACT(XMLELEMENT("s", col1 || '|', col2 || '|', col3), '/s/text()').getclobval()  
Most Valuable Expert 2011
Top Expert 2012

Commented:
actually it is possible for the xml trick (as presented above) still to fail ,  if any one field is 4000 then adding the '|' to it will cause an error

however, it's an easy fix, simply add the '|' as distinct fields within the element

 
SELECT EXTRACT(XMLELEMENT("s", col1 , '|', col2 , '|', col3), '/s/text()').getclobval() column1
  FROM yourtable;
Most Valuable Expert 2011
Top Expert 2012

Commented:
but,  again, this extra xml step is only needed if your resulting strings will be longer than 4000 for one line, for shorter results, just use the original post,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial