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

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;
  E开发者_StackOverflowND 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

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?

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.

All referenced objects, including tables and views, must exists when the package is compiled. If the table is altered or dropped the package will become invalid and will have to be recompiled. So create the table in the ashish schema first.

This is even true for "AUTHID CURRENT_USER" packages. It's a funny situation because when a procedure in the package is run the procedure will look for the table in the invoker's schema, not the package owner schema so the table may not exist in that schema and Oracle will raise a runtime exception. But the owner of the package can compile it because the table exists in its schema. It's funny but that's the way it is.

You can code your sql into a string (dynamic) and then pass it to an execute statement. I'm sure this way, oracle won't know what you're going to run before hand.