which data type is support more string/characters then CLOB

Question

Currently I used CLOB for storing definition of an stored procedures for all_objects but some procedure length are long so it is very difficult to excute using Excute Immediate as it cross lenghth inw hile using CLOB

ORA-06502: PL/SQL: numeric or value error 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

DECLARE
    v_output   CLOB := NULL;
    a          CLOB := NULL;
    srce       VARCHAR2(1000) := NULL;
BEGIN
   -- Note,we don't search for package bodies. We will extract the body
   -- along with the package spec.
    dbms_output.put_line('Database DDL For Selected Objects Report');
    FOR dd IN (
        SELECT
            object_name,
            status
        FROM
            all_objects
        WHERE
                status != 'VALID'
            AND
                object_name LIKE '%SP_RPT%'
    ) LOOP
        a := '';
        srce := dd.object_name;
        dbms_output.put_line(srce || ' proceudure creation start');
        FOR dd1 IN (
            SELECT
                text
            FROM
                dba_source
            WHERE
                    type = 'PROCEDURE'
                AND
                    name LIKE dd.object_name
        ) LOOP
-- DBMS_OUTPUT.put_line (dd1.text);
            a := a || dd1.text;
     --       DBMS_OUTPUT.put_line (a);
        END LOOP;

        dbms_output.put_line(a);
   --  EXECUTE IMMEDIATE a ;
        dbms_output.put_line(srce || ' proceudure Updated successfully');
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('ERROR occured while creating Procedure ' || srce);
        dbms_output.put_line(a);
        dbms_output.put_line(substr(
            dbms_utility.format_error_stack
             || ' ,'
             || dbms_utility.format_error_backtrace,
            1,
            500
        ) );

        RAISE;
END;

EXECUTE IMMEDIATE accepts CLOB statements, so there should be no problem. The limit for CLOB depends on your Database Block Size and vary from 8 TB to 128 TB - I doubt that one could ever exceed this limit. — Wernfried Domscheit
thanks @WernfriedDomscheit i too surprised let me share code and error — Siddharth Jain
@WernfriedDomscheit could you p,lese check comments in answer 1 and let me know your feeds — Siddharth Jain
Having just an error message is not helpful. What is your code? For sure the issue it not due to length in case of CLOB. — Wernfried Domscheit
@SiddharthJain can you please comment the exception part of your code and run it, just see at which line you are getting error. — Ankit Mongia

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-08-09T10:27:36

You may try it like this:

FOR dd1 IN (SELECT text FROM dba_source WHERE ... ORDER BY LINE) LOOP
    a := a || dd1.text ||CHR(13);
END LOOP;

However, what is the purpose of this? Most likely a simple

FOR dd IN (
    SELECT object_name, object_type, status
    FROM all_objects
    WHERE status != 'VALID'
        AND object_name LIKE '%SP_RPT%') 
LOOP
    EXECUTE IMMEDIATE 'ALTER '||dd.object_type||' '||dd.object_name||' COMPILE';
    if dd.object_type = 'PACKAGE BODY' THEN
       EXECUTE IMMEDIATE 'ALTER '||dd.object_type||' '||dd.object_name||' COMPILE BODY';
    END IF;
END LOOP;

would do the same.

which data type is support more string/characters then CLOB

2 Answers