How do you convert DEBT data to TIMESTAMP or VARCHAR2?

Question

I need something like:

SELECT PARTITION_NAME, to_char(LONG_TO_TIMESTAMP(HIGH_VALUE), 'MM/DD/YYYY HH24:MI:SS') AS HIGH_VAL FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'TABLE_NAME' 

The result should look something like this:

  PARTITION_NAME HIGH_VAL
 ---------------- --------------------
 SOME_NAME 01/01/2010 00:00:00 

When I run:

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS

Seem to be:

  PARTITION_NAME HIGH_VAL
 ---------------- --------------------
 SOME_NAME TIMESTAMP '2010-01-01 00:00:00' 

If I use UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) ,
I get error ORA-00997: illegal use of LONG datatype .

If I use ''||HIGH_VALUE or to_clob(HIGH_VALUE) or to_char(HIGH_VALUE) ,
I get error ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONG

My working function thanks to shobi:

 CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE ( p_TableName IN VARCHAR2, p_PatitionName IN VARCHAR2 ) RETURN DATE IS v_LongVal LONG; BEGIN SELECT HIGH_VALUE INTO v_LongVal FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = p_TableName AND PARTITION_NAME = p_PatitionName; RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS'); END GET_HIGH_VALUE_AS_DATE; 

SQL looks like

 SELECT PARTITION_NAME, GET_HIGH_VALUE_AS_DATE(TABLE_NAME, PARTITION_NAME) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME LIKE 'TABLE_NAME' AND ROWNUM < 2; 

Answer 1

The only way to convert LONG columns is to PL / SQL. Take a look at the following example defining the length of the LONG field:

 SET SERVEROUTPUT ON SIZE 10000; DECLARE long_var LONG; BEGIN SELECT text_column INTO long_var FROM table_with_long WHERE rownum < 2; DBMS_OUTPUT.PUT_LINE('The length is '||LENGTH(long_var)); END; 

Basically, you define the variable as a LONG type, then select the INTO column of the variable. Finally, it is displayed to the user. SET SERVEROUTPUT ON SIZE 10000 allows you to wind from PUT_LINE to the screen.

You can use a similar method to select LONG in the varchar field. The following example puts the first 2000 characters in TABLE_B, which for our purposes has one column, TEXT_FIELD:

 DECLARE long_var LONG; var_var VARCHAR2(2000); BEGIN SELECT text_column INTO long_var FROM table_with_long WHERE rownum < 2; var_var := substr(long_var,1,2000); INSERT INTO table_b VALUES (var_var); END; 

Answer 2

You could also do the following:

 CREATE TABLE long_to_clob ( partition_name VARCHAR2(30) , high_value_clob CLOB , high_value_text VARCHAR2(4000) ); INSERT INTO long_to_clob (partition_name, high_value_clob) SELECT partition_name, TO_LOB(high_value) FROM user_tab_partitions; UPDATE long_to_clob SET high_value_text = DBMS_LOB.SUBSTR(high_value_clob, 1, 4000); 

The only caveat using TO_LOB is that it should be used in an INSERT statement as described above. On the other hand, there are fewer restrictions on the use of the DBMS_LOB package. Obviously, once you get the value in the VARCHAR2 column, you can do whatever you want with it.

Hope this helps.