Pages

Thursday, February 9, 2023

Convert LONG to VARCHAR2 in Oracle

Example of using Function co convert LONG to VARCHAR2

CREATE OR REPLACE FUNCTION LONG_2_VARCHAR 
   (p_table_name IN VARCHAR2,
    p_partition_name IN  VARCHAR2) RETURN VARCHAR2 IS
 v_ret_str VARCHAR2(32767);
BEGIN
  SELECT high_value INTO v_ret_str 
    FROM USER_TAB_PARTITIONS 
   WHERE table_name = p_table_name 
     AND partition_name = p_partition_name;

  RETURN REPLACE (v_ret_str,'''','');

EXCEPTION 
  WHEN OTHERS THEN
    RETURN 'Unexpected Error! Unable to Convert Long to Varchar2 !';
END;
/

Usage example:
SELECT table_name, 
       partition_name, 
       LONG_2_VARCHAR(table_name, partition_name) high_value
  FROM USER_TAB_PARTITIONS 
WHERE table_name = 'MY_TABLE';