CODEWITHSUNDEEP
javasqloracle-database
M.MELKI
M.MELKI

Reputation: 47

formatting query results using oracle

I want to format query results in oracle and save them into an output file.

I tried this query: 

spool "result.txt"
SELECT STA_CODE,DATE_CREATION,DATE_FIN_INSTANCE,DATE_FIN_TRAITEMENT  FROM DEMANDE;
spool off;

In my output file, the result looks like:

STA_CODE                  DATE_CRE DATE_FIN DATE_FIN

------------------------- -------- -------- --------

200                       09/05/17 09/05/17 09/05/17 

400                       09/05/17 09/05/17 09/05/18

I want then to write a java code that takes for each line the result and match it with name of column: for example STA_CODE=200, STA_CODE=400, DATE_CRE=09/05/17, DATE_CRE=09/05/18 .... I'm biginner in JAVA and I can't write that Bit of code. Is possible to directly format query results and then parse the output file without doing any transformation with java.

Upvotes: 0

Views: 84

Answers (1)

Utsav
Utsav

Reputation: 8093

If you want each row separated, then use

SELECT 'STA_CODE='||STA_CODE
 ||', DATE_CRE=' ||to_date(DATE_CREATION,'DD/MM/YY')---other values 
from DEMANDE

If you want all STA_CODE first and then all DATE_CRE and then other columns in one line, separated by comma, use something like

select listagg(col1,', ') within group (order by seq)
from (
SELECT 1 as seq,'STA_CODE='||STA_CODE as col1 from DEMANDE
union
SELECT 2 as seq,'DATE_CRE='||to_date(DATE_CREATION,'DD/MM/YY') from DEMANDE
union 
---- other select queryies separated by union.

)

NOTE: You cannot guarantee order among each row. So it might happen that second STA_CODE come first and first DATE_CRE come second. To garuntee that, order by a column in all union queries.

Upvotes: 1

Related Questions