Tag Archives: PLSQL

Pass CLOB Variable in EXECUTE IMMEDIATE Statement

Pass CLOB Variable in EXECUTE IMMEDIATE Statement

Pass CLOB Variable because varchar length is small, we need to pass long sql query.

Error due to small variable define:

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

Note: To overcome this variable VARCHAR datatype length, We will use the CLOB datatype.

Following is the example of using bytes greater than varchar max length(326727 bytes).

Execute Immediate with CLOB Variable

DECLARE
var CLOB;
BEGIN
var := 'create table test2( id number(10))';

while length(var) <70000
loop
var := var || chr(10) || '--comment';
end loop;

dbms_output.put_line (length(var));
EXECUTE IMMEDIATE var;
END;
/
Output:
70004

PL/SQL procedure successfully completed.

Print output from CLOB variable
use dbms_lob.substr to break the string l_offset is starting point and middle parameter is length.

set serveroutput on
DECLARE
vara CLOB;
BEGIN
vara := 'create table test1( a number(10))';
-- to make length 64k k
while length(vara) <70000
loop
vara := vara || chr(10) || '--comment';
end loop;

--Syntax: dbms_output.put_line( dbms_lob.substr( CLOB_OBJECT, offset_length,offset_start ) );
dbms_output.put_line( dbms_lob.substr( vara, 2500,1 ) );

END;
/