How to generate csv in oracle if it's query result return more than 32767 character per line in linux shell script
-
1From SQL*Plus, or something else?– PhilᵀᴹJul 11, 2016 at 7:13
-
What problem are you actually having?– Andrew BrennanJul 11, 2016 at 11:25
Add a comment
|
1 Answer
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.
-
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.– RajJul 12, 2016 at 3:25
-
The clob can work, if you use dbms_lob to write to a file on disk. 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.– RajJul 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. Jul 12, 2016 at 17:49