Re: CLOB Question

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 23 Dec 2011 01:15:08 +0000 (UTC)
Message-ID: <pan.2011.12.23.01.15.07_at_gmail.com>

On Thu, 22 Dec 2011 15:16:59 -0800, ExecMan wrote:

> have a small procedure which strings together a bunch of values.
> The length is about 70k. The procedure works fine. A pasted a copy
> below. Then I tried a function, returning a CLOB and I get a ORA-06502:
> PL/SQL: numeric or value error ORA-06512.
>
> I thought CLOB values could be 4GB? Am I missing something within PL/
> SQL or within PHP (which can't be answered here).

You are missing something within PHP, which can be answered here. You are probably trying to do something like this:

$text="";
oci_bind_by_name($csr,":CLOB",$text);

That will not work, because of the wrong type, or as Oracle succinctly puts it, "numeric or value error". The right thing to do would be this:

$qry = <<<SQL
DECLARE
fcon CLOB;
BEGIN
SELECT fcontent into fcon
FROM test2_ins
WHERE fname='harrison_bergeron.txt';
:CLB:=fcon;
END;
SQL;
try {

    $dbh = oci_connect("scott", "tiger", "local");     if (!$dbh) {

        $err = oci_error();
        throw new exception($err['message']);

    }
    $lh = oci_new_descriptor($dbh, OCI_DTYPE_LOB);

    $res = oci_parse($dbh, $qry);
    oci_bind_by_name($res, ":CLB", $lh, -1, SQLT_CLOB);
    if (!oci_execute($res, OCI_NO_AUTO_COMMIT)) {
        $err = oci_error($dbh);
        throw new exception($err['message']);

    }
    $novel = $lh->read(65536);
    printf("Length of the string is %d\n", strlen($novel)); }
catch(Exception $e) {

    print "Exception:\n";
    die($e->getMessage() . "\n");
}
?>

If I am not mistaken, that is a script from the chapter 9 of the following book:

http://www.apress.com/9781430235606

-- 
http://mgogala.byethost5.com

Received on Thu Dec 22 2011 - 19:15:08 CST