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.

Answer 1

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

Answer 2

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.