Oracle Stored Procedure compilation error

Question

I'm new to PL/SQL. Can anyone please help fix my compilation error? Your help is much appreciated. Also, after I would like to call this procedure to check and add a new user.

create or replace procedure CheckAddUser ( userid in varchar2(20))
as
declare vartmp number;
begin
    SELECT nvl((SELECT distinct 1 FROM crm_admin.LTY_USER_STORE WHERE usr_nm = userid  ), 0) INTO :varTmp FROM dual;    
    IF (:varTmp = 0) THEN
       dbms_output.put_line('the user ' || ':userid' || ' does not exist');

    elsif (:varTmp = 1) THEN
       dbms_output.put_line('the user ' || ':userid' || '  already exist');
    End if;
end;

@Chetan in sqldeveloper, I got a couple of: 1. Error(2,44): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. 2. Error(4,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue. — ddss12
– ddss12, Commented Apr 5, 2017 at 3:54

Gurwinder Singh · Accepted Answer · 2017-04-05 04:00:33Z

Try this:

create or replace procedure checkadduser(userid in varchar2)
as
    vartmp number;
begin
    select coalesce(max(1), 0) into vartmp
    from dual
    where exists (
            select 1
            from crm_admin.lty_user_store
            where usr_nm = userid
            );
    if vartmp = 0 then
        dbms_output.put_line('the user ' || userid || ' does not exist');
    elsif vartmp = 1 then
       dbms_output.put_line('the user ' || userid || '  already exist');
    end if;
end;
/

Changes made:

Removed the size from parameter
Removed the declare keyword - not part of the procedure syntax
Modified the query to stop searching as soon as a row is found and return 1 otherwise 0.
```
select coalesce(max(1), 0) into varTmp
from dual
where exists (
        select 1
        from crm_admin.lty_user_store
        where usr_nm = userid
        );
```
If the usr_nm is unique in your table, this will work well too (this can be used even if it's not unique but can be bit less performant if number of rows per usr_nm can be arbitrarily large):
```
select coalesce(max(1), 0)
into varTmp
from crm_admin.lty_user_store
where usr_nm = userid
```
Do not use : with the variables and parameters.

@ GurV now it's working. Thank you for your help! begin 2 checkadduser('MikeC'); 3 end; 4 / the user MikeC already exist

Farkhat Batyrbayev · Accepted Answer · 2017-04-07 10:27:15Z

Don't use ":" with var. I did some changes, which I would migth use:

--I recommended to change procedure to function, then you can use it in SQL
create or replace 
procedure CheckAddUser ( userid in varchar2)
as
  --Best practics, use self-describing variables
    isuserexist number(1,0); -- vartmp
    message_suff varchar2(30):=' does not exist';
begin
  --Best practics, first check the parameters
  if trim(userid) is null then
    raise_application_error(-20000, 'Param userid is empty');
  end if;

    select count(*) into isuserexist
  from crm_admin.lty_user_store 
  where usr_nm = userid;

  --only one if, reads easier
  if isUserExist > 0 then
    message_suff:= ' already exist';
  end if;

  dbms_output.put_line('the user ' || ':userid' || message_suff);
end;

Thank you for your answer and tips of good practices. It helps! @Farkhat
trim(userid)='' will never be true. Also if we're talking best practice, we can avoid unnecessary brackets around if conditions and count(*) is the standard expression for counting rows, not count(1) (plus it's less to type).

Collectives™ on Stack Overflow

Oracle Stored Procedure compilation error

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related