Hi,
In looking at some procedures on a database I work with I found code that didn't look right to me: somehow concatenating athe contents of a varrary into a CLOB and using that as the source of execute immediate. I did my best to put together something I could try to execute, and it looks like this:
create type DISBUR_ARRAY IS VARRAY(100000) OF number(10);
create or replace procedure fixstuff(disbids IN clob) as
sqlstr clob;
BEGIN
sqlstr := 'create table testx as select short_desc from table_maintain where table_maintain_id in (' || disbids || ');';
execute immediate sqlstr;
end;
declare
V_DISBUR_IDS DISBUR_ARRAY := DISBUR_ARRAY(); --CLOB;
i number(2);
begin
for i in 1 .. 5 loop
V_DISBUR_IDS.EXTEND;
V_DISBUR_IDS(V_DISBUR_IDS.COUNT) := i+80;
end loop;
fixstuff(v_disbur_ids);
end;
My little attempt doesn't work; it complains about the type of argument to fixstuff (procedure declared as a clob, trying to pass it the disbur_array). But the original, which I have tried to simplify, is in production, the procedures are showing as being compiled, and it really is passing that disbur_array to procedure that expects a CLOB. It might be a version difference: the production version is running 11.2 and I'm running 19.0 – maybe type checking is stricter? Or did I maybe mess up some little thing in building my simplified version? Interested in knowing partly because if it's a version thing, it's something that's going to have to change very soon.
Thanks.