PLS-00103 Oracle stored procedure error

Question

I am new to stored procedures. I am trying to run stored procedure and getting these errors: I am getting PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure... PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: * & = - + < / > at in is mod remainder not rem then... I have tried searching for what causes these errors and for examples similar to this, but results were not sufficient. Any clues as to why these errors are happening? here is the code:

CREATE OR REPLACE PROCEDURE LIST_ACTIONS_CHECK_ADD 
(
  LISTNAME IN VARCHAR2  
) AS 
BEGIN
 DECLARE CNT NUMBER;
 SELECT COUNT(LIST_NAME) INTO CNT FROM LISTS_MASTER WHERE LIST_NAME = LISTNAME;
IF (CNT > 0)
 RETURN 1
ELSE
 RETURN 0
END IF;
END LIST_ACTIONS_CHECK_ADD;

New Code:

CREATE OR REPLACE PROCEDURE LIST_ACTIONS_CHECK_ADD 
(
  P_LISTNAME IN VARCHAR2  
) 
AS 
 L_CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO L_CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = P_LISTNAME;
 IF (L_CNT > 0)
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

Answer 1

The skeleton of a stored procedure declaration is

CREATE OR REPLACE PROCEDURE procedure_name( <<parameters>> ) 
AS
  <<variable declarations>>
BEGIN
  <<code>>
END procedure_name;

In the code you posted,

1. You put the BEGIN before the variable declarations
2. You have an extraneous DECLARE-- you would only use that if you are declaring a PL/SQL block that doesn't involve a CREATE.
3. You are missing semicolons after your RETURN statements.
4. A procedure cannot return a value. If you want to return either a 1 or a 0, you probably want a function, not a procedure. If you need a procedure, you can declare an OUT parameter.
5. You are missing the THEN after the IF

It sounds like you want something like

CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD 
(
  LISTNAME IN VARCHAR2  
) 
  RETURN NUMBER
AS 
 CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = LISTNAME;
 IF (CNT > 0)
 THEN
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

Note that as a general matter, you are generally better off using some sort of naming convention to ensure that parameters and local variables do not share the name of a column. Trying to figure out whether LISTNAME is a parameter or a column name and what the difference between LIST_NAME and LISTNAME is will generally confuse future programmers. Personally, I use a p_ prefix for parameters and a l_ prefix for local variables. I would also suggested using anchored types-- lists_master.list_name%type if that is what is being passed in

CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD 
(
  P_LIST_NAME IN lists_master.list_name%type
) 
  RETURN NUMBER
AS 
 L_CNT NUMBER;
BEGIN
 SELECT COUNT(LIST_NAME) 
   INTO L_CNT 
   FROM LISTS_MASTER 
  WHERE LIST_NAME = P_LIST_NAME;
 IF (L_CNT > 0)
 THEN
   RETURN 1;
 ELSE
   RETURN 0;
  END IF;
END LIST_ACTIONS_CHECK_ADD;

Answer 2

• (Correction #1) You cannot return a value in a procedure; LIST_ACTIONS_CHECK_ADD should be dropped and declared as a function in order to return a NUMBER
• (Correction #2) You need to move the declaration of CNT as follows (see below)
• (Correction #3) You need semicolons on the return statements:
• (Correction #4) You need a THEN after IF (CNT > 0) (see below):

DROP PROCEDURE LIST_ACTIONS_CHECK_ADD;
CREATE OR REPLACE FUNCTION LIST_ACTIONS_CHECK_ADD
(
   LISTNAME IN VARCHAR2  
) 

RETURN NUMBER AS 

  CNT NUMBER;
BEGIN
    SELECT COUNT(LIST_NAME) INTO CNT FROM LISTS_MASTER WHERE LIST_NAME = LISTNAME;
    IF (CNT > 0) THEN
       RETURN 1;
    ELSE
       RETURN 0;
    END IF;
END LIST_ACTIONS_CHECK_ADD;

---

This Can be executed from SQLPLUS as:

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
    V_RESULT NUMBER;
BEGIN

    V_RESULT := LIST_ACTIONS_CHECK_ADD('X');
    DBMS_OUTPUT.PUT_LINE('RESULT: ' || V_RESULT);

END;