This discussion is archived
2 Replies Latest reply: Nov 30, 2012 4:50 AM by 740719 RSS

Timestamp in Run PL/SQL

740719 Newbie
Currently Being Moderated
Hello everyone,

Oracle 11gR2
SQL Developer 3.2.09

We have a stored procedure in a package that returns a refcursor. One of the columns (say XYZ) is timestamp with local time zone. When I right click on the package and select Run I get the Run PL/SQL window where I enter the input parameters and run the procedure. The result is displayed in the "Output Variables" tab. When I select the refcursor I can see data from all the columns except for XYZ. It displays oracle.sql.TIMESTAMPLTZ@XXXXXX (where XXXXXX is a hexadecimal number).

If I cast the column XYZ to timestamp with time zone then I get oracle.sql.TIMESTAMPTZ@XXXXXX

If I cast it to just timestamp then I can see the data.

Without the casting the data is available for our other applications. For some reason SQL Developer is not able to display it.

I even added couple of execute immediate statements to set the time zone and timestamp format. Still no use.

Anybody know of a fix for this?

Thanks
Balaji
  • 1. Re: Timestamp in Run PL/SQL
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    This is a bug. It's been reproduced in-house, and fixed for our next release.

    I reproduced it with this test case

    create or replace
    FUNCTION SYSREF_TIMESTAMP
    RETURN sys_refcursor
    IS
    l_curvar sys_refcursor;
    BEGIN
    OPEN l_curvar FOR 'SELECT systimestamp from dual';

    RETURN l_curvar;
    END SYSREF_TIMESTAMP;

    Execute, this is displayed in the output for the ref cursor
    oracle.sql.TIMESTAMPTZ@7449369e

    Edited by: Jeff Smith SQLDev PM on Nov 28, 2012 10:47 PM
  • 2. Re: Timestamp in Run PL/SQL
    740719 Newbie
    Currently Being Moderated
    Thanks Jeff. Its the same for both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE return types.

    Looking forward for the fix.

    Balaji Calluru

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points