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'
Output 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
It looks like:
PARTITION_NAME HIGH_VAL ---------------- -------------------- SOME_NAME TIMESTAMP' 2010-01-01 00:00:00'
If I use UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
,
I end up getting an ORA-00997: illegal use of LONG datatype
error.
If I use ''||HIGH_VALUE
or to_clob(HIGH_VALUE)
or to_char(HIGH_VALUE)
,
I get an ORA-00932: inconsistent datatypes: expected [DATA_TYPE] got LONG
error
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;
The only way to convert LONG columns is in PL/SQL. Look at the following example, which determines 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 a variable as the LONG type, then SELECT the column INTO the variable. Finally, it is output to the user. SET SERVEROUTPUT ON SIZE 10000 allows spooling from the PUT_LINE to go to the screen.
You can use a similar method to select the LONG into a varchar field. The following example puts the first 2000 characters into 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;