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

"Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message news:3D499E52.2EB478D6_at_noaa.gov...
> 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 One should avoid creating tables on the fly like hell 2 Generally speaking as you have globally temporary tables now, it is not necessary anymore
3 Tables have to exist at compile time when used in *static* sql 4 Consequently you have to replace your static sql by *dynamic* sql (which is an awful solution, as it will increase parsing) or try to avoid creating the table.
5 Your solution is awful too and most likely not going to work, as your code is validated when it starts running. The obj# of the current table is likely going to be different from the table you compiled with, I wouldn't be surprised if it wouldn't work
6 try to return from your path to hell.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address


Hth