I'm trying to get the default value of a column from USER_TAB_COLUMNS. I was able to achieve this using dynamic sql and creating temp table of CLOB column in PL/SQL block.
Below is some sample data:
CREATE TABLE test1 (col1 NUMBER DEFAULT 100);
SET SERVEROUTPUT ON;
DECLARE
v_table_name VARCHAR2(100) := 'TEST1';
v_column_name VARCHAR2(100) := 'COL1';
v_output VARCHAR2(1000);
v_table_exists NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_table_exists
FROM user_tables
WHERE table_name = 'LOB_TBL';
IF v_table_exists = 0 THEN
EXECUTE IMMEDIATE 'create table lob_tbl (lob_val clob )';
ELSE
EXECUTE IMMEDIATE 'truncate table lob_tbl';
END IF;
EXECUTE IMMEDIATE 'INSERT INTO lob_tbl
SELECT to_lob(data_default)
FROM user_tab_columns
WHERE table_name = :1
AND column_name = :2
AND data_default IS NOT NULL' USING v_table_name, v_column_name;
EXECUTE IMMEDIATE 'select lob_val from lob_tbl' INTO v_output;
dbms_output.put_line(v_output);
END;
/
I get the expected output. Can we achieve the same by not using DDL statements in the PL/SQL block (by types?)? At this point, I'm not worried about data length.
PL/SQL procedure successfully completed.
100
Using:
Windows 10 Enterprise
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production