[solved] oracle11g read CLOB field content

Yii 1.1 General Discussion
1

use CLOB to store the HTML content,

how can i read the string of HTML content by CLOB field?

echo $this->CONETNT_HTML;

>>> Resource id #51

2

echo stream_get_contents($this->CONTENT_HTML)

  • Atention: Yii does not handle very well CLOB fields or very large string fields!

See my topic: http://www.yiiframework.com/forum/index.php?/topic/4606-oci-problem-with-large-texts/page__hl__CLOB__fromsearch__1

3

thank you very much!!

but why sometime, will show "Resource id #51" with stream_get_contents(…) ?

4

Still displaying "Resource"?

5

sometime show Resource #51

sometime will kill httpd.exe

6

Try first assign stream_get_contents result to a variable. After this, try echo the variable.


$var = stream_get_contents($this->CONTENT_HTML);




echo $var;
7

use Procedure can fix this problem!


public static function getOracleClobValue($tableName, $fieldName, $primaryKey, $pk)

{

    $sql = 'SELECT LENGTH('.$fieldName.') AS NUM FROM '.$tableName.' WHERE '.$primaryKey.'=:PK';

    $cmd = Yii::app()->db->createCommand($sql);

    $cmd->bindParam(':PK', $pk);

    $row = $cmd->queryRow();

    $num = $row['NUM'];


    $content='';


    $start = 1;

    $len = 2500; // fetch length per time

    while($start <= $num)

    {

        $ret='';

        $sql = "BEGIN GET_CLOB(:TABLE_NAME, :FIELD_NAME, :PRIMARY_KEY, :PRIMARY_KEY_VALUE, :START_POS, :FETCH_LENGTH, :RETURN_VALUE); END;";

        $cmd = Yii::app()->db->createCommand($sql);

        $cmd->bindParam(':TABLE_NAME', $tableName);

        $cmd->bindParam(':FIELD_NAME', $fieldName);

        $cmd->bindParam(':PRIMARY_KEY', $primaryKey);

        $cmd->bindParam(':PRIMARY_KEY_VALUE', $pk);

        $cmd->bindParam(':START_POS', $start);

        $cmd->bindParam(':FETCH_LENGTH', $len);

        $cmd->bindParam(':RETURN_VALUE', $ret, PDO::PARAM_STR, 5000);

        $cmd->execute();

        $content .= $ret;

        $start=$start+$len;

    }

    

    return $content;

}

Procedure Sql:


CREATE OR REPLACE PROCEDURE GET_CLOB(TABLE_NAME IN VARCHAR2, FIELD_NAME IN VARCHAR, PRIMARY_KEY IN VARCHAR, PRIMARY_KEY_VALUE IN INTEGER, START_POS IN INTEGER, FETCH_LENGTH IN BINARY_INTEGER, RETURN_VALUE OUT VARCHAR2) IS RULE_XML CLOB;

FETCH_BUFFER VARCHAR2(3999);

FETCH_LENGTH_ BINARY_INTEGER;

BEGIN

EXECUTE IMMEDIATE 'SELECT ' ||FIELD_NAME|| ' FROM ' ||TABLE_NAME|| ' WHERE ' ||PRIMARY_KEY|| '=:1' INTO RULE_XML USING PRIMARY_KEY_VALUE;

FETCH_LENGTH_:=FETCH_LENGTH;

DBMS_LOB.READ(RULE_XML, FETCH_LENGTH_, START_POS, FETCH_BUFFER);

RETURN_VALUE := FETCH_BUFFER;

END GET_CLOB;
8

Just this worked for me:




public $questao;


...


public function afterFind(){

        $this->questao = stream_get_contents($this->nmquestao);

        return true;

    }

    

    public function beforeValidate(){

        $this->nmquestao = $this->questao;

        return true;

    }
9

Ops! One important advice:

If you try to catch more than one record from db with CLOB field, httpd will crash!

Make sure you are loading this CLOB field only when you’re retrieving ONE record by time.

10

Did anyone solve problems with storing large texts in Oracle? Would be nice if this workaround would be transparent for ActiveRecord/CRUD patterns in Yii (ie fetching multiple AR models with CLOB parameters…)

It is realy a problem to handle large strings like in CMS systems…

11