SQL Developer Hints to Format Output

Oracle

SQL Developer may not be the perfect IDE, but it doesn’t mean that it has not got any tricks at all. One of those features I’d like mention about can ease data transfer. SQL Developer excepts hints to format output. Here are the outputs for some of those hints; csv, loader, fixed and insert. As names implie; CSV gives rows with columns separated by commas, loader gives data formatted for SQL Loader, fixed returns columns separated by fixed spaces and insert creates insert commands.

select /csv/ * from scott.dept;
“DEPTNO”,”DNAME”,”LOC”
10,”ACCOUNTING”,”NEW YORK”
20,”RESEARCH”,”DALLAS”
30,”SALES”,”CHICAGO”
40,”OPERATIONS”,”BOSTON”

select /loader/ * from scott.dept;
10|”ACCOUNTING”|”NEW YORK”|
20|”RESEARCH”|”DALLAS”|
30|”SALES”|”CHICAGO”|
40|”OPERATIONS”|”BOSTON”|

select /fixed/ * from scott.dept;
“DEPTNO” “DNAME” “LOC”
“10” “ACCOUNTING” “NEW YORK”
“20” “RESEARCH” “DALLAS”
“30” “SALES” “CHICAGO”
“40” “OPERATIONS” “BOSTON”

select /insert/ * from scott.dept;
REM INSERTING into scott.dept
SET DEFINE OFF;
Insert into scott.dept (DEPTNO,DNAME,LOC) values (10,’ACCOUNTING’,’NEW YORK’);
Insert into scott.dept (DEPTNO,DNAME,LOC) values (20,’RESEARCH’,’DALLAS’);
Insert into scott.dept (DEPTNO,DNAME,LOC) values (30,’SALES’,’CHICAGO’);
Insert into scott.dept (DEPTNO,DNAME,LOC) values (40,’OPERATIONS’,’BOSTON’);

It’s also possible to get output in html or xml format which can really be handy for a quick report. As you can see below, html format includes even a search box to make searches in content.