0

I have such table function with one parameter of CLOB data type

CREATE TYPE clob_tab AS TABLE OF CLOB;
/



create or replace FUNCTION process_clob (clob_val IN CLOB  )
RETURN clob_tab 

IS
      v_clob clob_tab := clob_tab();
      v_count NUMBER := 0;
      v_num NUMBER;
      v_start  PLS_INTEGER;
      v_next   PLS_INTEGER;
      v_delim  PLS_INTEGER;
      v_len    PLS_INTEGER;

      
    BEGIN
         v_start := 1;
  LOOP
    v_next  := 0;
    v_delim := DBMS_LOB.INSTR( clob_val, ',', v_start );
    IF v_delim > 0 AND (v_next = 0 OR v_delim < v_next) THEN
      v_next := v_delim;
    END IF;
    v_delim := DBMS_LOB.INSTR( clob_val, ';', v_start );
    IF v_delim > 0 AND (v_next = 0 OR v_delim < v_next) THEN
      v_next := v_delim;
    END IF;
    v_delim := DBMS_LOB.INSTR( clob_val, '|', v_start );
    IF v_delim > 0 AND (v_next = 0 OR v_delim < v_next) THEN
      v_next := v_delim;
    END IF;

    v_len := CASE v_next WHEN 0 THEN LENGTH( clob_val ) + 1 ELSE v_next END - v_start;
    -- Add the substring to the result table
    v_clob.extend();
    v_clob(v_clob.count) := SUBSTR( clob_val, v_start, v_len );

    
    EXIT WHEN v_next = 0;
    -- Move to the next position after the delimiter
    v_start := v_next + 1;
  END LOOP;
      RETURN v_clob;
END process_clob;  


select * from process_clob ( :val )

it is working as expected but I wonder what happens if I run the select statement in SQL Developer. Not using Run as Script but Run as statement.

a popup will open to enter values for bind variable. My question is if what data type is this bind variable.

I can enter some comma delimited string like abc,xyz and get the values in one column separated in rows. but If I try to enter something from Excel like this, it is a string and comma together of 16 characters. enter image description here

It works fine till I try to enter more than 1820 rows. Then I get this error message enter image description here

First I thought maybe the bind variable is of data type varchar2( 32767 bytes) but 16 characters times 1821 rows = 29136. It obviously does nothing to do with it. what is the reason for that. Is it just a limit in SQL Developer in the popup? What type is the bind variable?

I need to run it as statement and not a script because of some other joins and need the output as table with columns.

Pato
  • 153
  • 6
  • This is because [string literal](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-1824CBAA-6E16-4921-B2A6-112FB02248DA) has `varchar2` datatype by default: A text literal can have a maximum length of 4000 bytes if the initialization parameter MAX_STRING_SIZE = STANDARD, and 32767 bytes if MAX_STRING_SIZE = EXTENDED. See this answer for explanation of the error: https://stackoverflow.com/a/34739537/2778710 – astentx May 12 '23 at 13:38
  • but I don't exceed 32767 Bytes because 16 characters in 1821rows is just 29136 Bytes. I don't get an error string literal too long as well but another message – Pato May 12 '23 at 17:19
  • If you check the linked question you'll find this: *But, when the same query is executed through JDBC it gives "ORA-01460: unimplemented or unreasonable conversion requested"* What is your max_string_size parameter? – astentx May 12 '23 at 18:20
  • I am not a DBA so I can not check it but assuming that I can enter more than 4000 but less then 32k then the parameter is set to extendednif Understand it correctly. That means SQL Developer is using JDBC driver and there us no option to enter more? If I would have declared bind variable of CLOB data type like var clob_type CLOB and excec :clob_type := '1,2,3,.....100000' then the string could have been more than 32k why is that? – Pato May 13 '23 at 06:25

0 Answers0