Forum Stats

  • 3,751,240 Users
  • 2,250,335 Discussions
  • 7,867,355 Comments

Discussions

PL SQL anonymous block procedure Exception Handling Problem?

1028857
1028857 Member Posts: 9
edited Jul 29, 2013 8:13AM in SQL & PL/SQL

Hello,

I am a newbie to PL/SQL.

I am having difficulty trying to handle exceptions for an anonymous PLSQL block procedure I have created that will format any 10 digit block and format it into the following (XXX)-XXX-XXXX.

I need to -

1. handle if there is more than 10 digits

2. handle if there less than 10 digits

3. handle if there are any inappropriate characters (non number)

4. handle if there are no characters.

            I have created the following procedure.  However, my if/else logic attempts or raise exceptions have create errors thus far.

Please advise the following code.

create or replace

PROCEDURE format_phone

    (

    p_phne_no IN OUT VARCHAR2

    ) IS

  extra_digits EXCEPTION; -- Number must be a 10 digit number. Please enter 10 digits

  no_digits EXCEPTION; -- Please enter digits there were no digits inputed.

  invalid_char EXCEPTION; --You have entered an inappropriate character please enter number values 0-9.

  less_digits EXCEPTION; --You have entered too few digits. Please enter a 10 digit phone number.

BEGIN

  p_phne_no :='('    

              || SUBSTR(p_phne_no,1,3) ||

              ')'

              ||'-'||

              SUBSTR(p_phne_no,4,3)

              ||'-'||

              SUBSTR(p_phne_no,7);

  --DBMS_OUTPUT.PUT_LINE (p_phne_no);

EXCEPTION

  WHEN  invalid_char

  THEN

    dbms_output.put_line('You have entered an inappropriate character please enter number values 0-9.');

 

  WHEN no_digits

  THEN

    dbms_output.put_line('Please enter digits there were no digits inputed.');

   

  WHEN less_digits

  THEN

    dbms_output.put_line('You have entered too few digits. Please enter a 10 digit phone number.');

   

  WHEN extra_digits

  THEN

    dbms_output.put_line('You have entered too many digits. Number must be a 10 digit number. Please enter 10 digits.');

END format_phone;

Thank you for your concern.

Tagged:
1028857

Best Answer

  • Pablolee
    Pablolee Member Posts: 405
    Accepted Answer

    >>

    Ok so i need to declare a variable, Then pass parameter p_phne_no to that variable.

    You could, yes, but you don't need to. You could simply test the valueof the passed parameter

    LENGTH(p_phne_no) >10...

    >>--I need 3 more case scenarios in which i test Val. which is still equal to the parameter p_phne_no?

    Yes (or just test p_phne_no).

    1028857
«1

Answers

  • Pablolee
    Pablolee Member Posts: 405

    Well, you haven't told the program to raise any exceptions. How does oracle know what each condition should be to raise an exception?    

  • hemu
    hemu Member Posts: 474

    select

    (case when

    regexp_like (('0123456'),'[[:digit:]]{10}') then 'ok' else 'something wrong' end ) as remark

    from dual

  • Pablolee
    Pablolee Member Posts: 405

    Don't you think that it might have been appropriate to explain to a newbie that what you have posted is a pure SQL solution. Did you consider that what the OP is trying to d is to learn how to work with exceptions, rendering your 'solution' useless?

  • 1028857
    1028857 Member Posts: 9

    Hi thank you for your help.

    @800049

    What function or condition do I use to  check the substr().

    Should I use a IF/else block?

    If  ___   condition

    Raise

    Extra_digits.

  • 1028857
    1028857 Member Posts: 9
    edited Jul 29, 2013 3:48AM

    In my numerous trial and error runs.

    IF SUBSTR(p_phne_no, 1,10) != number

    THEN

        RAISE invalid_char;

    However this is an incorrect syntax.

  • 1028857
    1028857 Member Posts: 9

    will regexp work for pl sql?

  • Pablolee
    Pablolee Member Posts: 405

    OK, you have a couple of issues here apart from your code.

    You say that you run a piece of code and it returns an error.

    1. You only post a snippet of the code

    2. That code does not relate to the code that you first posted.

    3. You don't post the error.

    Here is a quick example of how you might work with exceptions here:

    DECLARE

      too_big EXCEPTION;

      val NUMBER (10);

    BEGIN

      val := 12345;

      CASE

         WHEN LENGTH(val) > 4 THEN

          RAISE too_big;

        ELSE

          dbms_output.put_line('Success');

      END case;

    END;

  • 1028857
    1028857 Member Posts: 9

    Ok I apologize for misclarification. I am missing the connection of how to implement this exception handling in my first post. How will work with the substr() i used in the first post. I need the exception handling to take place before the substr() handles the number.

    I originally posted

    "DECLARE

        CREATE OR REPLACE PROCEDURE format_phone

    (p_phne_no IN OUT VARCHAR2) IS "

    the variable i declare p_phne_no it has no type yet.

    Only that it is being Outputted as a VARCHAR2 in an IN OUT procedure.

    Where do I declare its type?

    Is it more correct to declare a separate variable?

    As it relates to your example.

      too_big EXCEPTION;        is declaring an exception

    which i did

    "extra_digits EXCEPTION;"

    however i did not specify a variable with a number type. or val NUMBER(10);

  • Pablolee
    Pablolee Member Posts: 405
    fb281d46-72f0-4c50-aba5-ff42ccca74c7 wrote:
    
    Ok I apologize for misclarification.[/quote] No apology required. 
    [quote]I am missing the connection of how to implement this exception handling in my first post.[/quote] And my last post shows you an example of how that type of exception handling might be done.
    [quote]How will work with the substr() i used in the first post. [/quote] Why do you feel you need to use the substring for the exception handling? Hint, you don't.
    Note that in my example, I tested the length of the variable to see if it was bigger than 4 characters. If it was, then I wanted to raise the 'too_big' exception. (VERY similar to what you are trying to do.
    [quote]I need the exception handling to take place before the substr() handles the number. [/quote] I agree. My sample shows you an example of how you might do that.
    
    [quote]the variable i declare p_phne_no it has no type yet.[/quote]
    1. It's a parameter, not a variable
    2. Yes it does. have a type. VARCHAR2.
    [quote]Only that it is being Outputted as a VARCHAR2 in an IN OUT procedure.[/quote] Yes, with a type of VARCHAR2.
    [QUOTE]Where do I declare its type?[/QUOTE] You already did; VARCHAR2
    [quote] Is it more correct to declare a separate variable?[/quote] Not necessarily. It's certainly an option.
    
    [quote]
    As it relates to your example. 
      too_big EXCEPTION;        is declaring an exception   -- Yes, I know.
    which i did
    "extra_digits EXCEPTION;"  -- Yes, I saw that.
    however i did not specify a variable with a number type. or val NUMBER(10);[/quote] Irrelevant. I did not produce an analogue to your requirements, I produce an example of how one might test tge size of a value and raise a pre declared exception on that basis. You now need to try to implement that within the parameters of your requirements.
    

    .

    1028857Pablolee
This discussion has been closed.