CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldevelopersqlplus
Tim Meyer
Tim Meyer

Reputation: 12600

How do you UPDATE a CLOB with large values using only PL/SQL without modifying the DB schema?

Say you had an Oracle 10g (10.2.0.3) database scheme which you cannot modify (as it is not under your control), and you want to insert large strings (100k+ characters) into some CLOB fields for testing purposes using PL/SQL (to be more precise: An SQL script which can be executed via sqlplus through a batch file). Is it possible?

I have found several solutions using .NET, oci etc or creating a stored procedure, but I need a solution which works e.g. from within SQL Developer, without doing any modifications to the DB scheme like adding stored procedures etc.

I have tried it in several ways like e.g.

-- Disclaimer: I typed this by heart just for SO, there might be syntax errors
DECLARE
  v_test CLOB;
  v_clob_to_update CLOB;
BEGIN

  -- Note: rpad only works up to 32755 or something
  v_test := to_clob( rpad( 'This is a very long string', 30000, '.' ) );
  v_test := v_test || to_clob( rpad( '.', 30000, '.' ) );

  SELECT my_clob_field INTO v_clob_to_update FROM my_table WHERE my_id = 1234 FOR UPDATE;
  DBMS_LOB.WRITE( v_clob_to_update, LENGTH( v_test ), 1, v_test );
END;

but I always end up getting "string literal too long" or "numeric or value error" messages.

Is it possible to do this while respecting the limitations I'm given?

Upvotes: 0

Views: 4614

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

If you use the dbms_lob package to write to v_test, you should get past the error that you're getting. The string concatenation operator || hasn't been overloaded to handle LOB data.

In other words, rather than

v_test := v_test || to_clob( rpad( '.', 30000, '.' ) );

you probably want

dbms_lob.writeAppend( v_test, 30000, rpad( '.', 30000, '.' ) );

Upvotes: 4

Related Questions