Oracle APEX report - processing a field value-CodePudding

I have been creating Oracle APEX reports for a while.

I need to processes a column value to charge it's format.

The current SQL looks like this. All is good.

select PP_RUNDATA.ID as ID,

PP_RUNDATA.PP_START_TIME as PP_START_TIME,

PP_RUNDATA.PP_END_TIME as PP_END_TIME,

from PP_RUNDATA PP_RUNDATA

The time columns are in seconds. This PL/SQL converts the seconds into date / time. (this work well)

alter session set nls_date_format="dd/mm/yyyy - hh24:mi:ss";
select date '1970-01-01'   1661596871 * interval '1' second result from dual;

I need to bring together the SQL and PL/SQL and change the 1661596871 to the column value.

I know I can use a "List Value" to process the PP_START_TIME / PP_END_TIME column using PL/SQL returning SQL.

A very poor, not working example,

declare
temp1  varchar2(500);
begin
EXECUTE IMMEDIATE 'alter session set nls_date_format="dd/mm/yyyy - hh24:mi:ss"';
select date '1970-01-01'   PP_START_TIME * interval '1' second into temp1 from dual;
return 'select temp2 from dual';
End;

I know there are more than two things wrong with this coding;

1) Syntax to access data in column PP_START_TIME (maybe &PP_START_TIME.)

2) the "return statement"

What am I doing wrong in the coding / my thinking in the above or maybe the overall approach in Oracle APEX Reports?

Thanks for looking

Pete

CodePudding user response：

You can use:

select ID,
       DATE '1970-01-01'   PP_START_TIME * INTERVAL '1' SECOND as PP_START_TIME,
       DATE '1970-01-01'   PP_END_TIME   * INTERVAL '1' SECOND as PP_END_TIME
from   PP_RUNDATA

If you want a particular format then you can use TO_CHAR:

select ID,
       TO_CHAR(
         DATE '1970-01-01'   PP_START_TIME * INTERVAL '1' SECOND,
         'dd/mm/yyyy - hh24:mi:ss'
       ) as PP_START_TIME,
       TO_CHAR(
         DATE '1970-01-01'   PP_END_TIME   * INTERVAL '1' SECOND,
         'dd/mm/yyyy - hh24:mi:ss'
       ) as PP_END_TIME
from   PP_RUNDATA