I have built the following procedure to read data into a CLOB (see code example below). Now I would like to have the same functionality to do that for a LONG column.
I know that LONGs are deprecated in ORACLE but for my particular case it is neccessary to stay with LONG (for the moment).
CREATE OR REPLACE PROCEDURE my.p_update_proc (
p_table_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_pattern IN VARCHAR2
)
as
l_sql varchar2(4000);
l_bfile bfile := bfilename('MY_DIR',p_table_name||'.'||p_pattern||'.txt');
l_data clob;
l_soffset NUMBER := 1;
l_doffset NUMBER := 1;
l_lang NUMBER := 0;
l_warn NUMBER;
BEGIN
DBMS_LOB.createtemporary (lob_loc => l_data,cache => TRUE,
dur => DBMS_LOB.call);
dbms_lob.fileopen( l_bfile, DBMS_LOB.file_readonly );
DBMS_LOB.loadclobfromfile(l_data, l_bfile, DBMS_LOB.LOBMAXSIZE,
l_soffset, l_doffset, 0, l_lang, l_warn );
dbms_lob.fileclose(l_bfile);
dbms_output.put_line(l_sql); -- to debug/trace your dynamic statement
l_sql := 'update ' || p_table_name || ' set '|| p_column_name || '='
|| ':1' || ' where ' || p_column_name || ' like ' || '''' || p_pattern || ''
';
execute immediate l_sql using l_data;
END; --Procedure
/
I'm aware that the constraint "like 'pattern'" doesn't work for LONGs and I could live with a modification such that I test for a different field in the table row for the identification of that row (and discard it later).
Changing a CLOB back into a LONG (by an ALTER TABLE MODIFY COLUMN) doesn't work either, so it's crucial to me that I find a way to fill the file content into a LONG into an already existing table.
