Using the Oracle "at time zone" function in sql - problem and solution

Question

I have a table with a date column that I know is stored in GMT. I have a procedure that accepts a date input and an account ID. The procedure: 1) gets the account ID timezone (stored in table account) 2) determines the start and end range in GMT as follows: v_start_time := cast( from_tz( cast( i_date as timestamp ), v_tz ) at time zone c_gmt as date ); -- where i_date is input, v_tz is 'US/Eastern' or any other tzname from v$timezone_names, and c_gmt is the string 'GMT' v_end_time := v_start_time + 1; -- Add exactly one day to start date 3) return sys_refcursor to caller as:

open o_cur for
select gmt_col, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

However, the developer would like both the gmt_date and the local time in the cursor. First, I attempted to use the exact same conversion method as I had to determine v_start_time, that is:

open o_cur for 
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone v_tz as date ) as local_time, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

However, when compiled, this results in ORA-00905: missing keyword. I attempted to add the single quotes around the "v_tz" like: chr( 39 ) || v_tz || chr( 39 ), but that doesn't work - the proc compiles, but when I open the cursor, I get ORA-01882: timezone region not found. After a bit of experimentation, here are two solutions that allow "at time zone" to work smoothly in sql:

SOLUTION 1:

open o_cur for
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone ( select v_tz from dual ) as date ) as local_time, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

SOLUTION 2:

in package spec:

function echo( i_sound in varchar2 ) return varchar2;
pragma restrict_references( echo, wnps, rnps, wnds, rnds );

in package body:

function echo( i_sound in varchar2 ) return varchar2 is begin return i_sound; end;

in procedure:

open o_cur for
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone echo( v_tz ) as date ) as local_time, some_value
from my_table
where account_id = i_account_id
   and gmt_col between v_start_time and v_end_time;

Performance appears to be comparable for each. The second solution hints at something I've started to do recently, which is to use functions to return "constants" with pragma restrict_references, so I can use the constant values flexibly between pl/sql and sql. For example:

function c_gmt return varchar2; pragma restrict_references( c_gmt, wnds, rnds, wnps, rnps );

select * from v$timezone_names where tzabbrev = c_gmt; select c_gmt from dual; v_start_time := blah blah blah || c_gmt; etc...

Solution

You shouldn't need the extra select from dual. Just putting the variable in parenthesis should do the trick (don't ask me why though):

open o_cur for  
select gmt_col, 
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone (v_tz) as date ) as local_time, some_value 
from my_table 
where account_id = i_account_id 
    and gmt_col between v_start_time and v_end_time;