Receiving error during compile test of procedure

Question

I've written a short procedure when a donor id is input it checks for active pledge based on status field (NUMBER for data type 10 is active, 20 is complete) and is making monthly payments and return boolean value True if all conditions are met and False if not. Before adding the DBMS output it would compile fine but I get error PLS-00306:wrong number or types of arguments in call"

Was feeling pretty good about solving this on my own but not sure where my mistakes are. Also looking for explanation (commented in code) about exception handlers. Thanks in advance for comments and teaching points!

My code:

CREATE OR REPLACE PROCEDURE DDPAY_SP
     (donor_id IN NUMBER, active_pl OUT BOOLEAN)

IS
      pay_count NUMBER;
BEGIN
      SELECT COUNT(*)
         INTO pay_count
         FROM dd_pledge 
         WHERE iddonor = donor_id AND
               idstatus = 10 AND
               paymonths > 1;

      IF pay_count > 1 THEN
         active_pl := TRUE;
      ELSE active_pl := FALSE;
      END IF;
      DBMS_OUTPUT.PUT_LINE('Active Pledge and paymonths: ' || active_pl);

      /* want to add exception but don't understand how to choose
      the handler for blocks of code that are not the Oracle defined
      exceptions--Can someone explain better than book I have? I know    
      code should be:

      EXCEPTION
      WHEN .....
      DBMS_OUTPUT.PUT_LINE('    ');  */

      END;

You don't need to call DBMS_OUTPUT. you have a boolean out parameter. Also, you cannot pass a boolean to DBMS_OUTPUT. What happens when you execute the procedure? — Lalit Kumar B, Jul 01 '15 at 10:33

score 4 · Accepted Answer · answered Jul 01 '15 at 10:42

4

DBMS_OUTPUT.PUT_LINE('Active Pledge and paymonths: ' || active_pl);

You cannot pass a BOOLEAN to DBMS_OUTPUT. You could only pass string arguments, i.e. VARCHAR2 datatype.

This is the reason you get the following error:

PLS-00306:wrong number or types of arguments in call"

Ideally, you would not want any DBMS_OUTPUT to be sent to your client, since you are using an OUT parameter, that would suffice.

If you really want to print the boolean value, then you must convert it into VARCHAR2 datatype.

For example,

SQL> set serveroutput on
SQL> DECLARE
  2    var_bool BOOLEAN;
  3  BEGIN
  4    var_bool := TRUE;
  5    dbms_output.put_line('Status = '||
  6    CASE
  7    WHEN var_bool THEN
  8      'TRUE'
  9    ELSE
 10      'FALSE'
 11    END);
 12  END;
 13  /
Status = TRUE

PL/SQL procedure successfully completed.

SQL>

answered Jul 01 '15 at 10:42

Lalit Kumar B

47,486
13
97
124

I wish the book I was using covered that. The problem I was working on was specific the procedure was to return "Boolean" value True. But looking at it now I don't see why I couldn't make active_proj a CHAR datatype and return that value? This would satisfy the error would it not? And to test the procedure I would return the value in a SELECT/BEGIN statement to validate the procedure worked properly. – allendks45 Jul 01 '15 at 13:42
@allendks45 I woukd rather use a function instead of procedure. It would give the flexibilty to call in SQL, however, you cannot return boolean then as it is only PL/SQL supported datatype. You could just RETURN string from function. – Lalit Kumar B Jul 01 '15 at 14:07
thanks Lalit! The way you explain the concepts helps immensely and from past experiences having the ability to share what you know with others makes that individual better in his/her craft. Almost done with this book but will keep learning with additional resources in the future. – allendks45 Jul 01 '15 at 14:42
@allendks45 All the best! There are few good examples and concepts I update in my blog, you could have a look if it interests you http://lalitkumarb.wordpress.com/posts/ – Lalit Kumar B Jul 01 '15 at 15:13

Receiving error during compile test of procedure

1 Answers1