Oracle prefix data into a clob with a procedure-CodePudding

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.


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 (' ')
/



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

-- Note there can be any data,
-- multiple lines, newlines, between the 
-- encapsulating tags

SELECT * from t


SEQ_NUM C   CREATE_DATE
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]
12012021 13:08:58

I'm looking to do something like this but by calling a procedure similar to the one posted above but I want the new data to be prefixed instead of appended and I want it to be encapsulated within SYSDATE tags


UPDATE T
   SET C = RPAD('A',20,'A') || CHR(10) || C 
 WHERE SEQ_NUM = 1
/

CodePudding user response：

Like this:

UPDATE T
SET   C = TO_CHAR(SYSDATE, '"["DDMMYYYY-HH24:MI:SS"]"') || CHR(10)
          || RPAD('A',20,'A') || CHR(10)
          || TO_CHAR(SYSDATE, '"["DDMMYYYY-HH24:MI:SS"]"') || CHR(10)
          || CHR(10)
          || C
WHERE SEQ_NUM = 1
/

db<>fiddle here

As a procedure:

CREATE OR REPLACE PROCEDURE lob_prepend(  
  p_clob IN OUT CLOB,  
  p_text IN     VARCHAR2  
)  
AS  
  l_len     CONSTANT PLS_INTEGER := LENGTH(p_clob);  
  l_ts_len  CONSTANT PLS_INTEGER := LENGTH('[DDMMYYYY-HH:mm:SS]');  
  l_cr_len  CONSTANT PLS_INTEGER := LENGTH(CHR(10));  
  l_str_len CONSTANT PLS_INTEGER := COALESCE(LENGTH(p_text), 0);  
  l_offset  CONSTANT PLS_INTEGER := 2*l_ts_len   3*l_cr_len   l_str_len;  
BEGIN  
  -- Shuffle all the text within the clob.  
  DBMS_LOB.COPY( 
    dest_lob    => p_clob, 
    src_lob     => p_clob, 
    amount      => l_len, 
    dest_offset => l_offset   1, 
    src_offset  => 1 
  ); 
 
  -- Put the header at the beginning.  
  DBMS_LOB.WRITE(  
    p_clob,  
    l_ts_len,  
    1,  
    TO_CHAR(SYSDATE, '"["DDMMYYYY-HH24:MI:SS"]"')  
  );  
  DBMS_LOB.WRITE(  
    p_clob,  
    l_cr_len,  
    1   l_ts_len,  
    CHR(10)  
  );  
  IF p_text IS NOT NULL THEN  
    DBMS_LOB.WRITE(  
      p_clob,  
      l_str_len,  
      1   l_ts_len   l_cr_len,  
      p_text  
    );  
  END IF;  
  DBMS_LOB.WRITE(  
    p_clob,  
    l_cr_len,  
    1   l_ts_len   l_cr_len   l_str_len,  
    CHR(10)  
  );  
  DBMS_LOB.WRITE(  
    p_clob,  
    l_ts_len,  
    1   l_ts_len   2*l_cr_len   l_str_len,  
    TO_CHAR(SYSDATE, '"["DDMMYYYY-HH24:MI:SS"]"')  
  );  
  DBMS_LOB.WRITE(  
    p_clob,  
    l_cr_len,  
    1   2*l_ts_len   2*l_cr_len   l_str_len,  
    CHR(10)  
  );  
END; 
/

SQL Fiddle here