SQL statement is to large, and creates a error

From: <jimi_xyz_at_hotmail.com>
Date: 3 Nov 2005 08:42:43 -0800
Message-ID: <1131036163.856437.220550@f14g2000cwb.googlegroups.com>

Hi
My error is this..

ORA-00604: error occurred at recursive SQL level 1 ORA-01003: no statement parsed

I beleive this error is happening because my SQL statment is to large, for the ref cursor to open. Here is the procedure..

---

PROCEDURE showcat(in_arrayprojectid in
Jiffy_IntArray_Def_API.int_array_typ,

			in_taxID			in		number,
		 	out_error_code 	 	out 	number,
		 	out_error_message 	out 	varchar2,
			out_ret_val			out		Jiffy_Cursor_Def_API.ref_cur_typ)

IS

BEGIN IDs := in_arrayprojectid(1);
If in_arrayprojectid.count > 1 Then

	FOR i in 2..in_arrayprojectid.Count LOOP
		IDs := IDs || ', ' || in_arrayprojectid(i);
	END LOOP;

End If;

vgbl_sql :=	' SELECT count(taxonomy.taxid), taxonomy.taxid, name '||


			' FROM taxonomy, tax_projects '||

			' WHERE tax_projects.taxid = taxonomy.taxid '||

			' AND projectid in ('|| IDs ||') ' ||

			' AND taxonomy.taxid in '||

			' (SELECT taxid FROM taxonomy '||

			' CONNECT by PRIOR taxid = parentid '||

			' START with taxid = '|| in_taxID ||' ) ' ||

			' GROUP by taxonomy.taxid, name ';

OPEN out_ret_val FOR vgbl_sql;

	 out_error_code := 0;
	 out_error_message := SQLERRM;

EXCEPTION
WHEN OTHERS THEN

		out_error_code := SQLCODE;
		out_error_message := '[' || CONST_PACKAGENAME || '.showcat] ' ||
SQLERRM;
	ROLLBACK;

END showcat;

---

The SQL statement that is being executed should look like this..

SELECT count(taxonomy.taxid), taxonomy.taxid, name FROM taxonomy, tax_projects |
WHERE tax_projects.taxid = taxonomy.taxid AND projectid in ('1, 2, 3, 4, .... N')
AND taxonomy.taxid in
(SELECT taxid FROM taxonomy
CONNECT by PRIOR taxid = parentid
START with taxid = 176
GROUP by taxonomy.taxid, name;

NOTE: There is about 1884 values within the IN () function. AND.. IDs VARCHAR2(32000);
vgbl_sql VARCHAR2(32000);

Any help/ideas will do.

Thanks Inadvance
Jimi Received on Thu Nov 03 2005 - 10:42:43 CST