Oracle rewrite a procedure to be generic-CodePudding

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

CodePudding user response：

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

CodePudding user response：

This is how I understood the question.

Is there a way this can be rewritten so it could be shared.

Yes, by using dynamic SQL. You'd compose all statements you need into a varchar2 local variable and then run it using execute immediate. It means that you'd, actually, have to pass table/column names into the procedure so that you'd be able to use them in a "generic" way.

Pay attention to SQL injection, i.e. bad people might try to misuse that code. Read more about DBMS_ASSERT package.

I want to avoid passing in more values if possible as it will make the code awkward and klunky.

Well, that's right the opposite of what you'll have to do. If the procedure has to be "generic", you have to pass table/column names (as I already said), so that means more parameters than you have now.

Is it worth it? I don't like dynamic SQL. Although it seems that it "solves" some problems, it brings in another. Code is difficult to maintain and debug. Basically, there's no such thing as free lunch. There are benefits, and there are drawbacks.