OraFAQ Forum: SQL & PL/SQL » Testing for Numeric when a varchar is passed in

Home » SQL & PL/SQL » SQL & PL/SQL » Testing for Numeric when a varchar is passed in (10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Testing for Numeric when a varchar is passed in [message #621096]

Fri, 08 August 2014 16:18

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

I'm wondering how can you test an input parm for correctness into a Proc/Func.

For ex. I copied a small function from here
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61plsql-512011.html

and it does state that "Assuming that the number passed to this function is a positive integer"

CREATE OR REPLACE FUNCTION 
plch_ceil_and_floor (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;

BUT, what if a input data that was passed in was a character like: "a"

I get error: "Variant conversion error for variable: number_in" thru pl/sql developer

My question:
Is there any way to trap for this error?? I couldn't seem to do it with an exception handler like

CREATE OR REPLACE FUNCTION 
plch_ceil_and_floor1 (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || sqlerrm);
END;

OR, do I have to pass in a varchar and convert the input parm to a number like

CREATE OR REPLACE FUNCTION 
plch_ceil_and_floor2 (number_in IN varchar2)
   RETURN PLS_INTEGER
IS
    v_number NUMBER;
BEGIN
    BEGIN
      -- Assign input string to an Oracle number 
      v_number := number_in;
    EXCEPTION
      -- Oracle caught the variable and it's NOT an Oracle number 
      WHEN OTHERS THEN
        dbms_output.put_line('Input Var is not a number');
        RETURN -1;
    END;
   RETURN CEIL (number_in) - FLOOR (number_in);
END;

Thanks

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621097 is a reply to message #621096]

Fri, 08 August 2014 16:55

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

You misunderstand how procedure/function is called. Calling session issues:

plch_ceil_and_floor(some-expression)

Oracle checks if function plch_ceil_and_floor exists and number of actual parameters in function call matches number of function formal parameters. Then it calculates some-expression and if some-expression datatype doesn't match function parameter type Oracle tries to convert it to proper datatype. If such conversion fails then error is raised. And only if everything check out then and only then Oracle calls function. As you can see, it makes no sense to check if parameter passed to function is of valid type inside the function. So if you issue:

plch_ceil_and_floor('a')

exception will be raised in calling block not in function block.

SY.

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621098 is a reply to message #621096]

Fri, 08 August 2014 17:00

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Hi

Here is an example that might help

SQL> declare
  2    retval pls_integer;
  3    function echo(p_numval in pls_integer)
  4    return pls_integer
  5    is
  6    begin
  7      return p_numval;
  8    end echo;
  9  begin
 10    retval := echo(p_numval=>'a');
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 10


SQL> 
SQL> 
SQL> 
SQL> 
SQL> set serveroutput on
SQL> declare
  2    param_type_mismatch exception;
  3    pragma exception_init(param_type_mismatch, -06502);
  4  --
  5  --
  6    retval pls_integer;
  7    function echo(p_numval in pls_integer)
  8    return pls_integer
  9    is
 10    begin
 11      return p_numval;
 12    end echo;
 13  begin
 14    retval := echo(p_numval=>'a');
 15  exception
 16    when param_type_mismatch then
 17      sys.dbms_output.put_line('param_type_mismatch exception raised');
 18      raise;
 19  end;
 20  /
param_type_mismatch exception raised
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 18

SQL> set serveroutput off
SQL>

And instead of sys.dbms_output.put_line at line 17 in the above code you write your logging procedure which updates your log table (autonomous transaction inside a procedure)

Also have a look at WHEN_OTHERS

[Edit]: Didn't see Solomon's had already given the answer. Smile

[Updated on: Fri, 08 August 2014 17:07]

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621099 is a reply to message #621096]

Fri, 08 August 2014 17:53

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Sorry but I'm still somewhat confused Sad

Quote:

if some-expression datatype doesn't match function parameter type Oracle tries to convert it to proper datatype. If such conversion fails then error is raised...exception will be raised in calling block not in function block.

CREATE OR REPLACE PROCEDURE test_plch_ceil_and_floor(p_number_in IN NUMBER) IS

  param_type_mismatch EXCEPTION;

  PRAGMA EXCEPTION_INIT(param_type_mismatch, -06502);

  retval NUMBER;

  FUNCTION plch_ceil_and_floor(number_in IN NUMBER) RETURN PLS_INTEGER IS
  BEGIN
    RETURN ceil(number_in) - floor(number_in);
  END;

BEGIN
  BEGIN
    retval := plch_ceil_and_floor(number_in => p_number_in);
    sys.dbms_output.put_line('Return value: ' || retval);
  EXCEPTION
    WHEN param_type_mismatch THEN
      sys.dbms_output.put_line('param_type_mismatch exception raised');
      RAISE;
  END;
END test_plch_ceil_and_floor;

In this example, wouldn't

EXCEPTION
    WHEN param_type_mismatch THEN
      sys.dbms_output.put_line('param_type_mismatch exception raised');
      RAISE;

display 'param_type_mismatch exception raised' if an 'a' was passed in?? Could it be that I'm using pl/sql developer "test" window??

Sorry about the "When Others"...

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621100 is a reply to message #621099]

Fri, 08 August 2014 18:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post coded example where YOU pass in 'a' via code which specifies (VAR_IN IN NUMBER)

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621101 is a reply to message #621099]

Fri, 08 August 2014 19:41

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

lott42 wrote on Fri, 08 August 2014 18:53

Sorry but I'm still somewhat confused

Your original example declare function parameter as number and function tries to catch exception for string argument. And I explained why it just can't happen. So answer to your question "OR, do I have to pass in a varchar and convert the input parm to a number like" is yes, you would have to declare function parameter as varchar2 and convert it to number within the function. However I don't see any reason why would you need it.

Now about your latest example. You have procedure test_plch_ceil_and_floor with numeric parameter. Inside procedure you declare a function with numeric parameter and then call that function passing procedure parameter as function parameter. So how you can pass 'a' to function? If you call test_plch_ceil_and_floor('a'), actual and formal parameter type mismatch will be discovered BEFORE procedure test_plch_ceil_and_floor is called and exception will be raised in the block that called procedure test_plch_ceil_and_floor and not in procedure itself, so that:

  EXCEPTION
    WHEN param_type_mismatch THEN
      sys.dbms_output.put_line('param_type_mismatch exception raised');
      RAISE;

will simply never happen.

SY.

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621123 is a reply to message #621101]

Sat, 09 August 2014 08:35

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Thanks everybody. Once I get back to the office on monday, I can test more

Report message to a moderator

Re: Testing for Numeric when a varchar is passed in [message #621210 is a reply to message #621123]

Mon, 11 August 2014 11:50

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

OK, so, I've modified my routines AGAIN slightly. I really just want to know is how to handle if there's a parm type mismatch(in this case a varchar was passed in while the procedure was expecting a number). One routine expects a number and the other a varchar2. I just re-display the input parm.

If I make this call

http://iasdev.ucr.edu/media_test/test_harness.test_number_v?number_in=a

I get this error

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Using this code

PROCEDURE test_number_v(number_in IN VARCHAR2) IS
    v_number_in NUMBER;
  
    param_type_mismatch EXCEPTION;
  
    PRAGMA EXCEPTION_INIT(param_type_mismatch, -06502);
  BEGIN
    v_number_in := to_number(number_in);
  
    htp.p('Result: ' || v_number_in);
  EXCEPTION
    WHEN param_type_mismatch THEN
      htp.p('Error: ' || SQLERRM);
      --RAISE;
  
  END test_number_v;

If I make this call

http://iasdev.ucr.edu/media_test/test_harness.test_number_n?number_in=a

I get this error

Mon, 11 Aug 2014 16:41:12 GMT

Failed to execute target procedure ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 35

  DAD name: media_test
  PROCEDURE  : test_harness.test_number_n
  URL        : http://iasdev.ucr.edu:80/media_test/test_harness.test_number_n?number_in=a
  PARAMETERS :
  ===========
  number_in:
   a

  ENVIRONMENT:
  ============
    PLSQL_GATEWAY=WebDb
    GATEWAY_IVERSION=2
    SERVER_SOFTWARE=Oracle-Application-Server-11g

    Blah..Blah..Blah

Using this code

PROCEDURE test_number_n(number_in IN NUMBER) IS
  
    v_number_in NUMBER;
  
    param_type_mismatch EXCEPTION;
  
    PRAGMA EXCEPTION_INIT(param_type_mismatch, -06502);
  BEGIN
    v_number_in := number_in;
  
    htp.p('Result: ' || v_number_in);
  EXCEPTION
    WHEN param_type_mismatch THEN
      htp.p('Error: ' || SQLERRM);
      --RAISE;
  
  END test_number_n;

So, I was asking is how do I capture if a valid number was passed into a procedure. Should I pass a varchar2 and try to "to_number" it?? I consider the first version a little more elegant response to an end user than the second version.

Example from above...

    v_number_in := to_number(number_in);

Report message to a moderator

Previous Topic:	procedure with loop
Next Topic:	number to date format ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 04 17:16:09 CDT 2025