Home » SQL & PL/SQL » SQL & PL/SQL » Prefix data into an existing clob (19.2)
Prefix data into an existing clob [message #685101] Sat, 23 October 2021 10:54 Go to next message
Unclefool
Messages: 21
Registered: August 2021
Junior Member
I have a procedure (see below), which works fine that appends data to a CLOB.

I had a request to prefix new data. The newest data should go into the beginning of the clob and the remaining data should be shifted down in the CLOB without losing any information.

Can you recommend a way to prefix new data with a GENERIC solution so other applications can share this functionality.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE table t(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
c CLOB,
create_date DATE DEFAULT SYSDATE
);
/

insert into t (c) values (
      rpad('X',20,'X')
  );
/


CREATE PROCEDURE lob_append(
  p_clob IN OUT CLOB,
  p_text IN     VARCHAR2
)
AS
  l_text varchar2(32760);
BEGIN
  -- newline each time code is appended for clarity.
  l_text := chr(10)
            || p_text || chr(10)
            || '['||TO_CHAR (SYSDATE, 'MMDDYYYY HH24:MI:SS')||']'||chr(10);

  dbms_lob.writeappend(p_clob, length(l_text), l_text );
END;
/


DECLARE
  l_clob CLOB := empty_clob();
BEGIN
  SELECT c INTO l_clob FROM t WHERE seq_num = 1 FOR UPDATE;

  lob_append(l_clob, rpad('Z',20,'Z'));

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;
/

Re: Prefix data into an existing clob [message #685102 is a reply to message #685101] Sat, 23 October 2021 12:03 Go to previous message
Solomon Yakobson
Messages: 3106
Registered: January 2010
Location: Connecticut, USA
Senior Member
Why not simply:

UPDATE T
   SET C = RPAD('Z',20,'Z') || CHR(10) || C
 WHERE SEQ_NUM = 1
/
SY.
Previous Topic: DBMS_JOB.SUBMIT not appearing
Next Topic: Who call me
Goto Forum:
  


Current Time: Mon Dec 06 21:54:40 CST 2021