CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle10g
tundal45
tundal45

Reputation: 193

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;
  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

Upvotes: 0

Views: 4120

Answers (4)

Paul
Paul

Reputation: 1

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.

Upvotes: 0

redcayuga
redcayuga

Reputation: 1251

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.

Upvotes: 0

dpbradley
dpbradley

Reputation: 11915

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.

Upvotes: 1

HonkHonkHonk
HonkHonkHonk

Reputation: 71

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?

Upvotes: 1

Related Questions