by Jack

Dynamic Oracle Insert

The SQL for this was a little more difficult than in my post for Microsoft SQL Server, this is because we are moving to a type that supports Unicode - so from CLOBs to NCLOBs. This means that all the CLOB fields in the insert needed to be passed with the TO_CLOB() function.

The following SQL shows how I achieved my result - note this will only work on Oracle 10g and higher as it uses some of the XML functionality.

move_to_var VARCHAR2(30) := 'TO_TABLE_NAME';
move_from_var VARCHAR2(6) := 'FROM_TABLE_NAME';
table_columns VARCHAR2(2000) := '';
table_columns_clob_values VARCHAR2(1000) := '';
table_columns_clob VARCHAR2(1000) := '';
clob_count NUMBER := 0;
insert_command VARCHAR2(2100) := 'insert into ';
-- first get normal columns, then clobs
-- build initial insert gubbins
insert_command := insert_command || move_to_var || ' (';
in the event there are only clobs in the table, this sql will not work.
we cheat slightly here, using an ORacle 10g xml function to generate the columns list
this could be achieved with a long funcion, but this is more elegant.
The undocumented command wm_concat() could be used, but is not supported by Oracle
-- for non-clob columns
SELECT RTrim(xmlagg(xmlelement(a,column_name || ',').extract('//text()')),',')
INTO table_columns FROM user_tab_columns
WHERE table_name = move_from_var
AND data_type != 'CLOB' AND data_type != 'NCLOB'
GROUP BY Table_name;
-- for nclobs
-- check to make sure nclobs exist or else you  get sql errors
SELECT count(*) INTO clob_count
FROM user_tab_columns
WHERE table_name = move_from_var
AND (data_type = 'CLOB' OR data_type = 'NCLOB');

-- if nclobs exist then get the column names
IF clob_count !=0 THEN
--need 2 lists - one for values, one for columns - due to TO_CLOB
SELECT RTrim(xmlagg(xmlelement(a,'TO_NCLOB(' ||column_name || '),').extract('//text()')),',')
INTO table_columns_clob_values FROM user_tab_columns
WHERE table_name =  move_from_var
AND (data_type = 'CLOB' OR data_type = 'NCLOB')
GROUP BY Table_name;
-- second column string without TO_CLOB
SELECT RTrim(xmlagg(xmlelement(a,column_name || ',').extract('//text()')),',')
INTO table_columns_clob FROM user_tab_columns
WHERE table_name = move_from_var
AND (data_type = 'CLOB' OR data_type = 'NCLOB')
GROUP BY Table_name;
-- now add , to begin of string to ensure sql runs smoothly
table_columns_clob := ',' || table_columns_clob;
table_columns_clob_values := ',' || table_columns_clob_values;

-- build final command
insert_command := insert_command || table_columns || table_columns_clob
|| ') select ' || table_columns || table_columns_clob_values || ' from '
|| move_from_var;

-- Execute final insert
-- running this as Execute Immediate allows for dynamic build
-- error catching should be inserted at this point
Execute Immediate insert_command;
-- Uncomment these lines to test output
--DBMS_OUTPUT.PUT_LINE (insert_command);
-- DBMS_OUTPUT.PUT_LINE (table_columns_clob_values);