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;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.