2 Replies Latest reply: Nov 30, 2012 6:50 AM by Balaji.Calluru RSS

    Timestamp in Run PL/SQL

    Balaji.Calluru
      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