Compiling PL/SQL code without executing it

[Nicolas Joyard]

Hello,

I'm looking for a way to compile PL/SQL code without actually executing it. I'm generating PL/SQL files which are then executed using SQL*Plus, but I would like to prevent executing all the files when any of them has a compilation error.

I thought about creating a temporary procedure for each file and then checking its compilation status, but isn't there a more straightforward way ?

Thanks,

Nicolas

[joel garry]

You could DBMS_UTILITY.COMPILE_SCHEMA
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_util.htm#i1002332
and then check all_objects for invalid ones.

Could you be a little more explicit about your requirement?

jg
--
@home.com is bogus.
http://www.forbes.com/sites/oracle/2013/01/11/why-oracle-ceo-larry-ellison-is-so-bullish-on-sun-hardware/

[Mark D Powell]

On Friday, January 11, 2013 12:08:00 PM UTC-5, joel garry wrote:
> On Jan 10, 5:04 am, Nicolas Joyard <joyard.nico...@gmail.com> wrote: > Hello, > > I'm looking for a way to compile PL/SQL code without actually executing it. I'm generating PL/SQL files which are then executed using SQL*Plus, but I would like to prevent executing all the files when any of them has a compilation error. > > I thought about creating a temporary procedure for each file and then checking its compilation status, but isn't there a more straightforward way ? > > Thanks, > > Nicolas You could DBMS_UTILITY.COMPILE_SCHEMA http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_util.htm#i1002332 and then check all_objects for invalid ones. Could you be a little more explicit about your requirement? jg -- @home.com is bogus. http://www.forbes.com/sites/oracle/2013/01/11/why-oracle-ceo-larry-ellison-is-so-bullish-on-sun-hardware/

For individual existing stored procedures and packages the alter procedure owner.proc_name compile would work. Code changes and new code could be syntax checked via create or replace, but I think the question is really concerned with anonymous pl/sql code.

Taking anonymous code and placing it in a create procedure statement seems a potential approach to the problem of checking the syntax without running the code.

It seems like I ought to know another way but nothing else comes to mind except that testing the code is what a test environment is for. Also small chunks of code can be placed in anonymous pl/sql scripts as procedures and called. DLM can be pointed at private copies of the tables or commented out so the code can be repeated.

HTH -- Mark D Powell --

[ddf]

One can also check DBA_INVALID_OBJECTS for such errors. I haven't had time to actually work on this problem to see if there is a way to 'conditionally' compile all objects and exit should one fail compilation. I'll put that on my to-do list after my RMOUG Training Days presentation and my meeting with the Apress editor.


David Fitzjarrell