Oracle rewrite a procedure to be generic

Question

I have a procedure, which is working well that other applications want to use.

As you can see table and column names are hardcoded into the procedure, which makes it difficult to share the code. Is there a way this can be rewritten so it could be shared. I want to avoid passing in more values if possible as it will make the code awkward and klunky.

Any suggestions would be greatly appreciated.

 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 or replace procedure lob_append( p_id in number, p_text in varchar2 )
 as
 l_clob clob;
 l_text varchar2(32760);
 l_system_date_time VARCHAR2(50); 
begin

 select c into l_clob from t where seq_num = p_id for update;

SELECT TO_CHAR (SYSDATE, 'MMDDYYYY HH24:MI:SS') into l_system_date_time from dual;

-- newline each time code is appended for clarity.

 l_text := chr(10) || p_text || chr(10) || '['||l_system_date_time||']'||chr(10);

 dbms_lob.writeappend( l_clob, length(l_text), l_text );
 end;
/

exec lob_append(1, rpad('Z',20,'Z'));

exec lob_append(1, rpad('Y',10,'Y'));


select * from t;
 /

Answer 1

Don't SELECT from the table in the procedure, instead pass the CLOB into the procedure; that way you do not need to use dynamic SQL in the procedure:

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

Then, when you want to call it:

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

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

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

  lob_append(l_clob, rpad('Y',10,'Y'));
END;
/

db<>fiddle here