Home » SQL & PL/SQL » SQL & PL/SQL » value returned from a function
value returned from a function [message #272629] Fri, 05 October 2007 14:33 Go to next message
phenonitin
Messages: 4
Registered: October 2007
Junior Member

Hi,

I am new PL/SQL programmer and so this question may seem fairly trivial. I have written a very small function that returns the row count from a table. I compile and run the program but Im not sure how I can check the value returned. Is there some query I need to type in to verify the output ? Please advice.

TIA

Re: value returned from a function [message #272636 is a reply to message #272629] Fri, 05 October 2007 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the same query outside the function.

Regards
Michel

[Updated on: Fri, 05 October 2007 14:48]

Report message to a moderator

Re: value returned from a function [message #272637 is a reply to message #272636] Fri, 05 October 2007 14:52 Go to previous messageGo to next message
phenonitin
Messages: 4
Registered: October 2007
Junior Member
Thank you for your response. My concern right now is not the accuracy of the result but I really would like to know if its possible to query the variable that the function returns.
Thanks
Re: value returned from a function [message #272639 is a reply to message #272629] Fri, 05 October 2007 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
it can be displayed by using DBMS_OUTPUT procedure
SQL> DESC DBMS_OUTPUT
Re: value returned from a function [message #272640 is a reply to message #272637] Fri, 05 October 2007 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it's possible.

Regards
Michel
Re: value returned from a function [message #272644 is a reply to message #272637] Fri, 05 October 2007 15:10 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
phenonitin wrote on Fri, 05 October 2007 15:52
Thank you for your response. My concern right now is not the accuracy of the result but I really would like to know if its possible to query the variable that the function returns.
Thanks


A simple
select {your function} from dual;

would do the trick.
Re: value returned from a function [message #272649 is a reply to message #272644] Fri, 05 October 2007 15:32 Go to previous messageGo to next message
phenonitin
Messages: 4
Registered: October 2007
Junior Member
Thank you all for your responses. I guess I havent typed in the function out correctly. I have tried out the above suggested solutions but to no avail

/* Formatted on 2007/10/05 13:15 (Formatter Plus v4.8.7) */
CREATE OR REPLACE FUNCTION count_tgt
   RETURN NUMBER
IS
   v_total   NUMBER (10);                         --initialize variables here
-- main body
BEGIN
   SELECT COUNT (*)
     INTO v_total
     FROM source_table r, source_table b
	 where r.person_uid = b.person_uid 
	 and r.carrier=b.CARRIER;
dbms_output.put_line (v_total);

   NULL;                       -- allow compilation
   RETURN v_total;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;                                  -- enter any exception code here
      RETURN NULL;
	  
END count_tgt;


Im certain there must be some error on my part.
Thanks a bunch
Re: value returned from a function [message #272651 is a reply to message #272649] Fri, 05 October 2007 16:02 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Hey there,

Try something like this:

SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
dbms_output.put_line('The value is "' || count_tgt || '"');
END;


That should give you output like:

The value is "<function output>"

[Updated on: Fri, 05 October 2007 16:02]

Report message to a moderator

Re: value returned from a function [message #272654 is a reply to message #272649] Fri, 05 October 2007 16:14 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

EXCEPTION
WHEN OTHERS
THEN
NULL; -- enter any exception code here
RETURN NULL;



You don't want to do the above bit of coding. This is Evil. You are hiding the actual error, unless and until that is your intention.

Try without the above statement. You will definitely find an answer to your question.

Regards

Raj
Previous Topic: SELECT email address and only display username Array? Delimiter?
Next Topic: Using variables in a SELECT statement - Special Offer!
Goto Forum:
  


Current Time: Sat Dec 10 11:14:49 CST 2016

Total time taken to generate the page: 0.12385 seconds