À l'aide de l'Oracle “à temps”, la zone de fonction en sql - problème et solution

J'ai une table avec une colonne de date que je sais, c'est stockée dans GMT. J'ai une procédure qui accepte une date d'entrée et un ID de compte. La procédure:
1) obtient le numéro de compte du fuseau horaire (stockée dans le tableau de compte)
2) détermine le début et la fin de la gamme GMT comme suit:
v_start_time := cast( from_tz( cast( i_date comme timestamp ), v_tz ) au moment de la zone c_gmt date ); -- où i_date est entrée, v_tz est "US/Eastern" ou de tout autre tzname from v$timezone_names, et c_gmt est la chaîne de caractères 'EME'
v_end_time := v_start_time + 1; -- Ajouter exactement un jour à la date de début
3) retour sys_refcursor de l'appelant, comme:

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;

Cependant, les développeurs souhaitent que les deux la gmt_date et le heure locale, dans le curseur. Tout d'abord, j'ai tenté d'utiliser exactement la même méthode de conversion que j'ai eu à déterminer v_start_time, c'est:

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;

Cependant, lors de la compilation, cela se traduit par ORA-00905: mot-clé manquant. J'ai tenté d'ajouter des guillemets simples autour de la "v_tz" comme: chr( 39 ) || v_tz || chr( 39 ), mais qui ne fonctionne pas - le proc compile, mais quand j'ai ouvert le curseur, j'obtiens l'erreur ORA-01882: le fuseau horaire de la région n'a pas trouvé. Après un peu d'expérimentation, voici deux solutions qui permettent de "au temps", la zone de travail en douceur dans 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:

dans le paquet spec:

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

dans le corps de package:

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

dans la procédure:

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 semble être comparable pour chaque. La deuxième solution allusion à quelque chose que j'ai commencé à le faire récemment, qui consiste à utiliser des fonctions pour le retour des "constantes" avec pragma restrict_references, si je peux utiliser les valeurs de constante de manière flexible entre le pl/sql et sql. Par exemple:

fonction c_gmt return varchar2;
pragma restrict_references( c_gmt, wnds, rnds, wnps, rnp );

select * from v$timezone_names où tzabbrev = c_gmt;
sélectionnez c_gmt from dual;
v_start_time := bla bla bla || c_gmt;
etc...