Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Getting ORA:06502:PL/SQL: numeric or value error: character string to small in FUNCTION

Christopher HallFeb 21 2024

Hello, I am trying to put the following SELECT into a function (The SELECT works by itself).

 SELECT DBMS_LOB.SUBSTR(GURSQLL_CMD, 3392, 1) "CLOB_DATA" 
FROM BANSECR.GURSQLL

This is the function code:

The error is being thrown here (line #13 ) =>

EXECUTE IMMEDIATE sql_stmnt INTO l_clob_text

It's probably something silly/stupid that I am missing. Thanks in advance.

CREATE OR REPLACE FUNCTION BANINST1.f_get_clob_data (
   p_col_name   IN VARCHAR2,
   p_ownr   IN VARCHAR2,
   p_tab_name IN VARCHAR2)
    RETURN VARCHAR2
IS
    l_clob_text   VARCHAR2(32767);
    sql_stmnt    VARCHAR2(100);
BEGIN

   sql_stmnt := 'SELECT DBMS_LOB.SUBSTR(||p_col_name||,3392,1) FROM ||p_ownr||.||p_tab_name';  
   
      EXECUTE IMMEDIATE sql_stmnt INTO l_clob_text
       USING p_col_name,p_ownr,p_tab_name;

    RETURN l_clob_text;
END;
/
Comments
Post Details
Added on Feb 21 2024
9 comments
136 views