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

Question

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.

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, Feb 10 '12 at 17:18
and bookmark this for reference later: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm — tbone, Feb 10 '12 at 17:22
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, Feb 10 '12 at 17:26
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, Feb 10 '12 at 17:39
If I add "/", I got error: PLS-00103: Encountered the symbol "/" . so are there any setttings on sql developer? my sql developer version is 3.0.04. I am stuck on this for hours. — Gary, Feb 10 '12 at 17:51
RUN AS A SCRIPT (f5) ... the 2nd icon from the left (looks like a piece of paper with a green go arrow in front of it). — tbone, Feb 10 '12 at 18:36
I figured it out finally. I compiled the create stuff separately, they work. Looks like one can't put the two or many creates in one window to compile. I Thank you, everybody! — Gary, Feb 10 '12 at 19:47
you can if you...run as a script... oh well, glad u got it working :) — tbone, Feb 10 '12 at 19:56

Andrew Wolfe · Answer 1 · 2013-12-06T16:00:12.263

28

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;
/

edited Dec 06 '13 at 16:00

answered Feb 11 '12 at 03:42

Andrew Wolfe

2,020
19
25

Thanks Andrew, your tip helped me to figure out why my package wasn't compiling. – Anna Lam May 04 '12 at 02:26
1

Please let's close this question. Please designate the correct answer @MissPiplup – Dropout May 30 '13 at 00:38
This works like a charm. You should give one simple example about it in this answer. – Ifan Iqbal Nov 18 '13 at 16:31

score 6 · Answer 2 · answered Feb 10 '12 at 17:41

6

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.

answered Feb 10 '12 at 17:41

M. P. R.

215
1
8

I copied your code into my sqldeveloper, I got: Error(7,1): PLS-00103: Encountered the symbol "/" . I am using oracle developer version 3.0.04. – Gary Feb 10 '12 at 17:49
Try creating the table it uses `CREATE TABLE "TESTE" ("teste2" VARCHAR2(5 BYTE), "teste3" VARCHAR2(5 BYTE), "TESTE" VARCHAR2(5 BYTE) ) ) ;` – M. P. R. Feb 10 '12 at 17:51
3

Also, make sure to run it as script, I think it's supposed to be F5. – M. P. R. Feb 10 '12 at 17:58

score 5 · Answer 3 · answered May 09 '13 at 15:09

5

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

answered May 09 '13 at 15:09

Dropout

13,653
10
56
109

The package which I was creating is obviously RDI_API. I have exactly the same structure as you do. This should solve your problem. – Dropout May 09 '13 at 15:11

score 2 · Answer 4 · answered Aug 07 '14 at 07:57

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.

score 1 · Answer 5 · answered Feb 21 '13 at 13:33

1

execute package and package body separately with F5

answered Feb 21 '13 at 13:33

Vishy

11
1

score 0 · Answer 6 · answered Jan 25 '17 at 11:21

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.

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

6 Answers6

Linked