Oracle PLSQL function throws PL/SQL: numeric or value error: character string buffer too small ,for returning large data

I have the following PLSQL functions that returns following error when returning a large data.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

MODIFIED PLSQL FUNCTION

 CREATE OR REPLACE FUNCTION FRDUSER.FRD_TELECOP_DYN_REP_SELECT_OPT (p_select IN VARCHAR2)
   RETURN CLOB
AS
 -- v_temp           CLOB;
  v_out            CLOB; 
  TYPE RefCurTyp   IS REF开发者_如何学Go CURSOR;      
 -- len     BINARY_INTEGER;
  v_cursor         RefCurTyp; 
  c_key            VARCHAR2 (1000);
  c_value          VARCHAR2 (1000);
  separator        VARCHAR2(3);

BEGIN

    OPEN v_cursor FOR p_select;

    -- Fetch rows from result set one at a time:
    separator := '';     

    LOOP      

      FETCH v_cursor INTO c_key,c_value;        
        EXIT WHEN v_cursor%NOTFOUND;

   --      dbms_lob.createtemporary(v_out, TRUE);
  --       dbms_lob.open(v_out, dbms_lob.lob_readwrite);
         v_out := v_out ||  separator || c_key || ',' || c_value;  
  --       dbms_lob.append(v_out,  v_temp);
         separator := ':';  

    END LOOP;

    -- Close cursor:
    CLOSE v_cursor;
    RETURN (v_out);
END;
/

I am executing the function as below,

            SELECT   CASE  
                WHEN OPTIONS_TYPE = 'S' THEN    OPTIONS_VALUES
                WHEN OPTIONS_TYPE = 'D'  THEN TO_CLOB(FRD_TELECOP_DYN_REP_SELECT_OPT (OPTIONS_VALUES))  
                END  
      FROM FRD_REP_FW_REP_TEMPLATES A, FRD_REP_FW_TEMPLATES B
        WHERE A.REP_ID=1123 AND  A.TEMP_ID=B.TEMP_ID  
        ORDER BY A.REP_TEMP_ID ASC

When i ran the above sql query it throw following error ORA-00932: inconsistent datatypes: expected CHAR got CLOB

The parameter to the sql function(options_values) contains sql query.

I suspect you're overcomplicating your logic a bit. You probably just need something like this (assuming that you actually need to dynamically pass the query in to the function)

SQL> create table clob_test (
  2    key varchar2(100),
  3    val varchar2(100)
  4  );

Table created.

SQL> insert into clob_test
  2    select level, dbms_random.string( 'A', 100 )
  3      from dual
  4   connect by level <= 10000;

10000 rows created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace function return_clob
  2    return clob
  3  as
  4    v_out clob;
  5    v_sql varchar2(1000) := 'select key, val from clob_test';
  6    v_key varchar2(100);
  7    v_val varchar2(100);
  8    v_rc  sys_refcursor;
  9  begin
 10    open v_rc for v_sql;
 11    loop
 12      fetch v_rc into v_key, v_val;
 13      exit when v_rc%notfound;
 14      v_out := v_out || v_key || ', ' || v_val;
 15    end loop;
 16    return v_out;
 17* end;
SQL> /

Function created.

SQL> select return_clob from dual;

RETURN_CLOB
--------------------------------------------------------------------------------

1, tzGWFXwKLgrRTGzTGbWMYMjVniIVMmCuGYGYydcrArHPRLExoFAJsZVhhPrRKyERExqRkbLGSebqX
<< more lob data removed>>

Per Oracle:

Decode:

If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as the first result parameter.

So you're trying to force result of decode to be a clob, which it won't be.

Have you tried a case statement instead of decode? (I haven't but worth a shot ;)