Simple PL/SQL Stored Procedure Not Functioning Properly Using Select Count(*)

Question

I am hoping that someone would be able to help me with my code.

-- This code is supposed to check the database Pages (pname, exit_count(number), entry_count(number)) if a website ('p01') already exists.

-- If the website does exist the code is supposed to return a DBMS message.

--If it does not exist then the website should be added with the pname, exit_count = 0, entry_count = 0.

--The problem is that once I add one website // exec add_pages('p01'); // if I try to add another // exec add_pages('p02'); // I get my DBMS message instead of it adding the website to Pages database.

--Any help or suggestions would be greatly appreciated.

--The code has to be in this following format as it is for a school project.

set serveroutput on size 1000000 format wrap

create or replace procedure add_pages (
    pname in pages.pname%type)
as
    page_name number;
begin   
select count(*) into page_name from pages
    where pages.pname = pname;
if
    page_name > 0 then
    dbms_output.put_line('This Website Already Exists!');
else 
    insert into pages values(pname, 0, 0);

end if;
end;

/

You might try executing SELECT * FROM PAGES ORDER BY PNAME just to be certain that you know what exists in the PAGES table before you start. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Mar 28, 2016 at 15:23
Usually it's not a very good idea to give your PL/SQL parameters and variables the names that are the same as table columns. — mustaccio
– mustaccio, Commented Mar 28, 2016 at 15:36

Justin Cave · Accepted Answer · 2016-03-28 15:42:53Z

2

Don't give parameters names that conflict with columns in tables.

When you have the query

select count(*) 
  into page_name 
  from pages
 where pages.pname = pname;

You probably mean "compare the value in the pname parameter against the value in the pname column of the pages table". In actuality, though, = pname resolves to the column in the table not to the parameter of the same name. So your query just ends up counting all the rows in the table where pname is not NULL.

You could fully qualify your parameter name

select count(*) 
  into page_name 
  from pages
 where pages.pname = add_pages.pname;

Most people, though, would adopt a convention to separate parameter names, variable names, and column names. Personally, I generally use a p_ prefix for parameters and l_ for local variables. But there are many possible naming conventions that avoid the error.

create or replace procedure add_pages (
    p_pname in pages.pname%type)
as
    l_page_count number;
begin   
  select count(*) 
    into l_page_name 
    from pages
    where pages.pname = p_pname;

  if l_page_count > 0 then
    dbms_output.put_line('This Website Already Exists!');
  else 
    insert into pages values(p_pname, 0, 0);
  end if;
end;

answered Mar 28, 2016 at 15:42

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gist Over a year ago

Thanks everybody for your help. It was as simple as renaming my value parameter from something else that existed in my table. This worked right away. Awesome job guys!!.

Collectives™ on Stack Overflow

Simple PL/SQL Stored Procedure Not Functioning Properly Using Select Count(*)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related