Home » SQL & PL/SQL » SQL & PL/SQL » cannot use variable in PL/SQL code for Timezone (Oracle Database 11g Release 11.2.0.1.0 - 64bit Production on Linux)
cannot use variable in PL/SQL code for Timezone [message #568329] Wed, 10 October 2012 18:02 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,
This might be a simple one,
but I tried this for some time and couldn't find it..

I've written this code to present the problem:



declare
region_date varchar2(100);
region varchar2(100);
offset_result varchar2(100);

begin
 region_date := '2013-04-07 01:59 Australia/Sydney';
 region := 'Australia/Sydney';
  select 
to_char(
    from_tz(cast(to_date(substr(region_date,1,17),'yyyy-mm-dd hh24:mi') as timestamp), region)
    AT TIME ZONE region , 'tzh:tzm')
    into offset_result
from dual;

dbms_output.put_line(offset_result);
end;




What I want to do is to use the variable "region" for the timezone specification as well,
And I seem to be unable to, and don't understand why.....

Can anybody explain why when I get an "ORA-00907: missing right parenthesis" with this value,
Whereas, it works when I put "AT TIME ZONE 'Australia/Sydney'" there?

Thanks in advance,
Andrey

[Updated on: Wed, 10 October 2012 18:16]

Report message to a moderator

Re: cannot use variable in PL/SQL code for Timezone [message #568337 is a reply to message #568329] Thu, 11 October 2012 00:24 Go to previous messageGo to next message
sandeepgujje
Messages: 28
Registered: January 2012
Location: India
Junior Member
Hi,

Hope you are looking for the below.

DECLARE
   region_date     VARCHAR2 (100);
   region          VARCHAR2 (100);
   offset_result   VARCHAR2 (100);
   v_qry           VARCHAR2 (2000);
BEGIN
   region_date := '2013-04-07 01:59 Australia/Sydney';
   region := 'Australia/Sydney';
   v_qry :=
         'SELECT TO_CHAR(
    FROM_TZ(CAST(TO_DATE(SUBSTR('||''''||region_date||''''||',1,17),''yyyy-mm-dd hh24:mi'') AS TIMESTAMP),'
      ||''''||region||''''
      || ')
    AT TIME ZONE '
      ||''''||region||''''
      || ' , ''tzh:tzm'') from dual';

   EXECUTE IMMEDIATE v_qry
                INTO offset_result;

   DBMS_OUTPUT.put_line (offset_result);
END;


Hope this helps..!!
Re: cannot use variable in PL/SQL code for Timezone [message #568351 is a reply to message #568337] Thu, 11 October 2012 02:15 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi sandeepgujje,
Very nice workaround. Thank you very much!!! this is very helpful to me.

However, just for understanding, can anyone explain why this variable can be used for the "from_tz" function,
Whereas, It cannot be used for the time zone specification?

Re: cannot use variable in PL/SQL code for Timezone [message #568362 is a reply to message #568351] Thu, 11 October 2012 02:55 Go to previous message
_jum
Messages: 489
Registered: February 2008
Senior Member
Or just simple:
DECLARE
  region_date   varchar2(100);
  region        varchar2(100);
  offset_result varchar2(100);

BEGIN
  region_date := '2013-04-07 01:59 Australia/Sydney';
  region := 'Australia/Sydney';
  offset_result :=  to_char(
    from_tz(cast(to_date(substr(region_date,1,17),'yyyy-mm-dd hh24:mi') as timestamp), region)
      AT TIME ZONE region,'tzh:tzm'); 
  dbms_output.put_line(offset_result);
END;
Previous Topic: Connect role in dba_role_privs
Next Topic: Export table data into text file through procedure/package (8 merged)
Goto Forum:
  


Current Time: Fri Aug 01 12:04:38 CDT 2014

Total time taken to generate the page: 0.11536 seconds