Oracle PL/SQL stored procedure

Question

I'm trying to create two stored procedure for project, but getting error on both Tables:

Member (MemberID, MembershipID, name, city.....)
RentalQueue (MovieID, DVDID, DateAdded)
DVD (DVID ID, Name..)
Rental(RentalID, MemberID, DVDID RequestDate,ShippedDate,ReturnDate)

two stored procedure description and code

1.) PL/SQL sp that adds a title to the customer’s rentalqueue. This procedure should take as IN parameters the customer ID and movie title ID as well as the location of where the movie is in the queue. The procedure should also make sure that no duplicate titles can be added, you will need to add some error handling in your code.

Create procedure add_to_queue (customerID number, titleID number) as
v_count number;
begin
        select count(memberid,dvdid) 
        from rentalqueue 
        where memberid=customerid and dvdid=titleid;

     if v_count = 1 then
        raise_application_error(20000, 'This film is already  in the rentalqueue for 
        this user');
     else 
          execute immediate ’insert into rentalqueue          
          values(’||customerid||’,’||titleid||’,’||sysdate||’)’;
     end if;
end;

2.) PL/SQL stored procedure that deletes a title from a customer’s rental queue once it is requested to ship. This procedure should take as IN parameters the customer ID and movie title ID.

    Create procedure delete_from_queue (customerID number, titleID number) as
    v_count number;
begin
        select count(memberid,dvdid) 
        from rentalqueue 
        where memberid=customerid and dvdid=titleid;

        if v_count = 1 then
           execute immediate ’delete from rentalqueue 
           where memberid=’||customerid||’ and dvdid=’||titleid;
        else 
        raise_application_error(20000, 'This film was not in the rentalqueue for this  
        user');
       end if;
 end;

Compile ErrorI get on both is below. I know that First two error has to do something with SELECT statement i do, but can't figure out what is wrong.

Error(4,20): PL/SQL: SQL Statement ignored
Error(4,27): PL/SQL: ORA-00909: invalid number of arguments
Error(6,41): PLS-00103: Encountered the symbol "’" when expecting one of the
    following:     ( - + case mod new not null <an identifier>    
<a double-quoted delimited-identifier> <a bind variable>    continue avg count current
 exists max min prior sql stddev    sum variance execute forall merge time timestamp 
interval    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string
 literal with character set specification>    <an alternatively

Answer 1

Try to replace ’ with ':

create or replace procedure add_to_queue (customerID number, titleID number) as

   v_count number;

begin

   select count(memberid) -- Cannot count two columns in one. 
     -- Must select into in PL/SQL
     into v_count
     from rentalqueue 
    where memberid = customerid 
      and dvdid = titleid;

   if v_count = 1 then
      -- User defined errors need to be prefixed with a minus (-)
      raise_application_error(-20000,'This film is already in the rental queue.');
   else 
      -- No dynamic SQL
      insert into rentalqueue -- May need to add column names.  
      values (customerid, titleid, sysdate);
   end if;

end;

You don't seem to need to use dynamic SQL though. I've removed the following from your first procedure and used a normal DML statement instead.

execute immediate 'insert into rentalqueue          
values('||customerid||','||titleid||','||sysdate||')';

For second procedure you can use this instead:

delete from rentalqueue 
 where memberid = customerid
   and dvdid = titleid;

Answer 2

This is not the right symbol - ’

This is - '

Try Editing the Procedures in Toad or SQL Developer for better results. These IDEs will underline any possible syntactical errors with a red line.