I have a procedure (see test case below), which works fine that appends data to a CLOB. In addition to appending the data in the CLOB I'm encapsulating the VALUE of SYSDATE in tags so I can keep track of when the data was updated in the CLOB.
Though I'm only showing data with 10-20 characters in my example the CLOB can be extremely big and in many cases concatenated within a block before being placed into the CLOB.
-- works fine
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE TABLE t (seq_num NUMBER, c CLOB);
INSERT INTO t (seq_num, c) VALUES (
1, EMPTY_CLOB() || RPAD('*', 4000, '*') || RPAD('*', 4000, '*')
);
/
CREATE OR REPLACE PROCEDURE lob_append(
p_clob IN OUT CLOB,
p_text IN VARCHAR2
)
AS
l_text varchar2(32760);
l_date_string VARCHAR2(50);
BEGIN
select '[' || TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') || ']'
into l_date_string from dual;
-- newline each time code is appended for clarity.
l_text :=chr(10) || l_date_string || chr(10)
|| p_text || chr(10)
|| l_date_string||chr(10);
dbms_lob.writeappend(p_clob, length(l_text), l_text );
END;
/
DECLARE
l_clob CLOB := empty_clob();
lTime date;
BEGIN
lTime := sysdate;
SELECT c INTO l_clob FROM t WHERE seq_num = 1 FOR UPDATE;
lob_append(l_clob, rpad('Z',20,'Z'));
loop
exit when sysdate = lTime + interval '2' second;
end loop;
l_clob := empty_clob();
SELECT c INTO l_clob FROM t WHERE seq_num = 1 FOR UPDATE;
lob_append(l_clob, rpad('Y',10,'Y'));
END;
/
SELECT * from t
SEQ_NUM C
1
[12-01-2021 13:08:58]
ZZZZZZZZZZZZZZZZZZZZ
[12-01-2021 13:08:58]
[12-01-2021 13:09:00]
YYYYYYYYYY
[12-01-2021 13:09:00]
I'm looking to do something like sample UPDATE below( pre append new data) but by calling an API that an application can call. But I'm running into an error I don't understand and can't fix and was hoping you can help.
UPDATE T
SET C = RPAD('A',20,'A') || CHR(10) || C
WHERE SEQ_NUM = 1
/
TRUNCATE TABLE t;
/
INSERT INTO t (seq_num, c) VALUES (
1, EMPTY_CLOB() || RPAD('*', 4000, '*') || RPAD('*', 4000, '*')
);
/
CREATE OR REPLACE PROCEDURE lob_prepend(
p_clob IN OUT CLOB,
p_text IN VARCHAR2
)
AS
l_clob CLOB;
l_len CONSTANT PLS_INTEGER := LENGTH(p_clob);
l_pos PLS_INTEGER;
l_text VARCHAR2(4000);
BEGIN
DBMS_LOB.CREATETEMPORARY(l_clob, FALSE);
l_pos := 1;
LOOP
EXIT WHEN l_pos > l_len OR l_len IS NULL;
DBMS_LOB.WRITEAPPEND(
l_clob,
LEAST(4000, l_len + 1 - l_pos),
TO_CHAR(SUBSTR(p_clob, l_pos, 4000))
);
l_pos := l_pos + 4000;
END LOOP;
DBMS_LOB.WRITE(p_clob, 19, 0, TO_CHAR(SYSDATE, '"["DD-MM-YYYY HH24:MI:SS"]"'));
DBMS_LOB.WRITEAPPEND(p_clob, 1, CHR(10));
IF p_text IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(p_clob, LENGTH(p_text), p_text);
END IF;
DBMS_LOB.WRITEAPPEND(p_clob, 19, TO_CHAR(SYSDATE, '"["DD-MM-YYYY HH24:MI:SS"]"'));
DBMS_LOB.WRITEAPPEND(p_clob, 1, CHR(10));
l_pos := 1;
LOOP
EXIT WHEN l_pos > l_len OR l_len IS NULL;
DBMS_LOB.WRITEAPPEND(
p_clob,
LEAST(4000, l_len + 1 - l_pos),
TO_CHAR(SUBSTR(l_clob, l_pos, 4000))
);
l_pos := l_pos + 4000;
END LOOP;
END;
/
DECLARE
v_clob CLOB;
BEGIN
SELECT c
INTO v_clob
FROM T
WHERE seq_num = 1
FOR UPDATE;
lob_prepend(v_clob, 'AAAAAAAAAAAAAAAAAAAA');
END;
/
ORA-21560: argument 3 is null, invalid, or out of range ORA-06512: at "SYS.DBMS_LOB", line 1149
ORA-06512: at "SQL_OQWECISXRJQQVHFYUMWHYESUK.LOB_PREPEND", line 24
ORA-06512: at line 10
ORA-06512: at "SYS.DBMS_SQL", line 1721