Define a CLOB input parameter for Oracle function (OR Define a function with CLOB input parameter)

Question

I am going to run a code block like below:

CREATE OR REPLACE FUNCTION func_name (START_DATE NUMBER,END_DATE NUMBER, NAME clob)
                     .
                     .
                     .
        select * from table_name
        where name_desc in NAME
                     .
                     .
                     .
 END;

In which its purpose is to create a function that one of its input argument is CLOB data type. But by executing my code, I get following error:

ORA-01704: string literal too long

I searched on the internet and also among stackoverflow questions but it didn't get any consequence.

Could you anyone help me with this issue?

Is the error coming from the function, or from the *call to* the function - where you're trying to provide the CLOB argument as a very long literal (i.e. `func_name(1, 2, 'some very long string ...')`)? Not relevant, but `in NAME` looks wrong - you have a single value so compare with `=` not `in`. If the CLOB is supposed to be, say, a comma-separated list of names and you want to match against any of them then that won't work, you would need to split the values out, or preferably pass as a collection rather than a string. Unclear what you're actually trying to do though. — Alex Poole, May 30 '20 at 12:02
This error is presented during calling the function. The value which is sent to CLOB parameter, is a list of names which are separated by comma, so I think that using the IN operator doesn't lead to problem — Atefeh, May 30 '20 at 12:09
Actually NAME will be replaced by a string of words which is separated by comma — Atefeh, May 30 '20 at 12:15
That isn't how `in` works. You will compare `name_desc` with the entire CLOB string, not each comma-separated value within the string. Anyway, the error is coming from the caller, so you need to construct the CLOB from smaller chunks. Without seeing your current code or knowing where that string or list of values is coming from, can't really help more. There are examples on this site of building up CLOBs though. — Alex Poole, May 30 '20 at 12:15
Could you please give me a block of codes of your suggestion as solution? — Atefeh, May 30 '20 at 12:18
To clarify what is the goal of IN operator usage, it is worth mentioning that I am going to filer the selected table based on values which will be received from CLOB parameter, and because these values are more than one, I used IN. — Atefeh, May 30 '20 at 12:22
There are lots of examples already, [here's one](https://stackoverflow.com/a/20614279/266304). Again, you can't use `in NAME` to do that because `NAME` is a single string value - Oracle doesn't know or care that to you it represents multiple values. Where are all those values to look for coming from, and can they go into a collection instead of being put into that one CLOB string? — Alex Poole, May 30 '20 at 12:25
I understood and understand you. But it is considerable that the specified block code works with varchar2 data type for fewer than 4000 character. But by changing data type to CLOB, I get that error. This list is provided from OBIEE by end user by choosing among a list of values. — Atefeh, May 30 '20 at 12:40

score 0 · Accepted Answer · answered Jul 17 '20 at 10:38

I believe I understand what you are trying to achieve. As per comments your use of IN is wrong. IN expects list of literals or list of values from select, but your CLOB value is neither, it is just a long string that needs to be processed first before you can use it in SELECT like you mentioned in comments.

To process your CLOB with list of names delimited with , you can find first comma in CLOB and extract value from the beginning of CLOB until this first delimiter and found value is put into collection (delimiter is removed ofc and value is trimmed, this might be optional as I am not sure how your input looks like exactly). Next you remove found value from the beginning of the CLOB and repeat until there are nothing to process in this CLOB. Once you have list of values in collection you can use it as SELECT in your original SELECT.

Try this example:

CREATE TABLE table_name (
  name_desc VARCHAR2(250) NOT NULL
);

INSERT INTO table_name (name_desc)
VALUES ('Lorem');
INSERT INTO table_name (name_desc)
VALUES ('ipsum');
INSERT INTO table_name (name_desc)
VALUES ('test');

COMMIT;

CREATE OR REPLACE TYPE name_list_tabt IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PROCEDURE func_name(p_start_date NUMBER,
                                      p_end_date   NUMBER,
                                      p_name       CLOB) IS
  v_delimiter VARCHAR2(1) := ',';
  v_name CLOB := p_name;
  v_delimiter_pos NUMBER;
  v_name_value VARCHAR2(4000);
  v_name_list name_list_tabt := name_list_tabt();
BEGIN

  -- OTHER CODE ABOVE ...

  LOOP
    -- Get position of delimiter
    v_delimiter_pos := dbms_lob.instr(v_name, v_delimiter, 1, 1);
    -- If no comma is found we get rest of the CLOB for last name, if there is no more CLOB to process this will return 0
    IF v_delimiter_pos = 0
    THEN
      v_delimiter_pos := dbms_lob.getlength(v_name);
    END IF;
    -- Get next name based on delimiter position, can cause error if single name is over 4000 bytes long
    v_name_value := dbms_lob.substr(v_name, v_delimiter_pos, 1);
    -- Next code will remove found name from CLOB start
    v_name := regexp_replace(v_name, v_name_value, NULL, 1, 1);
    -- If we reached the end of CLOB, exit
    IF v_delimiter_pos = 0
    THEN
      EXIT;
    END IF;
    -- Add new name to collection if it is not empty
    IF TRIM(REPLACE(v_name_value, v_delimiter)) IS NOT NULL
    THEN
      v_name_list.extend();
      v_name_list(v_name_list.count) := TRIM(REPLACE(v_name_value, v_delimiter));
    END IF;
  END LOOP;
  -- Your select in for loop just to make this code working, adjust as you need
  FOR i IN (SELECT *
              FROM table_name
             WHERE name_desc IN (SELECT /*+ dynamic_sampling(t 2) */
                                  column_value
                                   FROM TABLE(v_name_list) t))
  LOOP
    dbms_output.put_line(i.name_desc); -- Just some test output
  END LOOP;

  -- OTHER CODE BELOW ...

END;
/

BEGIN
  -- Example with different, even possibly incorrect values
  func_name(p_start_date => 1,
            p_end_date   => 2,
            p_name       => 'Lorem,ipsum,dolor,sit,amet,consectetur, 
 ,,adipiscing,elit,Etiam,interdum,ligula,    ,     ,');
  -- Based on table values and CLOB here output should be "Lorem" and "ipsum"
END;
/

Define a CLOB input parameter for Oracle function (OR Define a function with CLOB input parameter)

1 Answers1