CLOB Question

From: ExecMan <artmerar_at_yahoo.com>
Date: Thu, 22 Dec 2011 15:16:59 -0800 (PST)
Message-ID: <8c9d8860-5771-453e-b30c-fd18ddd3998d_at_o9g2000yqa.googlegroups.com>

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; Received on Thu Dec 22 2011 - 17:16:59 CST