plsql : New function inside an existing package compilation and syntax check

Question

How do I compile a new function created inside a package in plsql, to see syntactical error and so on

Answer 1

You can't compile a single function - compile the whole package.

If your concern is about invalidating the whole package in case that function has errors, then

• create a standalone function (i.e. outside of the package)
• debug it
• once it is OK (doesn't have syntax errors, returns result as expected), include it into the package

Answer 2

In addition to @Littefoot's advise I'd say: use a proper GUI. Oracle has a free tool called sql developer. It has a great interface for editing database objects (packages/functions/procedures/triggers). It highlights errors and is very well documented (https://www.thatjeffsmith.com/sql-developer/). Note that is does not point out syntax errors - but once you're a bit used to working with pl/sql they become obvious very quickly.

Answer 3

In Oracle, after compiling a procedure/function/package. If there is an error then the command will return with the message:

ORA-24344: success with compilation error

You can then use:

SHOW ERRORS

or

SELECT * FROM USER_ERRORS;

or, for example, for errors with packages in a specific schema:

SELECT *
FROM   ALL_ERRORS
WHERE  owner = 'SCHEMA_NAME'
AND    type IN ( 'PACKAGE', 'PACKAGE BODY');

Which will list the errors (complete with line numbers and error messages) and you can then debug the procedure/function/package and recompile it.

fiddle