Mit dem Oracle "bei time zone" - Funktion im sql - problem und Lösung

Ich habe eine Tabelle mit einer Datum-Spalte, die ich kenne, ist gespeichert in GMT. Ich habe eine Prozedur, die ein Datum eingegeben werden Eingabe-und eine account-ID. Das Verfahren:
1) wird die Konto-ID timezone (gespeichert in der Tabelle Konto)
2) bestimmt den start-und end-Bereich in GMT, wie folgt:
v_start_time := cast( from_tz( cast( i_date als timestamp ), v_tz ) Zeitzone c_gmt als Datum ); -- wo i_date ist input, v_tz 'US/Eastern' oder andere tzname aus v$timezone_names, und c_gmt ist der string 'GMT'
v_end_time := v_start_time + 1; -- geben Sie genau einen Tag zu beginnen Datum
3) return sys_refcursor Anrufer:

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;

Aber der Entwickler möchte sowohl das gmt_date und der lokalen Zeit in den cursor. Zuerst habe ich versucht, verwenden Sie die exakt gleiche Umwandlung Methode, wie ich Sie hatte, um zu bestimmen, v_start_time, das ist:

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;

Jedoch, wenn Sie erstellt, dies führt zu ORA-00905: missing keyword. Ich habe versucht, fügen Sie die Anführungszeichen um den "v_tz" wie: chr( 39 ) || v_tz || chr( 39 ), aber das funktioniert nicht - der proc kompiliert, aber wenn ich öffnen Sie die cursor, bekomme ich ORA-01882: timezone region nicht gefunden. Nach ein bisschen Experimentieren, hier sind zwei Lösungen, die es ermöglichen, "Zeitzone", um ein reibungsloses funktionieren in sql:

LÖSUNG 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;

LÖSUNG 2:

im Paket spec:

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

im Paket, Karosserie:

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

in-Verfahren:

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;

Leistung zu sein scheint vergleichbar für jeden. Die zweite Lösung deutet auf etwas, was ich begonnen habe, zu tun, vor kurzem, was ist die Verwendung von Funktionen für die Rückgabe von "Konstanten" mit pragma restrict_references, damit ich die Konstanten Werte an, die flexibel zwischen pl/sql und sql. Zum Beispiel:

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

select * from v$timezone_names wo tzabbrev = c_gmt;
wählen Sie c_gmt von dual;
v_start_time := bla bla bla || c_gmt;
etc...

InformationsquelleAutor Thomas Gnade | 2010-11-11