PL/SQL ERROR cannot identify what the error is, PSL-00103

Question

I was asked to write a simple procedure, that inserts a new row into a table under certaing condition. The database: table Users - user_id .... table Tracks - track_id .... table Liked - user_id, track_id

The procedure must ensure that a user is in users, the track is in tracks, but both don't appear in the same record in the table Liked.

Have no idea where the errors are from, please help!

List of errors: Error(72,1): PLS-00103: Encountered the symbol "DECLARE" Error(77,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

create or replace Procedure proc_insert_liked 
(p_userID IN liked.user_id%TYPE , p_trackID IN liked.track_id%TYPE) 
AS
test1 liked.user_id%TYPE;
test2 liked.track_id%TYPE;
test3 tracks.track_id%Type;
test4 users.user_id%Type;

cursor c1 is
Select user_id
from liked
where user_id = p_userID;

cursor c2 is
Select track_id
from liked
where track_id = p_trackID;

cursor c3 is
Select track_id
from tracks
where track_id = p_trackID;

cursor c4 is
Select user_id
from users
where user_id = p_userID;

Begin

--opening cursors
open c1;
open c2;
open c3;
open c4;

fetch c1 into test1;
fetch c2 into test2;
fetch c3 into test3;
fetch c4 into test4;
-- if user and track in liked
if (c1%found) AND (c2%found) then
   dbms_output.put_line('User already liked that song');  
else  
-- if track in tracks
     if c3%found then
     -- if user in users
        if c4%found then
           Insert into Liked (user_id, track_id)
           Values (p_userId, p_trackID);
        else
           dbms_output.put_line('no such a user');
        end if;
     else
        dbms_output.put_line('no such a track');
     end if;
end if;


-- closing cursors
close c1;
close c2;
close c3;
close c4;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-      '||SQLERRM);
    END;


Declare
  prompt_user_id liked.user_id%Type :=4795;
  prompt_track_id liked.track_id%Type :=199589;
Begin
  proc_insert_liked(prompt_user_id, prompt_track_id);
end;

Answer 1

If you're running all this as one script, you need a / between the end; of the procedure definition, and the declare of the anonymous block. You should one after the second end; to terminate and submit the anonymous block too, but it looks like your client is assuming there should be one there. (I'm guessing this is SQL Developer?)

...
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-      '||SQLERRM);
    END;
/

Declare
  prompt_user_id liked.user_id%Type :=4795;
...
end;
/

Once you start a PL/SQL block, semicolons are only PL/SQL statement separators. They do not act as terminotrs for the whole block, even after that final end;.

You can read more in the SQL*Plus reference, which largely applies to SQL Developer as well.

Incidentally, using when others to catch exceptions is generally not a good idea, even if you report the actual error via your own raise. You're losing the stack trace that would give you more context and help you identify exactly where the error was encountered.

Answer 2

Another way would be to make use of joins to ensure that the requirements are satisfied instead of opening a pile of cursors:

CREATE OR REPLACE PROCEDURE PROC_INSERT_LIKED
  (p_userID IN LIKED.USER_ID%TYPE,
   p_trackID IN LIKED.TRACK_ID%TYPE)  AS
BEGIN
  -- The procedure must ensure that a user is in users, the track is in tracks,
  -- but both don't appear in the same record in the table Liked.

  FOR aRow IN (SELECT *
                 FROM (SELECT *
                         FROM USERS
                         WHERE USER_ID = p_userID) u
                 INNER JOIN (SELECT *
                               FROM TRACKS
                               WHERE TRACK_ID = p_trackID) t
                   ON (1 = 1)
                 LEFT OUTER JOIN (SELECT *
                                    FROM LIKED
                                    WHERE USER_ID = p_userID AND
                                          TRACK_ID = p_trackID) l
                   ON (1 = 1)
                 WHERE l.USER_ID IS NULL AND
                       l.TRACK_ID IS NULL)
  LOOP
    INSERT INTO LIKED(USER_ID, TRACK_ID)
      VALUES (p_userID, p_trackID);
    EXIT;
  END LOOP;
END PROC_INSERT_LIKED;

This assumes that USER_ID is the primary key (or is at least unique) on USERS; that TRACK_ID is primary or unique on TRACK; and that the combination of (USER_ID, TRACK_ID) is primary or unique on TRACK.

How/why it works: data from the USER table is required so we fetch it first (could have just as easily used TRACK first - no particular reason to choose one over the other). TRACK is also required, so INNER cartesian-join (i.e. no join criteria) it to USER - this is safe to do in this case because we're subquerying for TRACK using the primary key so by the time the rows are joined we've already got the single row that we care about. LIKED is subqueried using its primary key, and is then OUTER cartesian-joined to the previous result - this way we get the results even if there is no matching row in TRACK (which is, after all, what we want). We check in the WHERE clause to ensure that the USER_ID and TRACK_ID from the LIKED subquery are NULL, meaning that no row was found on LIKED for the given USER_ID and TRACK_ID. So, if this query returns a row we know that there is data in USERS matching p_userID, there is data in TRACKS matching p_trackID, and that there is NO data in LIKED matching the pair of (p_userID, p_trackID).

SQLFiddle here

Share and enjoy.