Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rafael.

Asked: April 07, 2021 - 2:51 pm UTC

Last updated: April 12, 2021 - 6:01 am UTC

Version: 11g

Viewed 100+ times

You Asked

Hello. I have a query that produces a result. We need to automate the result into a text file with pipe (|) delimited characters that will be used in another process. The problem is that creating this file using the UTL_FILE tool will store it on the database server side folders. I don't have access to the database server and the tool wont allow me to create the file on a local folder in my computer or any other server. Our Oracle database is running on a Unix Solaris server, and all clients are on Windows platforms (server and PCs). I have researched everywhere on how to code anything to be able to create any type of file from the query, like SPOOL and 3rd party development (Java, .NET) but it is either complicated or the output is not formatted appropriately. The best tool so far has been UTL_FILE, but without the capability of being able to create and store the file on a different server other than the database server, it seems very difficult to automate the process, most specially when there is a UNIX/Windows separation. So the question, what other ways can I create the pipe (|) delimited text file and store it locally? The development tool we use is PL/SQL Developer.

and we said...

SQL*Plus or SQLcl will both let you spool results from a query to the file system where they are running.

For example, in SQLPlus

SQL> set markup csv on delimiter '|'
SQL> select * from scott.emp;

"EMPNO"|"ENAME"|"JOB"|"MGR"|"HIREDATE"|"SAL"|"COMM"|"DEPTNO"
7499|"ALLEN"|"SALESMAN"|7698|"20-FEB-81"|1600|300|30
7521|"WARD"|"SALESMAN"|7698|"22-FEB-81"|1250|500|30
7566|"JONES"|"MANAGER"|7839|"02-APR-81"|2975||20
7654|"MARTIN"|"SALESMAN"|7698|"28-SEP-81"|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|"01-MAY-81"|2850||30
7782|"CLARK"|"MANAGER"|7839|"09-JUN-81"|2450||10
7788|"SCOTT"|"ANALYST"|7566|"09-DEC-82"|3000||20
7839|"KING"|"PRESIDENT"||"17-NOV-81"|5000||10
7844|"TURNER"|"SALESMAN"|7698|"08-SEP-81"|1500||30
7876|"ADAMS"|"CLERK"|7788|"12-JAN-83"|1100||20
7900|"JAMES"|"CLERK"|7698|"03-DEC-81"|950||30
7902|"FORD"|"ANALYST"|7566|"03-DEC-81"|3000||20
7934|"MILLER"|"CLERK"|7782|"23-JAN-82"|1300||10