Oracle PL/SQL Compare Strings in Procedure

Question

With this table:

CREATE TABLE HR.MSG_USER ( 
ID                   number(38)  NOT NULL,
NAME                 varchar2(100)  NOT NULL,
PASS                 varchar2(10)  NOT NULL,
LOGIN                varchar2(20)  NOT NULL,
CONSTRAINT IDX_USER_LOGIN_UNIQUE UNIQUE ( LOGIN ) ,
CONSTRAINT PK_USER PRIMARY KEY ( ID ) ,
CONSTRAINT IDX_USER_NAME_UNIQUE UNIQUE ( NAME ) 
);

I got this procedure created:

create or replace procedure new_user ( login IN VARCHAR, name IN VARCHAR, pass IN VARCHAR) is

rowsFound number;
ERR_NULL            exception;
ERR_NAME_TOO_LONG   exception;
ERR_NAME_DUPLICATED exception;
ERR_UNIQUE          exception;

PRAGMA EXCEPTION_INIT(ERR_NULL,            -20001);
PRAGMA EXCEPTION_INIT(ERR_NAME_TOO_LONG,   -20002);
PRAGMA EXCEPTION_INIT(ERR_NAME_DUPLICATED, -20003);
PRAGMA EXCEPTION_INIT(ERR_UNIQUE,          -20013);

begin

  if (login is null) or (name is null) or (pass is null) then
    raise_application_error(-20001, 'Datos de Usuario Nulos');
  end if; 

  if (LENGTH(name) > 100) then
    raise_application_error(-20002, 'Nombre Usuario Demasiado Largo');
  end if; 

  select count(*) into rowsFound from MSG_USER where NAME = name;
  if rowsFound >= 1 then
        raise_application_error(-20013, 'Usuario Ya Existe: Name');
  end if;

  select count(*) into rowsFound from MSG_USER where LOGIN = login;
  if rowsFound >= 1 then
        raise_application_error(-20013, 'Usuario Ya Existe: Login');
  end if;

  INSERT INTO MSG_USER ( "ID", "NAME", "PASS", "LOGIN") VALUES ( seq_MSG_USER_id.nextval, name, pass,login );

  dbms_output.put_line('Usuario Creado: '||name);
  commit;

end;
/

When i insert a user with exec:

   exec new_user('testL1','testN1','testP1');
   exec new_user('testL2','testN2','testP2');

The second one fails, with a ORA-20013; it means that (testL1 = testL2)=True. But using:

select count(*) from MSG_USER where NAME = 'testN2';

Count it's equals 0. I don't know why; can someone help me to correct my procedure?

score 3 · Answer 1 · answered Jun 12 '17 at 12:21

What do you think the (logical) value of NAME = name is?

I can see how you could make this mistake... perhaps you grew up coding in C or similar languages, where capitalization matters. Not so in SQL and PL/SQL.

Do not use the same name for table columns and for your procedure parameters. And understand that PL/SQL object names are case insensitive. For the procedure, change - for example - to p_name, p_login, etc.

MT0 · Accepted Answer · 2017-06-12T12:26:14.270

Your bind variable and column have the same name. Change the bind variable to have a different name.

create or replace procedure new_user (
  i_login IN VARCHAR2, -- Use VARCHAR2 not VARCHAR
  i_name  IN VARCHAR2,
  i_pass  IN VARCHAR2
)
is
  rowsFound number;
  ERR_NULL            exception;
  ERR_NAME_TOO_LONG   exception;
  ERR_NAME_DUPLICATED exception;
  ERR_UNIQUE          exception;

  PRAGMA EXCEPTION_INIT(ERR_NULL,            -20001);
  PRAGMA EXCEPTION_INIT(ERR_NAME_TOO_LONG,   -20002);
  PRAGMA EXCEPTION_INIT(ERR_NAME_DUPLICATED, -20003);
  PRAGMA EXCEPTION_INIT(ERR_UNIQUE,          -20013);
begin
  if (i_login is null) or (i_name is null) or (i_pass is null) then
    raise_application_error(-20001, 'Datos de Usuario Nulos');
  end if; 

  if (LENGTH(i_name) > 100) then
    raise_application_error(-20002, 'Nombre Usuario Demasiado Largo');
  end if; 

  select count(*) into rowsFound from MSG_USER where NAME = i_name;
  if rowsFound >= 1 then
        raise_application_error(-20013, 'Usuario Ya Existe: Name');
  end if;

  select count(*) into rowsFound from MSG_USER where LOGIN = i_login;
  if rowsFound >= 1 then
        raise_application_error(-20013, 'Usuario Ya Existe: Login');
  end if;

  INSERT INTO MSG_USER ( "ID", "NAME", "PASS", "LOGIN") VALUES ( seq_MSG_USER_id.nextval, i_name, i_pass, i_login );

  dbms_output.put_line('Usuario Creado: '||i_name);
end;
/

Also, you should not put a COMMIT statement in the procedure - call it after the procedure as it will allow you to bundle multiple procedure calls into a single transaction and roll them all back simultaneously.

Oracle PL/SQL Compare Strings in Procedure

2 Answers2