显示CLOB中存储的大型文本数据

Question

I am querying my clob column as

$patch_log= oci_parse($conn_prs, "select htf.escape_sc(DBMS_LOB.substr(patch_audit, 9999)) as patch_log
from patch_files where bug_id = 12345"); 
...
echo $row['PATCH_LOG'];

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

So when I am changing it to htf.escape_sc(DBMS_LOB.substr(patch_audit,4000)) the query is running fine but returns first 4000.

The number of characters in that column row is 49979 characters in one case and sometimes even more.

I need to display it with php, how can I do it?

Answer 1

Assuming you're using Oracle 11.2 or earlier

You're passing DBMS_LOB.SUBSTR() a CLOB, which means the data type of the returned value is a VARCHAR2. You're calling a SELECT statement, which means you're using the returned value in SQL.

The maximum size of a VARCHAR2 in SQL is 4,000 bytes. This is in marked contrast to the maximum size of a VARCHAR2 in PL/SQL, which is 32,767 bytes.

This is why dbms_lob.substr(patch_audit, 4000) works but dbms_lob.substr(patch_audit, 4001) will not.

These are hard limits built into the database and there's no way around them. If you want to do this in SQL you have to split up your CLOB on 4,000 byte and SELECT multiple parts of the data. This means iterating through the CLOB in the database. For instance you could do something like the following, which works out the amount of data in the CLOB and returns N rows, each with 4,000 bytes.

with my_clob as (
 select patch_audit 
   from patch_files 
  where bug_id = 12345 
        )
 select dbms_lob.substr(patch_audit, 4000, (level - 1) * 4000 + 1)
   from my_clob
connect by level <= ceil(dbms_lob.getlength(patch_audit) / 4000)

Don't do this on more than one CLOB at a time otherwise you'll be returning huge amounts of data and CONNECT BY evaluates the WHERE clause after the hierarchy has been created so a sub-select is necessary.

Or, you could just select the CLOB and parse it in PHP. I don't know any PHP but OCI-Lob::read would appear to be a good place to start. There's a few blogs out there which give you an indication; Mark Foster wrote the following (modified slightly for you)

$result = oci_execute($patch_log);
    if($result !== false){
        while($row = oci_fetch_assoc($patch_log)){
            echo $row['PATCH_LOG']->read(2000);
        }
    }

Assuming you're using Oracle 12.1 or later

In 12c Oracle increased the size of the VARCHAR2 (note not VARCHAR) to 32,767 bytes in SQL. In order to use this increase you have to alter the MAX_STRING_SIZE initialization parameter to EXTENDED. This is a one way change, which can't be backed out and could have large impacts on your application. It's something you want to test first.

Internally the extended columns will be stored as LOBs anyway, so LOB processing will be done in the background by Oracle whether you like it or not - and any LOB restrictions may (don't know) still apply. Unless there's a genuine business need it's worth just doing some LOB processing in PHP.