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

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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

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