Re: debugging an Oracle function??

Rich D wrote:
> 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

select QuestionTypeForQuestionId(1) from dual;

-- 
Regards, Frank van Bortel