Calling a function that returns a boolean in PL/SQL 1

[frumpus]

I'm hoping to get some help with the syntax for calling a function (provided by 3rd party software) that returns a boolean value.

This is the full extent of the documentation provided on the function by the third party.

f_validate_answer 

FUNCTION f_validate_answer(p_pidm   gobansr.gobansr_pidm%TYPE,
                           p_num    gobansr.gobansr_num%TYPE,
                           p_answer gobansr.gobansr_ansr_desc%TYPE)
  RETURN BOOLEAN

Parameters  
 p_pidm    NUMBER(,) Required Key
 p_num    NUMBER(,) Required Key

It should be fairly simple for me to enter a call to this function in Oracle SQL Developer in a way that works returns a value, but I can't find any useful help on doing this. All the help I can find is on writing functions and procedures. I just want to call one!

Any suggestions?

 

[frumpus]

This is an example of one of the many things I've tried.

DECLARE v_result BOOLEAN;
IF gb_pin_answer.f_validate_answer(P_PIDM => 12345, P_NUM => 1, P_ANSWER => 'someanswer') THEN 
  v_result := TRUE;
ELSE
  v_result := FALSE;
END IF
RETURN v_result;
end;

This returns the following error on line 2.

Error report:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "IF" to continue.
ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "RETURN" when expecting one of the following:

   ;
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Am I even in the ballpark here?

 

[frumpus]

Ok, so I was missing a 'begin' in the above attempt. I added that and now I have this:

DECLARE v_result BOOLEAN;
BEGIN
IF gb_pin_answer.f_validate_answer(P_PIDM => 15022, P_NUM => 1, P_ANSWER => '101996') THEN 
  v_result := TRUE;
ELSE
  v_result := FALSE;
END IF;
RETURN v_result;
end;

Which gives me this error:

Error report:
ORA-06550: line 8, column 1:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

So I removed the variable name from the RETURN statement, giving me this:

DECLARE v_result BOOLEAN;
BEGIN
IF gb_pin_answer.f_validate_answer(P_PIDM => 15022, P_NUM => 1, P_ANSWER => '101996') THEN 
  v_result := TRUE;
ELSE
  v_result := FALSE;
END IF;
RETURN;
end;

Which at long last DOES compile, but only tells me 'anonymous block completed' and does not give me value of v_result.

Clearly RETURN doesn't do what I thought it did. How can I return this value?

 

[LKBrwnDBA]

Am I even in the ballpark here?

No, you need to go back and study the correct syntax.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[frumpus]

Obviously.

 

[frumpus]

I should just be able to do this:

select gb_pin_answer.f_validate_answer(P_PIDM => 12345, P_NUM => 1, P_ANSWER => 'someanswer') from dual;

But for some reason boolean functions are just cranky i guess. That gives me this error.

ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
06552. 00000 - "PL/SQL: %s"
*Cause:
*Action:
Error at Line: 1 Column: 7

 

[frumpus]

The select from dual approach appears to fail because the function returns a boolean type, but boolean is not a valid sql type. It is only valid for pl/sql. So I am right back where I started. Not knowing how to put the value into something I can actually access.

 

[carp]

You may have to forgo the BOOLEAN if you are going to try to access this from SQL. Traditionally, you would use 0 for false and 1 for true.

Also, depending on how you were trying to invoke the block you posted, you could be running into the same problem.
Try running this from the command line/TOAD/whatever tool you are using:

DECLARE v_result NUMBER;
BEGIN
IF gb_pin_answer.f_validate_answer(P_PIDM => 15022, P_NUM => 1, P_ANSWER => '101996') THEN
  v_result := 1;
ELSE
  v_result :=0;
END IF;
dbms_output.put_line('Result: '|| v_result);
end;

If running this from SQL*Plus, you will probably also need to precede the above with
SET SERVEROUTPUT ON

The results will show up in your dbms_output buffer.

 

[frumpus]

That helped a lot thank you. Still have a small hurdle to overcome but making progress. I am using Oracle SQL Developer for the moment just to get this bit straightened out and yes, I did have to include the SET SERVEROUTPUT ON. With that it gives me:

Result: 1

...as expected. The problem is that ultimately I need to invoke this from vbscript in an asp web page. For that to work, (at least in the way I know how to do it) I need the result to come back as a record set object as if from a SELECT query.

So last night about 2 am I figured out that I'd would have to set v_result as something other than boolean and came up with this idea.

SELECT v_result FROM (DECLARE v_result varchar2 (1);
                      BEGIN
                      v_result := 'F';
                      IF gb_pin_answer.f_validate_answer(P_PIDM => 15022, P_NUM => 1, P_ANSWER => '101996') THEN 
                        v_result := 'T';
                      END IF;
                      RETURN;
                      end;)

The problem here is that Oracle doesn't like the semicolons inside the function. It gets to the end of line 1, finds the semicolon and craps out, telling me I'm missing a right parenthesis.

Any idea how I can get my result as a traditional SQL recordset? All I can think of at this point is creating a permanent function that returns a varchar or number to call the existing function that returns a boolean so I can just select my function from dual. That is frowned upon here as this is a third party database and the DBA doesn't like modding it.

I need a cocktail.

 

[frumpus]

SOLVED

In the end we went ahead and wrote the wrapper function as follows:

create or replace function rly_dumb_function (pidm_in number, p_num number, p_answer char)
RETURN CHAR as
BEGIN
RETURN CASE when gb_pin_answer.f_validate_answer(pidm_in, p_num, p_answer)
THEN 'Y'
ELSE 'N' END;
end;

So now I can just select that from dual and get on with my life.

This was a helpful blog post if anyone comes across this thread with a similar issue.

http://oraclequirks.blogspot.com/2009/02/ora-06553-pls-382-expression-is-of.html