Number to character conversion error in trigger. [ORACLE 11G ]-CodePudding

I'm trying to make a trigger that returns an error with a specific message when the user tries to insert a new record to Addresses table and provides the wrong post code. Two IF statements check if the number of characters in the given post-code is right (it should be 6: 2 numbers, 1 dash and 3 numbers). Another IF statement checks if the position of the dash is 3. When I try to insert a post-code with too few characters (for example '12932') it works fine and returns the right message. The problem begins when I provide the post-code in which the dash position is incorrect (for example '1-9000'). In such case it returns an error (PL/SQL: numeric or value error: character to number conversion error, line 15). What could be the reason? Because personally I can't find any char to number conversions. Thanks.

Code:

CREATE OR REPLACE TRIGGER post_code_trigger
BEFORE INSERT OR UPDATE ON Addresses
FOR EACH ROW

DECLARE
code_length NUMBER; 
index_of_dash NUMBER;
code_tmp VARCHAR(6);

BEGIN
code_tmp := :NEW.Post_code;
code_length := LENGTH(code_tmp);
index_of_dash := INSTR(code_tmp, '-');

IF(code_length <> 6) THEN
    RAISE_APPLICATION_ERROR(-20000, 'Post code contains the wrong number of characters.');
ELSE
    IF (index_of_dash <> 3) THEN
        RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain '-' character or it is in the wrong posiition. Sample post code "26-500" ');
    END IF;
    
END IF;

END;
/


INSERT INTO Addresses (address_ID, city, post_code, province, street_name, house_number) VALUES (2, 'Warsaw','1-9000','mazowieckie','Sylvester Stallone Street', 67);

CodePudding user response：

In the body of the code, indekx_of_dash does not match index_of_dash in the declaration. Once you fix that typo, it works.

It can be simplified to:

CREATE OR REPLACE TRIGGER post_code_trigger
BEFORE INSERT OR UPDATE ON Addresses
FOR EACH ROW
DECLARE
BEGIN
  IF LENGTH(:NEW.Post_code) <> 6 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Post code contains the wrong number of characters.');
  ELSIF INSTR(:NEW.Post_code, '-') <> 3 THEN
      RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain "-" character or it is in the wrong posiition. Sample post code "26-500" ');
  END IF;
END;
/

But you probably don't want to use a trigger and just want a CHECK constraint.

ALTER TABLE addresses ADD CONSTRAINT addresses_postcode_format
  CHECK (post_code LIKE '??-???');

db<>fiddle here

CodePudding user response：

Ok, I found a solution. Firstly I simplified my code according to the MT0's answer. Then I deleted the '-' part from the error message. Now everything works as it should. Thank you.

IF INSTR(:NEW.Post_code, '-') <> 3 THEN
        RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain a dash character or it is in the wrong posiition. Sample post code "26-500" ');

instead of

ELSIF INSTR(:NEW.Post_code, '-') <> 3 THEN
      RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain '-' character or it is in the wrong posiition. Sample post code "26-500" ');

CodePudding user response：

When you originally posted your question you had this line:

RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain "-" character or it is in the wrong posiition. Sample post code "26-500" ');

Your real code actually has (but with Polish text):

RAISE_APPLICATION_ERROR(-20000, 'Post code doesnt contain '-' character or it is in the wrong posiition. Sample post code "26-500" ');

The problem then is the '-' in the middle of that. The single quotes around the dash end one string, and start another; and the dash is now outside the string literal(s), so Oracle interprets it as a minus sign. That causes it to implicitly convert the two shorter string literals either side to numbers, so they can be subtracted from each other. So you're really doing:

to_number('Post code doesnt contain ')
-
to_number(' character or it is in the wrong posiition. Sample post code "26-500" ')

The error is occurring when it tries to convert those strings, because they clearly do not represent numbers.

With the dash enclosed in double-quotes instead the whole thing is one string literal again, so there is no confusion or attempt to perform a calculation.

Incidentally, you seem to have avoided an issue with quotes with doesnt instead of doesn't, though presumably only in the translated version. But if you wanted to use single quotes within a string, you can either escape them:

'Post code doesn''t contain ''-'' character or it is in the wrong position. Sample post code ''26-500'''

or use the alternative quoting mechanism:

q'[Post code doesn't contain '-' character or it is in the wrong position. Sample post code '26-500']'

db<>fiddle demo