formatting query results using oracle

Question

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.

Not really sure how you want the output to look from the query. Is it all the STA_CODE values then all the DATE_CRE values and so on ? — BriteSponge
– BriteSponge, Commented May 10, 2017 at 11:13

Utsav · Accepted Answer · 2017-05-10 12:01:11Z

1

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.

answered May 10, 2017 at 12:01

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M.MELKI Over a year ago

IT's okey..Thanks !

Collectives™ on Stack Overflow

formatting query results using oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related