When preforming a SELECT on an Oracle collection type (TABLE or VARRAY) of timestamp PL/SQL Developer does not repersent the collection corrcetly.
The resulting collection is either returned as: or (after initial execution).
Clicking on ... next to the displays no internal values (as it does with a collection of any other datatype). I am able to deconstruct the collection types to get the scalar (timestamp) values.
Using SQLPlus I am also able to successfully perfrom a select on the collection columns.
The sample scripts below illustrate the behavior described above:
/*
|| SET-UP
*/
-- CREATE VARRAY COLLECTION TYPE
CREATE OR REPLACE
TYPE timestamp_var_t AS VARRAY(50) OF TIMESTAMP(3)
/
-- CREATE TABLE COLLECTION TYPE
CREATE OR REPLACE
TYPE timestamp_tab_t AS TABLE OF TIMESTAMP(3);
/
/*
|| TEST SCENARIOS
*/
-- VARRAY TESTS
-- SELECT collection object form VARRAY
SELECT timestamp_var_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 ) timestamp_coll
FROM dual
/
-- SELECT scalar values from VARRAY
SELECT b.column_value
FROM TABLE(timestamp_var_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 )) b
/
-- TABLE TESTS
---- SELECT collection object form TABLE
SELECT timestamp_tab_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 ) timestamp_coll
FROM dual
/
-- SELECT scalar values from TABLE
SELECT b.column_value
FROM TABLE(timestamp_tab_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 )) b
/
/*
|| CLEAN-UP
*/
-- DROP VARRAY COLLECTION TYPE
DROP TYPE timestamp_var_t
/
-- DROP TABLE COLLECTION TYPE
DROP TYPE timestamp_tab_t
/
/*
|| PL/SQL Developer Details
PL/SQL Developer
Version 7.1.1.1339
01.36228 - Unlimited user license
Windows XP Professional 5.1 Build 2600 (Service Pack 2)
Physical memory : 1,021,688 kB (489,332 available)
Paging file : 2,464,940 kB (2,063,212 available)
Virtual memory : 2,097,024 kB (1,976,240 available)
Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe
Preferences
Session mode: Dual
OCI Library:
Use OCI7: False
*/
The resulting collection is either returned as: or (after initial execution).
Clicking on ... next to the displays no internal values (as it does with a collection of any other datatype). I am able to deconstruct the collection types to get the scalar (timestamp) values.
Using SQLPlus I am also able to successfully perfrom a select on the collection columns.
The sample scripts below illustrate the behavior described above:
/*
|| SET-UP
*/
-- CREATE VARRAY COLLECTION TYPE
CREATE OR REPLACE
TYPE timestamp_var_t AS VARRAY(50) OF TIMESTAMP(3)
/
-- CREATE TABLE COLLECTION TYPE
CREATE OR REPLACE
TYPE timestamp_tab_t AS TABLE OF TIMESTAMP(3);
/
/*
|| TEST SCENARIOS
*/
-- VARRAY TESTS
-- SELECT collection object form VARRAY
SELECT timestamp_var_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 ) timestamp_coll
FROM dual
/
-- SELECT scalar values from VARRAY
SELECT b.column_value
FROM TABLE(timestamp_var_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 )) b
/
-- TABLE TESTS
---- SELECT collection object form TABLE
SELECT timestamp_tab_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 ) timestamp_coll
FROM dual
/
-- SELECT scalar values from TABLE
SELECT b.column_value
FROM TABLE(timestamp_tab_t(SYSTIMESTAMP, SYSTIMESTAMP + .0001, SYSTIMESTAMP + .25, SYSTIMESTAMP + .035 )) b
/
/*
|| CLEAN-UP
*/
-- DROP VARRAY COLLECTION TYPE
DROP TYPE timestamp_var_t
/
-- DROP TABLE COLLECTION TYPE
DROP TYPE timestamp_tab_t
/
/*
|| PL/SQL Developer Details
PL/SQL Developer
Version 7.1.1.1339
01.36228 - Unlimited user license
Windows XP Professional 5.1 Build 2600 (Service Pack 2)
Physical memory : 1,021,688 kB (489,332 available)
Paging file : 2,464,940 kB (2,063,212 available)
Virtual memory : 2,097,024 kB (1,976,240 available)
Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe
Preferences
Session mode: Dual
OCI Library:
Use OCI7: False
*/
