Oracle function with CLOB as input parameter and spliting the string

I created an Oracle table function that can delete and insert passed values into my table.

Yes I know that function shouldn't do that and I have to use stored procedure but unfortunately I can not use the stored procedure in another tool, so I misuse the function and add PRAGMA so the the function can use COMMIT in the body as well. The function works as I want but there is a issue with string literal bigger than 4000 characters. I think CLOB and the DBMS_CLOB function can handle large strings but I still get an error

> ORA-01704: string literal too long
> 01704. 00000 - "string literal too long"
> *Cause: The string literal is longer than 4000 characters.
> *Action: Use a string literal of at most 4000 characters.
> Longer values may only be entered using bind variables.

It is because I am passing very large string literal that exceeds 4000 characters and it doesn't matter that the input parameter p_val is defined as CLOB, 4000 characters is always the limit when passing literals?

create or replace FUNCTION table_fn_test (p_val CLOB, p_action VARCHAR2, p_random NUMBER, p_user VARCHAR2)
RETURN my_t_tf_tab PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_count NUMBER :=0;
v_dup NUMBER :=0;
v_info  VARCHAR2(50) :='';
v_val CLOB;
v_index NUMBER := 1;
BEGIN
IF p_val IS NOT NULL THEN
v_val := p_val || ',';
WHILE DBMS_LOB.INSTR(v_val, ',', v_index) > 0
LOOP
IF p_action = 'insert' THEN
SELECT COUNT(*) INTO v_dup FROM my_table WHERE COL2 = TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)) AND COL1 =  RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' ');
IF v_dup = 0 THEN
INSERT INTO my_table(COL2,COL1) VALUES (TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)), RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' '));
v_count := v_count + SQL%ROWCOUNT;
v_info := v_count || ' row(s) inserted';
END IF;
ELSIF p_action = 'delete' THEN
DELETE FROM my_table WHERE COL2 = TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)) AND COL1 =  RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' ');
v_count := v_count + SQL%ROWCOUNT;
v_info := v_count || ' row(s) deleted';
END IF;
v_index := DBMS_LOB.INSTR(v_val, ',', v_index) + 1;
END LOOP;
COMMIT;
END IF;
FOR rec IN (SELECT COL1, COL2 from my_table )
LOOP
PIPE ROW(my_t_tf_tab(rec.COL2, rec.COL1 ));
END LOOP;
END table_fn_test;

> 4000 characters is always the limit when passing literals?

Yes, unless your DB is configured for the larger 32k limit. (And technically it's bytes, not characters, which might be matter here.)

The problem is with your call, not with the function. You will need to construct a CLOB to pass in - how depends on where/how you call it.

From SQL you could concatenate several shorter strings together:

select table_fn_test (
to_clob('less that 4000')
|| to_clob('less than 4000')
|| to_clob('less than 4000') /* etc */,
...)
from dual

That's obviously a bit painful if you have a huge string to start with as you have to break it up as you copy and paste it. If the value is in a file or passed from an application then you should read and treat it as a CLOB as early as you can, so it's never subject to that limit.