Hi, I'm trying to understand this small issue our application, written
in PHP, is having with Oracle.

I 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).

PROCEDURE WORKS FINE
=====================
PROCEDURE get_all_tickers (
p_data OUT REF_CRS) IS

v_tickers CLOB;
v_ticker_table ticker_tab := ticker_tab();

BEGIN
FOR v_rec IN (SELECT ticker FROM master_table WHERE type = 'S') LOOP
v_tickers := v_tickers || v_rec.ticker || ',';
END LOOP;

v_tickers := RTRIM(v_tickers,',');
v_ticker_table.EXTEND;
v_ticker_table(1) := ticker_type('0');
v_ticker_table(1).ticker := v_tickers;
OPEN p_data FOR SELECT * FROM TABLE(CAST(v_ticker_table AS
ticker_tab));
END get_all_tickers;


FUNCTION FAILS -- PHP chokes on it
=============
FUNCTION get_all_tickers
RETURN CLOB IS

v_tickers CLOB;

BEGIN
FOR v_rec IN (SELECT ticker FROM master_table WHERE type = 'S') LOOP
v_tickers := v_tickers || v_rec.ticker || ',';
END LOOP;

v_tickers := RTRIM(v_tickers,',');
RETURN v_tickers;
END get_all_tickers;

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


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