Oracle show stored procedure compilation errors

oracle show stored procedure compilation errors

Package \ pkg_ta_econtractsdeal.pck; Package created Warning: Package body created with compilation errors SQL> show error Errors for PACKAGE BODY DTL. Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle The SHOW ERRORS command reports any compilation errors. If the compilation fails, use “sho err” on SQL*Plus to display errors. SQL> ALTER PROCEDURE temp_proc_with_bug COMPILE; Warning: Procedure.

youtube video

02 06 Demo Creating \u0026 Compiling Procedures, Errors and Warnings

Share: Oracle show stored procedure compilation errors

Oracle show stored procedure compilation errors
Oracle show stored procedure compilation errors

Oracle show stored procedure compilation errors - apologise

SQLERRM(SQLCODE); END debit_account; PROCEDURE apply_transactions IS /* Applies pending transactions in the table TRANSACTIONS to the ACCOUNTS table. Used at regular intervals to update bank accounts without interfering with input of new transactions. */ /* Cursor fetches and locks all rows from the TRANSACTIONS table with a status of 'Pending'. Locks released after all pending transactions have been applied. */ CURSOR trans_cursor IS SELECT acct_id, kind, amount FROM transactions WHERE status = 'Pending' ORDER BY time_tag FOR UPDATE OF status; BEGIN FOR trans IN trans_cursor LOOP /* implicit open and fetch */ IF trans.kind = 'D' THEN debit_account(trans.acct_id, trans.amount); ELSIF trans.kind = 'C' THEN credit_account(trans.acct_id, trans.amount); ELSE new_status := 'Rejected'; END IF; /* Update TRANSACTIONS table to return result of applying this transaction. */ UPDATE transactions SET status = new_status WHERE CURRENT OF trans_cursor; END LOOP; COMMIT; /* Release row locks in TRANSACTIONS table. */ END apply_transactions; PROCEDURE enter_transaction (acct NUMBER, kind CHAR, amount NUMBER) IS /* Enters a bank transaction into the TRANSACTIONS table. A new transaction is always put into this 'queue' before being applied to the specified account by the APPLY_TRANSACTIONS procedure. Therefore, many transactions can be simultaneously input without interference. */ BEGIN INSERT INTO transactions VALUES (acct, kind, amount, 'Pending', sysdate); COMMIT; END enter_transaction; END bank_transactions;

Packages allow the database administrator or application developer to organize similar routines. They also offer increased functionality and database performance.

Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the following areas:

  • encapsulation of related procedures and variables
  • declaration of public and private procedures, variables, constants, and cursors
  • better performance

Stored packages allow you to encapsulate (group) related stored procedures, variables, datatypes, and so forth in a single named, stored unit in the database. This provides for better organization during the development process.

Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.

The methods of package definition allow you to specify which variables, cursors, and procedures are


Directly accessible to the user of a package.  


Hidden from the user of a package.  

For example, a package might contain ten procedures. You can define the package so that only three procedures are public and therefore available for execution by a user of the package; the remainder of the procedures are private and can only be accessed by the procedures within the package.

Do not confuse public and private package variables with grants to PUBLIC, which are described in Chapter 25, "Controlling Database Access".

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilations can be minimized, resulting in less impact on overall database performance.

A package is dependent on the objects referenced by the procedures and functions defined in its body. Oracle automatically tracks and manages such dependencies. See Chapter 19, "Oracle Dependency Management", for more information about dependency tracking.

When you create a procedure or package, Oracle

  • compiles the procedure or package
  • stores the compiled code in memory
  • stores the procedure or package in the database

The PL/SQL compiler compiles the source code. The PL/SQL compiler is part of the PL/SQL engine contained in Oracle. If an error occurs during compilation, a message is returned.

Oracle caches the compiled procedure or package in the shared pool of the system global area (SGA). This allows the code to be executed quickly and shared among many users. The compiled version of the procedure or package remains in the shared pool according to the modified least-recently-used algorithm used by the shared pool, even if the original caller of the procedure terminates his or her session. See "The Shared Pool" on page 6-6 for specific information about the shared pool buffer.

At creation and compile time, Oracle automatically stores the following information about a procedure or package in the database:

schema object name  

This name identifies the procedure or package. You specify this name in the CREATE PROCEDURE, CREATE FUNCTION, CREATE PACKAGE, or CREATE PACKAGE BODY statement.  

source code and parse tree  

The PL/SQL compiler parses the source code and produces a parsed representation of the source code, called a parse tree.  

pseudocode (P code)  

The PL/SQL compiler generates the pseudocode, or P code, based on the parsed code. The PL/SQL engine executes this when the procedure or package is invoked.  

error messages  

Oracle might generate errors during the compilation of a procedure or package.  

To avoid unnecessary recompilation of a procedure or package, both the parse tree and the P code of an object are stored in the database. This allows the PL/SQL engine to read the compiled version of a procedure or package into the shared pool buffer of the SGA when it is invoked and not currently in the SGA. The parse tree is used when the code calling the procedure is compiled.

All parts of database procedures are stored in the data dictionary (which is in the SYSTEM tablespace) of the corresponding database. When planning the size of the SYSTEM tablespace, the database administrator should keep in mind that all stored procedures require space in this tablespace.

When you invoke a standalone or packaged procedure, Oracle verifies user access, verifies procedure validity, and executes the procedure.

Oracle verifies that the calling user owns or has the EXECUTE privilege on the procedure or encapsulating package. The user who executes a procedure does not require access to any procedures or objects referenced within the procedure; only the creator of a procedure or package requires privileges to access referenced schema objects.

Oracle checks the data dictionary to determine whether the status of the procedure or package is valid or invalid. A procedure or package is invalid when one of the following has occurred since the procedure or package was last compiled:

  • One or more of the schema objects referenced within the procedure or package (such as tables, views, and other procedures) have been altered or dropped (for example, if a user added a column to a table).
  • A system privilege that the package or procedure requires has been revoked from PUBLIC or from the owner of the procedure or package.
  • A required schema object privilege for one or more of the schema objects referenced by a procedure or package has been revoked from PUBLIC or from the owner of the procedure or package.

A procedure is valid if it has not been invalidated by any of the above operations. If a valid standalone or packaged procedure is called, the compiled code is executed. If an invalid standalone or packaged procedure is called, it is automatically recompiled before being executed.

For a complete discussion of valid and invalid procedures and packages, recompiling procedures, and a thorough discussion of dependency issues, see Chapter 19, "Oracle Dependency Management".

The PL/SQL engine executes the procedure or package using different steps, depending on the situation:

  • If the procedure is valid and currently in memory, the PL/SQL engine simply executes the P code.
  • If the procedure is valid and currently not in memory, the PL/SQL engine loads the compiled P code from disk to memory and executes it. For packages, all constructs of the package (all procedures, variables, and so on, compiled as one executable piece of code) are loaded as a unit.

The PL/SQL engine processes a procedure statement by statement, handling all procedural statements by itself and passing SQL statements to the SQL statement executor, as illustrated in Figure 14-2.