Search code examples
oracle-databasestored-proceduresplsqlprocedureplsqldeveloper

Can't create procedure and function in sql plus oracle


Can't create any procedure/function in pl/sql. Always getting procedure created with compilation error. Last try with following code

SQL>  CREATE OR REPLACE PROCEDURE REPORT(NAME VARCHAR2, VERSION VARCHAR2,
  2     STARTDATE DATE, ENDDATE DATE) AS 
  3   BEGIN 
  4   END REPORT;
  5   /

Warning: Procedure created with compilation errors.

SQL> show error procedure report;
Errors for PROCEDURE REPORT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2      PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe 

function

CREATE OR REPLACE FUNCTION GETEMPSALARY (EMPNUMBER IN INTEGER) RETURN INTEGER
IS
DECLARE
EMPSALARY INTEGER;
BEGIN
SELECT SAL INTO EMPSALARY FROM EMP WHERE EMP.EMPNO = EMPNUMBER;
RETURN EMPSALARY;
END GETEMPSALARY;
/

any suggestions ?


Solution

  • Your procedure has no statements. At least put a NULL statement in there to get it to compile:

    CREATE OR REPLACE PROCEDURE REPORT(NAME VARCHAR2, VERSION VARCHAR2,
          STARTDATE DATE, ENDDATE DATE) AS 
    BEGIN 
      NULL;
    END REPORT;