tailieunhanh - Oracle Built−in Packages- P104

Oracle Built−in Packages- P104: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Recompile the procedure in the SCOTT schema that shows employees. SQL exec PROCEDURE USER SHOWEMPS Notice that I pass in all arguments in uppercase to ensure a match. 2. Recompile the body of the empmaint package in the SALLY schema. SQL exec PACKAGE BODY SALLY EMPMAINT Here too I pass in all arguments in uppercase to ensure a match. 3. Suppose that I had created a procedure as follows CREATE OR REPLACE PROCEDURE Aabc IS BEGIN END Try it You will discover that this syntax will be acceptable to the compiler It turns out that if you enclose your identifier be it a table name column name program name or variable name in double quotes all of the normal rules are suspended. Then I can recompile this program with the following command SQL exec PROCEDURE USER abc Strange but true Exceptions The ALTER_COMPILE procedure may raise any of the following exceptions ORA-20000 Insufficient privileges or object does not exist. You must either own the specified object or be granted the CREATE ANY PROCEDURE privilege. ORA-20001 Remote object cannot compile. You can only recompile objects on the local database instance. ORA-20002 Bad value for object type. You need to provide one of the values listed in the previous section. Notice that these exceptions are not defined in the specification of the package. Instead ALTER_COMPILE simply calls RAISE_APPLICATION_ERROR with one of the above error numbers. These error numbers may therefore conflict with your own -20NNN error number usages. If you embed calls to ALTER_COMPILE inside your application or utility watch out for the confusion such conflicts can cause. Example At first glance you might say this of the ALTER_COMPILE procedure Why bother The command is available in SQL Plus. I ll just execute the ALTER PROCEDURE XXX COMPILE command when I need to recompile. Compiling PL SQL Objects 506 Appendix A