Anyone worked out how to pass >32k to a test script?

[Tim Scott]

Hi,

I'm having trouble passing in a large value (~74k) as a parameter to a test script. I searched the forum to no avail :-(

The script is intended to receive that input and convert it to a sqlplus friendly script so that we can supply that in software patches for our application.

The problem is that when I select 'CLOB' the script fails with "Invalid LOB locator" and when I use "Temporary CLOB", it gets converted immediately to an NCLOB. Attempting to process that in PL/SQL gives me just the very first character and then blanks thereafter ... :-(.

A very simple test case:

BEGIN
:v := dbms_lob.substrc, 20);
END;

Then setup "c" as a variable using "Temporary CLOB", open the Large Data Editor and enter "Some Text". Add "v" as a String.

Running the script results in "v" having the value "S".

Opening the Large Data Editor gives "S" ... but there's some anomalies: the size has changed (it was 9 when I typed "Some Text" and it's now 36) and the Hex view shows what I would expect from UCS-2 encoded data ("S", chr(0), "o", chr(0), ...)

The database is 12.1.0.2 with AL32UTF8 for VARCHARs and AL16UTF16 for NVARCHARs; PLSDEV is Version 9.0.4.1644.

Any ideas greatly appreciated.

Thanks.

 

[Tim Scott]

A rather convoluted answer followed some overnight inspiration :

DECLARE
  l_clob  CLOB;
  p_do NUMBER := 1;
  p_so NUMBER := 1;
  csid NUMBER := dbms_lob.default_csid;
  p_lang NUMBER := dbms_lob.default_lang_ctx;
  p_warn NUMBER;
BEGIN
  dbms_lob.createtemporary(l_clob, TRUE);
  dbms_lob.convertToClob(l_clob, :b, dbms_lob.lobmaxsize, p_do, p_so, csid, p_lang, p_warn);
  :v := dbms_lob.substr(l_clob, 20) || ' ... [' || dbms_lob.getlength(l_clob) || ' chars] ...' ||
        dbms_lob.substr(l_clob, 20, dbms_lob.getlength(l_clob)-19);
  dbms_lob.freetemporary(l_clob);
END;

And then bind "b" as a "Temporary BLOB".