Convert LONG format to DATE without a function

user_5202138 0
Viewed 530

The column HIGH_VALUE has data similar to below, of type LONG, and always has the same length: 

TIMESTAMP' 2019-01-30 00:00:00'

How can I convert it to a DATE type without using a function?

My overall goal is to create a result set which can then be used as an inner query for other aggregations. For example, I would like to be able to sum the number of rows over a year according to the date produced by converting the HIGH_VALUE column to a date.

I have only read permissions on this database and therefore cannot create functions. I've seen other solutions on StackOverflow and other sites, but they all require creating a function.

ALL_TAB_PARTITIONS is a standard built-in Oracle table and therefore I'm not including the table structure. In case that's an issue, please let me know and I will create an example table.

An example query and the data one row that results from this query follows. Note that I cannot create tables on this database so I will also need an a method that works without creating a temporary table.

Insert into EXPORT_TABLE (TABLE_OWNER,TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS) 
VALUES ('TO','TN','SYS_P201709','TIMESTAMP'' 2019-01-30 00:00:00''',5053133);

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS
from ALL_TAB_PARTITIONS;
oracle18c oracle12c oracle
3 Answers
user_50708790

For the conversion of a LONG type ( HIGH_VALUE ) to TIMESTAMP, One option is to use dynamic sql and perform your insert through an anonymous block. No Procedure or function is required.

DECLARE
     tstamp TIMESTAMP;
BEGIN
     FOR rec IN ( SELECT table_owner,table_name,partition_name,high_value,num_rows
                  FROM all_tab_partitions
                  WHERE ROWNUM < 5
                ) LOOP
          EXECUTE IMMEDIATE 'BEGIN :dt := '
                            || rec.high_value
                            || '; END;'
             USING OUT tstamp;  --assign the long to an external timestamp variable
     INSERT INTO export_table (
               table_owner,table_name,partition_name,high_value,num_rows
          ) VALUES (rec.table_owner, 
            rec.table_name, rec.partition_name, tstamp, rec.num_rows
          );

     END LOOP;
END;
/

AS @APC commented, There's also a solution using Pure SQL, which uses a slightly complex Xml expression.

user_79985910

Combining the pure SQL solution from APC's comment with the enhancements in Oracle 12 to allow functions to be declared in WITH clauses and Kaushik Nayak's method of using EXECUTE IMMEDIATE to convert the string value to a date then you can get this:

Oracle Setup - Test Table & Data:

CREATE TABLE EXPORT_TABLE (
  TABLE_OWNER     VARCHAR2(30),
  TABLE_NAME      VARCHAR2(30),
  PARTITION_NAME  VARCHAR2(30),
  HIGH_VALUE      LONG,
  NUM_ROWS        INTEGER
); 

INSERT INTO EXPORT_TABLE VALUES ( 'TO', 'TN', 'PN', 'TIMESTAMP ''2019-06-26 12:34:56''', 12345 );

Query:

WITH FUNCTION clobToDate( value IN CLOB ) RETURN DATE
  IS
    ts DATE;
  BEGIN
    EXECUTE IMMEDIATE 'BEGIN :ts := ' || value || '; END;' USING OUT ts;
    RETURN ts;
  END;
SELECT TABLE_OWNER,
       TABLE_NAME,
       PARTITION_NAME,
       clobToDate(
         EXTRACTVALUE(
           dbms_xmlgen.getxmltype(
             'SELECT high_value'
             || ' FROM  EXPORT_TABLE'
             || ' WHERE TABLE_OWNER    = ''' || t.table_owner || ''''
             || ' AND   TABLE_NAME     = ''' || t.table_name || ''''
             || ' AND   PARTITION_NAME = ''' || t.partition_name || ''''
           ),
           '//text()'
         )
       ) AS HIGH_VALUE,
       NUM_ROWS
FROM   EXPORT_TABLE t;

Output:

TABLE_OWNER | TABLE_NAME | PARTITION_NAME | HIGH_VALUE          | NUM_ROWS
:---------- | :--------- | :------------- | :------------------ | -------:
TO          | TN         | PN             | 2019-06-26 12:34:56 |    12345

db<>fiddle here

Update: If you want to aggregate some columns then:

WITH FUNCTION clobToDate( value IN CLOB ) RETURN DATE
  IS
    ts DATE;
  BEGIN
    EXECUTE IMMEDIATE 'BEGIN :ts := ' || value || '; END;' USING OUT ts;
    RETURN ts;
  END;
SELECT table_owner,
       table_name,
       MAX( high_value ) AS max_high_value,
       SUM( num_rows ) AS total_rows
FROM   (
  SELECT TABLE_OWNER,
         TABLE_NAME,
         PARTITION_NAME,
         clobToDate(
           EXTRACTVALUE(
             dbms_xmlgen.getxmltype(
               'SELECT high_value'
               || ' FROM  EXPORT_TABLE'
               || ' WHERE TABLE_OWNER    = ''' || t.table_owner || ''''
               || ' AND   TABLE_NAME     = ''' || t.table_name || ''''
               || ' AND   PARTITION_NAME = ''' || t.partition_name || ''''
             ),
             '//text()'
           )
         ) AS HIGH_VALUE,
         NUM_ROWS
  FROM   EXPORT_TABLE t
)
GROUP BY table_owner, table_name;

db<>fiddle here

user_15092640
Related