From: "T. Murlidharan Nair" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 19, 2002 12:57 PM Subject: Stored procedures and Perl DBI
> Has anyone executed a stored procedures from a perl CGI. I am having > problems when there are many rows returned. When the return result > is > a scalar things are fine but when the return is a result set, I am > totally stumped. > I need to finish this soon so please help. > Murli > > > The following is my pl/sql code > > CREATE OR REPLACE PROCEDURE new_worker_select_cur > ( f_name IN OUT varchar2, > l_name OUT varchar2, > email OUT varchar2 > ) > AS > > CURSOR cursor_temp (cur_f_name IN varchar2) IS > SELECT PREFIX_NAME, FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, EMAIL, > PEOPLE_ID, PID, SOURCE_ID,SOU > RCE_INFO, NSF_USER_TYPE FROM test WHERE first_name = cur_f_name; > > temp_record cursor_temp%ROWTYPE; > > BEGIN > > IF( cursor_temp%ISOPEN) THEN > CLOSE cursor_temp; > END IF; > OPEN cursor_temp(f_name); > > LOOP > > BEGIN > > FETCH cursor_temp INTO temp_record; > EXIT WHEN cursor_temp%NOTFOUND; > > END; > f_name := temp_record.first_name; > l_name := temp_record.last_name; > email := temp_record.email; > > DBMS_OUTPUT.put_line(f_name); > DBMS_OUTPUT.put_line(l_name); > DBMS_OUTPUT.put_line(email); > > END LOOP; > > RETURN; > > IF(cursor_temp%ISOPEN) THEN > CLOSE cursor_temp; > END IF; > > > END NEW_WORKER_SELECT_CUR; *** This belongs in dbi-users, _not_ dbi-dev. I'm sending my response there. I don't see how this procedure would ever return multiple rows through its parameters and thoser are all DBI would see. There are exapmles in DBD-Oracle-1.x/Oracle.ex/ for how to return result sets through DBI placedholders. You need to read Oracle's manuals concerning DBMS_OUTPUT. It is really severely limited in the amount you can pass that way. I suspect that for large number of rows you are overflowing its buffer. If you look in the webserver logs you may see that mentioned. This is assuming you've activated DBM_OUTPUT somehow. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.htm Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
