2015-02-11 15:35
浏览 517


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?

图片转代码服务由CSDN问答提供 功能建议

我正在查询 clob

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

我收到错误 ORA-06502:PL / SQL:numeric或 值错误:字符串缓冲区太小

因此当我将其更改为 htf.escape_sc(DBMS_LOB.substr(patch_audit, )时 4000 ))查询运行正常,但首先返回4000.

在一个案例中,该列行中的字符数为49979个字符, 有时甚至更多。


  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongqian5569 2015-02-14 13:15

    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.

    解决 无用
    打赏 举报
  • 查看更多回答(2条)

相关推荐 更多相似问题