Re: Must tables exist at PL/SQL compile time?

Daniel and Sybrand -

Thanks very much for the responses. I took Sybrand's suggestion and placed my reference to the troublesome table in some dynamic SQL and all is well now. I knew that there was some easy workaround to the requirement that tables exist at compile time!

For what it's worth, I'm not creating a table dynamically inside of a procedure. My Pro*C program communicates with my PL/SQL using Oracle pipes and invokes a third-party
program that creates this table from an existing table. This third-party program, unfortunately, is using an API that requires the creation of an additional table. I don't have influence over that program at all.

My program is working well now in spite of its descent into the programmatic equivalent of Hades. It's actually quite efficient and easy to understand, considering the restrictions that I have.

Thanks for your help.

Tom

Daniel Morgan wrote:

> Thomas Gaines wrote:
>
> > Good day -
> >
> > My PL/SQL skills are a tad rusty these days, and I so I have to
> > ask this seemingly silly question.
> >
> > I'm attempting to compile a package, and the only error I receive speaks
> > of a
> > non-existent table (at compilation time, but not execution time)
> > referenced
> > in the code. My application flow will create the aforementioned
> > table via a Pro*C program, and then the PL/SQL program will access
> > it.
> >
> > Are you aware of any technique that I can use at PL/SQL compile time
> > to avoid the dreaded "PLS-00201: identifier 'xxx.xxx' must be declared"
> > error? I suppose that I could create a dummy xxx.xxx table, compile
> > the PL/SQL and then remove the dummy table, but I'd rather avoid
> > that if possible.
> >
> > Thanks very much,
> > Tom
>
> 1. The tables must exist at compile time.
> 2. There is absolutely no excuse for creating tables on the fly in a
> procedure.
> 3. You might choose to use a global temporary table but even that is likely
> to be unnecessary.
>
> Tell us what you are actually trying to accomplish so we can help you with
> how to do it successfully from the standpoint of performance and
> scalability.
>
> My instinct here is that you are trying to do TSQL in PL/SQL.
>
> Daniel Morgan
Received on Thu Aug 01 2002 - 16:56:35 CDT