CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
ddss12
ddss12

Reputation: 99

Oracle Stored Procedure compilation error

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;

Upvotes: 0

Views: 861

Answers (2)

Farkhat Batyrbayev
Farkhat Batyrbayev

Reputation: 9

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;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

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:

  1. Removed the size from parameter
  2. Removed the declare keyword - not part of the procedure syntax

  3. 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

  4. Do not use : with the variables and parameters.

Upvotes: 1

Related Questions