Reputation: 4725
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.
Upvotes: 21
Views: 70405
Reputation: 329
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.
Upvotes: 0
Reputation: 825
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.
Upvotes: 2
Reputation: 2096
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;
/
Upvotes: 28
Reputation: 13866
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!
Upvotes: 5
Reputation: 215
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.
Upvotes: 6