SQL & PL/SQL

sparc
11.2.0.2

A partitioned table will have a record in the dba_tab_partitions view. This HIGH_VALUE column is a LONG datatype an contains the values which I am trying to convert to a date. Assumptions here are that we know that the partitioning strategy is a range partitioned table based on a TIMESTAMP column.

My question is how to effectively convert this to a date to which data arithmetic can be applied?

--
-- this included to explain the ddl behind the partitioned table (in example)
--

...
"PART_MON_YEAR" ("START_DATE" TIMESTAMP (6))
...
  PARTITION BY RANGE ("START_DATE")
 (PARTITION "Y1"  VALUES LESS THAN (TIMESTAMP ' 2011-01-01 00:00:00')
...


CREATE TABLE T_LONG (val LONG);

DECLARE
   x   LONG;
BEGIN
   SELECT   high_value
     INTO   x
     FROM   dba_tab_partitions
    WHERE       table_owner = 'TESTUSER1'
            AND table_name = 'PART_MON_YEAR' --known table to have range partition on TIMESTAMP
            AND partition_name = 'Y1';

   INSERT INTO t_long
     VALUES   (SUBSTR (x, 1, 2000));

   COMMIT;
END;

SELECT   * FROM t_long;
--TIMESTAMP' 2011-01-01 00:00:00'

You can select this value out right:

select TIMESTAMP' 2011-01-01 00:00:00' from dual;

--1/1/2011 12:00:00.000000000 AM

SELECT   TO_CHAR (TIMESTAMP ' 2011-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss')
            blah
  FROM   DUAL;

--2011-01-01 12:00:00

SELECT   TO_DATE (
            TO_CHAR (TIMESTAMP ' 2011-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss'),
            'yyyy-mm-dd hh:mi:ss'
         )
            blah
  FROM   DUAL;

--1/1/2011 12:00:00 PM

However, if I need to make this conversion using NDS whereby I've tabled the TIMESTAMP' 2011-01-01 00:00:00' value as a VARCHAR2...I'm finding difficulty in squeezing out the date. Why did I table the TIMESTAMP' 2011-01-01 00:00:00' as a varchar2? We had a requirement to effectively "see" the HIGH_VALUE for a particular range partitioned table. Its tabled by selecting the HIGH_VALUE into a LONG datatype and then substr(blah, 1, 2000) on the insert. Selecting off of the dba_tab_partitions view is not within the client's security model.

DECLARE
   x       VARCHAR2 (128);
   v_sql   VARCHAR2 (1024);
   y       VARCHAR2 (32);
BEGIN
   SELECT   val INTO x FROM t_long;

   v_sql :=
         'SELECT    TO_CHAR (:1, '
      || ''''
      || 'mm/dd/yyyy hh:mi:ss'
      || ''''
      || ') FROM   dual';

   DBMS_OUTPUT.put_line (x);

   DBMS_OUTPUT.put_line (v_sql);

   EXECUTE IMMEDIATE v_sql INTO   y USING x;

   DBMS_OUTPUT.put_line (y);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

--TIMESTAMP' 2011-01-01 00:00:00'
--SELECT    TO_CHAR (:1, 'mm/dd/yyyy hh:mi:ss') FROM   dual
--ORA-01722: invalid number

I'm looking for possible solutions...pseudo code is fine...I can run down the syntax. Moreover, is this possible?

Thank you for any possible suggestions or guidance.

-abe
Boulder, Colorado