create oracle package encountered PLS-00103: Encountered the symbol "CREATE"

21
votes

I am writing an oracle package using Oracle sql developer, I got this compile error:

Error(7,1): PLS-00103: Encountered the symbol "CREATE" .

create or replace
PACKAGE TestPackage AS 
 FUNCTION beforePopulate RETURN BOOLEAN;
 FUNCTION afterPopulate RETURN BOOLEAN;
END TestPackage;

CREATE OR REPLACE PACKAGE BODY TestPackage AS
   FUNCTION beforePopulate RETURN BOOLEAN AS
   BEGIN
      DELETE FROM TEST_1;
      INSERT INTO TEST_1
      SELECT * FROM TEST WHERE VALUE=300;
      COMMIT;
      RETURN TRUE;
     EXCEPTION
       WHEN OTHERS THEN
        RETURN FALSE;
   END;
   FUNCTION afterPopulate RETURN BOOLEAN AS
     BEGIN
         UPDATE TEST SET RESULT="completed" WHERE VALUE=300;
            COMMIT;
         RETURN TRUE;
         EXCEPTION
           WHEN OTHERS RETURN FALSE;
        END;
  END;
END TestPackage;

If I add a / at line 6, the error became:

Error(6,1): PLS-00103: Encountered the symbol "/"

I tired an empty implementation like this:

create or replace 
package package1 as 
END PACKAGE1;

CREATE OR REPLACE 
package body package1 as 
end package1;

I got the same err.

6
oracleplsqlpls-00103
use an IDE, several things wrong here that should jump out in Toad or similar editors, such as: BEGIN afterPopulate should be FUNCTION afterPopulate, select * from TEST should be select blah into v_blah from TEST...other issues as well – tbone
and bookmark this for reference later: docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm – tbone
Thank you, tbone. I even tried an empty implementation like this:create or replace package package1 as END PACKAGE1; CREATE OR REPLACE package body package1 as end package1; I got the same err. – Gary
Run this as a script (put each / on its own line): create or replace package test1 as end test1;/ create or replace package body test1 as end test1;/ – tbone
you can if you...run as a script... oh well, glad u got it working :) – tbone

6 Answers

28
votes

When you have BEGIN, END, etc you are in PL/SQL, not SQL.

A PL/SQL block needs to be terminated with a single ("forward") slash at the very beginning of the line. This tells Oracle that you are done with your PL/SQL block, so it compiles that block of text.

SQL query - terminated by semicolon:

update orders set status = 'COMPLETE' where order_id = 55255;

PL/SQL block - commands separated by semicolon, block is terminated by forward-slash:

create or replace procedure mark_order_complete (completed_order_id in number)
is
begin
     update orders set status = 'COMPLETE' where order_id = :completed_order_id;
end mark_order_complete;
/
6
votes

This worked for me using Oracle SQL Developer:

create or replace PACKAGE TestPackage AS
FUNCTION beforePopulate 
 RETURN BOOLEAN;  
FUNCTION afterPopulate 
 RETURN BOOLEAN;
END TestPackage;
/
CREATE OR REPLACE PACKAGE BODY TestPackage AS    
 FUNCTION beforePopulate 
  RETURN BOOLEAN  AS    
 BEGIN       
  DELETE FROM TESTE;      
  INSERT INTO TESTE       
  SELECT 1,1,1 FROM DUAL; 
  COMMIT;     
  RETURN TRUE;  
 EXCEPTION    
  WHEN OTHERS THEN   
   RETURN FALSE;   
 END;
 FUNCTION afterPopulate 
  RETURN BOOLEAN  AS  
 BEGIN
  UPDATE TESTE SET TESTE='OK' WHERE TESTE='';
  COMMIT;       
  RETURN TRUE;  
 EXCEPTION       
  WHEN OTHERS THEN RETURN FALSE;    
 END; 
END TestPackage;
/

I couldn't get it to run until I actually created the tables and columns it'd use.

5
votes

After a couple hours of frustration I managed to make this stuff work. I had the exact problem as you did.

The solution for me was to run it as a script - not in the package code. Forward slashes work correctly in the SQL worksheet. I'm attaching the difference, I hope it will help you!

enter image description here

2
votes

I had the same problem. I create package using main menu od the left and put package declaration and body inside same .sql file. Problem get solved when I copy all code and paste it into new worksheet and put "/" after end package_name (both after package declaration and body) and then execute worksheet as script.

1
votes

execute package and package body separately with F5

0
votes

I had this problem (Error(6,1): PLS-00103: Encountered the symbol "/" ) when I coppied all the db package code (both procedures headers and implementations) in sqldeveloper into user/packages/MY_PACKAGE_NAME/MY_PACKAGE_BODY instead of copying headers (without '/' at the end) into user/packages/MY_PACKAGE_NAME and implementation (without headers at the top and without '/' at the end) into user/packages/MY_PACKAGE_NAME/MY_PACKAGE_BODY.