Regarding Oracle Stored Procedure not compiling because table does not currently exist

Question

All,

I have the following Package Description:

CREATE OR REPLACE PACKAGE ashish.PKG_Customer AUTHID CURRENT_USER AS
  TYPE cursorType IS REF CURSOR;

  PROCEDURE CreateCustomerTable;
  PROCEDURE SelectCustomers(o_ResultSet OUT cursorType);    
END PKG_Customer;

and here is the package body:

CREATE OR REPLACE PACKAGE BODY ashish.PKG_Customer AS

  PROCEDURE CreateCustomerTable AS
    sQuery VARCHAR2(1000);
  BEGIN
    sQuery := 'CREATE TABLE tblCustomer2(
               CustomerID INTEGER PRIMARY KEY,
               FirstName VARCHAR2(50),
               LastName VARCHAR2(50),
               City VARCHAR2(200), 
               State_Province VARCHAR2(100),
               PostalCode VARCHAR2(25)
              )';
    EXECUTE IMMEDIATE sQuery;
  END CreateCustomerTable;

  PROCEDURE SelectCustomers(o_ResultSet OUT cursorType) AS
  BEGIN
    OPEN o_ResultSet FOR
      SELECT CustomerID,
             FirstName,
             LastName,
             City,
             State_Province,
             PostalCode
        FROM tblCustomer;
  END SelectCustomers;
END PKG_Customer;

The issue I am facing is that my package will not compile because the table does not currently exist. Surely I should be able to create stored procedures in advance for tables that currently don't exist in Oracle right? Am I doing something wrong here?

The server version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit.

Thanks!
-Ashish

Answer 1

Not that I'm aware of. How can you compile something against objects that don't exist? Oracle doesn't know if you've mistyped the table name trying to reference an existing table or are hoping to create the table at a later time.

Why not create your tables first then create/compile your packages?

Answer 2

You can create the package in advance (meaning it will exist as an object in the database), but it will be marked invalid by Oracle. Oracle will attempt to recompile the object the first time it is referenced so if your tables exist at that time it will be OK.

However, you can run into problems when the dependencies are more than one level deep - Oracle will not reach down into the dependency chain to recompile all necessary invalid objects, and discarding the state of a package through recompilation can can cause a problem if the previous state was in use by another package.