Re: sending a clob in the refcursor?

From: <jimi_xyz_at_hotmail.com>
Date: 12 Aug 2005 07:49:31 -0700
Message-ID: <1123858171.541387.235030@z14g2000cwz.googlegroups.com>

amogh wrote:
> jimi_xyz_at_hotmail.com wrote:
> > Hi,
> > Is there anyway to select a clob with the refcursor? Let me explain.
> >
> > mytable =
> >
> > id | name | addressCLOB = type clob
> > -----------------------
> > 1 | Tom | NY
> > 2 | Sal | CA
> > 3 | Jed | PA
> > 4 | ed | NJ
> >
> >
> > Now what i want to do is...
> > api code..
> >
> > GlSql := 'select id, name, addressCLOB from mytable ' ||
> > 'where id = 2';
> >
> > OPEN cv for glSql;
> >
> >
> > -I have been working with a larger query and keep getting this error
> >
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-01003: no statement parsed
> >
> > I beleive its because of the Clob but i am not sure.
> >
> > Thank you,
> > Jimmie
> >
> Fetching a Clob through a REF Cursor is possible the way you have shown
> above. The error you are getting may be because of something else. Try
> fetching the id,name attributes and then the clob column separately,and
> see if you still hit the error. Can you post the code or a snippet where
> the clob is fetched thru the REF Cursor?
>
> Regards,
> Amogh

OK here is my code..

PROCEDURE query(in_word_to_search IN VARCHAR2,--a string defined by the user

                                in_location_array IN charArray,--where the user wants to search, EX title, scope, or objective

				out_error_code OUT NUMBER,--the error number
				out_error_message OUT VARCHAR2,--the error message
				cv IN OUT curtype)--sent back to the front end as a record set

IS
BEGIN

sql_contains_str := '';
sql_score_str := '';
sql_orderby_str := '';
sql_final := '';

IF in_location_array.count > 1 THEN
	sql_contains_str := 'contains('||in_location_array(1)||',

''%'||in_word_to_search||'%'', 1) > 0';

	FOR i in 2..in_location_array.count LOOP
		sql_contains_str := sql_contains_str || ' OR contains('

||in_location_array(i)|| ', ''%'||in_word_to_search||'%'', '|| i ||') > 0';

        END LOOP;

	FOR j in 2..in_location_array.count LOOP
		sql_score_str := sql_score_str || ' + score('||j||')';
	END LOOP;

	sql_score_str := 'score(1)' || sql_score_str;


	sql_orderby_str := ' ORDER BY ' ||sql_score_str|| ' DESC';

	sql_final := sql_score_str ||' "total_score" FROM project WHERE ' ||
sql_contains_str;
	sql_final := 'SELECT DISTINCT projectid, title, jobordernumber,

contract_number, company_performer, start_date, end_date, url, ' ||sql_final;

        sql_final := sql_final || sql_orderby_str; END IF; IF in_location_array.count = 1 THEN

        sql_final := 'SELECT DISTINCT projectid, title, jobordernumber, contract_number, company_performer, start_date, end_date, url, score(1) ' ||

				 '"total_score" FROM project ' ||
				 'WHERE contains('||in_location_array(1)||',
''%'||in_word_to_search||'%'', 1) > 0 ' ||
				 'ORDER BY score(1) DESC ';

END IF; dbms_output.new_line;
stdout(sql_final);

OPEN cv for sql_final;

out_error_code := 0;
out_error_message := SQLERRM;

EXCEPTION
when no_data_found then null;
 WHEN OTHERS THEN
  out_error_code := SQLCODE;
  out_error_message := sql_final;
  --'[' || CONST_PACKAGENAME || '.query] '||SQLERRM; ROLLBACK;
END query;

---

END SUBMITQUERY_API;
/
show errors Received on Fri Aug 12 2005 - 09:49:31 CDT