1

How to generate csv in oracle if it's query result return more than 32767 character per line in linux shell script

2
  • 1
    From SQL*Plus, or something else?
    – Philᵀᴹ
    Jul 11, 2016 at 7:13
  • What problem are you actually having? Jul 11, 2016 at 11:25

1 Answer 1

1

You can't use utl_file, since it has a maximum line length of 32767. It's possible that sqlplus has a similar limit. If you are tying to move data to a different Oracle database you can use data pump. You can eve use datapump on a materialized view. If you want to migrate to a non Oracle database you can use Perl or heterogeneous services to create a database connection to mySQL, SQL Server, etc. Then you won't have to worry about the line length.

UTL_FILE

4
  • You could however populate a clob with all your data, appending a new line character after each row then use utl_file to write to file by reading from clob. If you open the file with utl_file.fopen(..., max_linesize=>32767); you can write upto 32767 characters in each write. if you want to enter more characters per line, use utl_file.put multiple times, at the end add a new line character. lather. rinse. repeat for next line.
    – Raj
    Jul 12, 2016 at 3:25
  • The clob can work, if you use dbms_lob to write to a file on disk.
    – Gandolf989
    Jul 12, 2016 at 15:41
  • are you saying that there is a dependency here? i.e. clob can work if used with dbms_lob? i am fairly certain you can write clob contents using utl_file too.
    – Raj
    Jul 12, 2016 at 17:09
  • No, I am not saying that there is a dependency. I am saying that if you take a clob and write it to a file as a clob, then you will have fewer restrictions on how large the clob can be. It would likely be more efficient to use dbms_lob as well. But i have not proven that dbms_lob will work in this case. The OP should try to code a solution using clob and dbms_lob to see if it can handle linesize larger than 32k.
    – Gandolf989
    Jul 12, 2016 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.