PL/SQL: How to check address NOT NULL but can take -(hyphen) instead being NULL

610 pts.
Tags:
NULL
PL/SQL
PL/SQL procedure
Hi Experts, How can we make a check on an attribute not null but can take - rather I have address 3 which can't take null instead - would be fine how this can be check n PL/SQL procedure I can check IF address 3 IS NULL THEN ... END IF But i want to test with test case tat it can't take NULL but can take '-' hyphen instead please guide how can i achieve this .

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello Inprise.

You asked this similar question yesterday.

Could you please tell us if the answer provided was not useful, or your question was misunderstood ?

It would be also useful to know why you can’t use the IF – THEN -ENDIF approach.

Examples always help. Could you provide some example, or tell us what exactly you want your procedure to do when the address is null (please be as specific as possible)?

——————————-

I would create a before insert trigger (code below assumes table name is ADDRESS)

create or replace trigger trg_address_bi
before insert
on address
referencing NEW as new OLD as OLD
For each row
begin
if :new.address3 is null then
:new.address3 := '-';
end if;
end;

-Dwaltr

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Inprise
    Hi Experts I am still facng issue with this I need to make a check parameter varaible in a Pl/SQL procedure In Add() i have street 3 -- parameter variable,i should check it shud compulsorly take some alphanumeric value or hyphen How can i make this check Pls guide me
    610 pointsBadges:
    report
  • carlosdl
    We want to help, but we need you to provide as much information as possible regarding your situation. In the answer section, a couple of questions were asked. Please answer them. "You asked this similar question yesterday. Could you please tell us if the answer provided was not useful, or your question was misunderstood ? It would be also useful to know why you can't use the IF - THEN -ENDIF approach. Examples always help. Could you provide some example, or tell us what exactly you want your procedure to do when the address is null (please be as specific as possible) ?" Best regards,
    70,200 pointsBadges:
    report
  • Inprise
    i can't use trigger,i hav to implement IF... THEN approach only within the procedure I used IF (p_street 3 IS NULL) THEN raise_app_err(.....) ELSE IF .......this s the place where i shud ensure to check street 3 shud take only alphanumberic or hyphen so i tried to use REGEXP_LIKE(p_street 3,'[^[:alnum:]]',i) ... not sure this s correct approach howver i need to check else hyphen (-) not sure how to check this My procedure add the records to table which hav street 3 as attribute which should not be NULL and shud either be alphanumberic or hyphen Hope i now given the clarity on my query please guide me on this
    610 pointsBadges:
    report
  • carlosdl
    Thanks for clarifying it. The following pattern should do the trick:
    '^[a-zA-Z0-9 -]+$'
    If you want to use class operators, this should work:
    '^[[:alnum:] -]+$'
    So, you could use this validation:
    if  not regexp_like(p_street3,'^[a-zA-Z0-9 -]+$') then
      dbms_output.put_line('invalid characters');
    end if;
    or this:
    if not regexp_like(p_street3,'^[[:alnum:] -]+$','i') then
      dbms_output.put_line('invalid characters');
    end if;
    Hope this helps.
    70,200 pointsBadges:
    report
  • Inprise
    yes it helped me thanks howver how abt blank space,h can i check because ,as of now it takes even blank space in test data eg '123 - Asd' -- this is my test data which is passed successfully i want to check it won't allow even blank space
    610 pointsBadges:
    report
  • carlosdl
    Ok, I deliberately added the blank space to the pattern, since I thought it should be allowed. Just remove it from the pattern. It is located just before the hyphen. So, the patterns would look like these: '^[a-zA-Z0-9-]+$’ or '^[[:alnum:]-]+$’
    70,200 pointsBadges:
    report
  • Inprise
    thanks it helped n resolving my checks .thanks for great help !!!!
    610 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following