Thursday, May 17, 2012

Formatting Query Results in Oracle SQL Developer

In Oracle SQL Developer you can use what looks like optimizer hints (without the plus sign...) and specify what kind of output you want to generate:
SELECT /*csv*/       * FROM scott.emp;
SELECT /*xml*/       * FROM scott.emp;
SELECT /*html*/      * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/    * FROM scott.emp;
SELECT /*loader*/    * FROM scott.emp;
SELECT /*fixed*/     * FROM scott.emp;
SELECT /*text*/      * FROM scott.emp;
The one I like the most is the /*html*/ output with the nice search functionality.

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

From: http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

No comments:

Post a Comment