This is my little collection of SQL Developer tips for functional consultants like myself – so nothing complicated really but good to know.
Default Output directory:
When running in script mode and you use the spool commend your output will be located in:
%APPDATA%\SQL Developer
Due to the space be sure to use "s:
dir "%APPDATA%\SQL Developer"
Default Export Options
The new SQL Developer is very flexible when it comes to exporting data but to do a simple Excel export is just too many clicks.
However there is a workaround.
First select all data with: ctrl + A
Then copy all to clipboard including headers using: ctrl + shift + C
Formatting Output
When running in script mode you can add formatting keywords like this:
SELECT /*loader*/ * FROM scott.emp;
Output:
7369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20|
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|0|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|
Available keywords are:
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;
SQL Script Location
When running in script mode you can call other scripts using start command.
However be sure to set preferences to SQL Developer can find the scripts:
Here set to: E:\Temp\sql
NLS
If you are working with E-Business Suite then to make _VL view to work you need to ensure your environment is set correctly.
Sometimes SQL Developer defaults environment variable to local values rather than the generic US ones.
Setup like this: