Results 1 to 3 of 3

Thread: Dbtimezone

  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Dbtimezone

    Hi

    I am facing a rather strange issue.

    select dbtimezone from dual;
    gives me +05:30 as the output.

    When the above query is used in
    a function it says DBTIMEZONE must be
    declared.

    So I did a test by using the following stt.

    begin
    select dbtimezone from dual;
    end;

    This also gives the same error.

    How does it make dbtimezone not
    recognized when enclosed in a begin .. end;

    Will appreciate help extended.

    Regards
    Ramkumar

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: Dbtimezone

    Two things:
    1. there is an undocumented / unpublished Oracle bug which requires you be connect as SYS or a SYSDBA user for your function to be created successfully. This is true for all timezone functions created by package STANDARD.
    2. when using SELECT inside a BEGIN/END block you must assign all selected values into variables.

    SQL> connect sys/pass@orcl as sysdba
    connected.

    SQL> create or replace function mydbtimezone
    return varchar2
    is v1 varchar2(7);
    begin
    select dbtimezone INTO v1 from dual;
    return v1;
    end;
    /
    function created.

    SQL> select mydbtimezone from dual;
    '-05:00'

    If you connect as any other user and run the above Create Function statement, it will fail.

    Interestingly, the function created above is the same as the one Oracle creates in the STANDARD package. I think the source code is in %ORACLE_HOME%\rdbms\admin\stdbody.sql.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Feb 2004
    Posts
    8

    Thank you

    Got it. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •