debugging an Oracle function??

From: Rich D <rdalfonso_at_nyc.rr.com>
Date: 12 Feb 2003 10:17:19 -0800
Message-ID: <196cd325.0302121017.478a6777@posting.google.com>

I have a function that accepts a value and runs three cursors. I then run an 'if statement' using the recordcount of the cursors and return a string to the user.

All I wanna know is how do I debug/test this function in SQL PLus. I need to see the values that my cursors are returning, certain values of variables, etc

I was told to set 'set serveroutput on' and then I can use dbms_outout.put_line to write out any values as long as I run the function from a stored procedure.

Can anyone show my some sample code of how to run a function form a stored proc?

Here's the function I'm using

CREATE OR REPLACE FUNCTION QuestionTypeForQuestionId (Survey_questions_id_in IN NUMBER)
RETURN varchar2 IS

		CURSOR freeform_cur IS 
			SELECT Survey_questions_freeform 
			FROM Survey_Questions 
			WHERE Survey_questions_id = Survey_questions_id_in; 
			freeform_cur_rec freeform_cur%ROWTYPE;

		CURSOR allowed_cur  IS 
			SELECT Survey_questions_allowed 
			FROM Survey_Questions 
			WHERE Survey_questions_id = Survey_questions_id_in;
			allowed_cur_rec allowed_cur%ROWTYPE;

		CURSOR likert_count_cur  IS 
			SELECT COUNT(Survey_ques_options_id) as Survey_ques_options_id 
			FROM Survey_Ques_Options 
			WHERE Survey_questions_id = Survey_questions_id_in 
			AND (Survey_ques_options_text = '1'  
			OR Survey_ques_options_text = '2' 
			OR Survey_ques_options_text = '3' 
			OR Survey_ques_options_text = '4' 
			OR Survey_ques_options_text = '5' );		  
			likert_count_cur_rec likert_count_cur%ROWTYPE;
BEGIN
	OPEN freeform_cur;
		FETCH freeform_cur INTO freeform_cur_rec;
	CLOSE freeform_cur;
	
	OPEN allowed_cur;
		FETCH allowed_cur INTO allowed_cur_rec;
	CLOSE allowed_cur;
	
	OPEN likert_count_cur;
		FETCH likert_count_cur INTO likert_count_cur_rec;
	CLOSE likert_count_cur;

        // I'd love to be able to output these values so I can see
them
	dbms_output.put_line(freeform_cur_rec.Survey_questions_freeform);
	dbms_output.put_line(allowed_cur_rec.Survey_questions_allowed);
	dbms_output.put_line(likert_count_cur_rec.Survey_ques_options_id);

	IF freeform_cur_rec.Survey_questions_freeform = '1' then
		 return 'Open Response';
   	ELSIF  allowed_cur_rec.Survey_questions_allowed = '1' or
allowed_cur_rec.Survey_questions_allowed = '-1' then
			IF likert_count_cur_rec.Survey_ques_options_id = '5' then
				 return 'Likert Scale';
			ELSE
				 return 'Radio button';
			END IF;
	ELSE
		 return 'Checkbox';	
	END IF;	

END QuestionTypeForQuestionId;

Thanks
rdalfonso_at_nyc.rr.com Received on Wed Feb 12 2003 - 12:17:19 CST