OraFAQ Forum: SQL & PL/SQL » cannot use variable in PL/SQL code for Timezone

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)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

cannot use variable in PL/SQL code for Timezone [message #568329]

Wed, 10 October 2012 18:02

Andrey_R
Messages: 137
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

sandeepgujje
Messages: 22
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..!!

Report message to a moderator

Re: cannot use variable in PL/SQL code for Timezone [message #568351 is a reply to message #568337]

Thu, 11 October 2012 02:15

Andrey_R
Messages: 137
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?

Report message to a moderator

Re: cannot use variable in PL/SQL code for Timezone [message #568362 is a reply to message #568351]

Thu, 11 October 2012 02:55

_jum
Messages: 453
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;

Report message to a moderator

Previous Topic:	Connect role in dba_role_privs
Next Topic:	Export table data into text file through procedure/package (8 merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 22:59:49 CDT 2013

Total time taken to generate the page: 0.29281 seconds