Re: debugging an Oracle function??

From: Tim X <timx_at_spamto.devnul.com>
Date: 13 Feb 2003 13:45:36 +1100
Message-ID: <87smusq4of.fsf@tiger.rapttech.com.au>

>>>>> "Rich" == Rich D <rdalfonso_at_nyc.rr.com> writes:

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

 Rich> All I wanna know is how do I debug/test this function in SQL

 Rich> PLus. I need to see the values that my cursors are returning,  Rich> certain values of variables, etc

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

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

do something like this (not tested)

SET SERVEROUTPUT ON SIZE 1000000 DECLARE
   result VARCHAR2(256);
   parameter_in NUMBER := 23;
BEGIN
   dbms_output.put_line('Calling my_func');    result := my_func(parameter_in);
   dbms_output.put_line('Return value is '||result); EXCEPTION
  WHEN OTHERS THEN
     dbms_output.put_line(SQLERRM);
END;
/

This will print out

Calling my_func
<the dbms_output.put_line lines from within your function> Return value is <whatever the value is returned>

The SIZE 1000000 sets the buffer size for dbms_output.

Note that if you want to execute a procedure, you can just do

exec my_proc

from within sqlplus - you don't need the DECLARE....BEGIN...END structure to execute a function because it is not returning anything, so exec will do.

The DECLARE part is only needed if you want to declare some variables in your anonymous block. For example, if I just wanted to execute a procedure I could also do

BEGIN
   my_proc;
END;
/

I used the DECLARE in the first example, because it is a function being called and I wanted to define a variable to put the result in. I could probably just have done

exec dbms_output.put_line(my_func);

since it is just returning a VARCHAR2 which wold be printed by dbms_output and as it is being passed to dbms_output.put_line, I don't need to declare a variable to put the return value into. Note however that dbms_output.put_line can be a bit picky about its arguments - I've seen the following faile

dbms_output.put_line('Return value is '||my_number_returning_func);

because even though there are overloaded versions of put_line to handle arguments of both VARCHAR2 and NUMBER (as well as others), it was not able to perform the implicit cast to transform the number returned by the function into a VARCHAR2 type. However, the following works

dbms_output.put_line('The return value
'||to_char(my_number_returning_func));

I guess thats more than you wanted eh?

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Wed Feb 12 2003 - 20:45:36 CST