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

Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<3D49AE92.74D5008_at_noaa.gov>...
> 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
>

OK, I haven't tried this, but it's a _really_ bad cowboy workround:

Create your table (a copy of the dynamically created one) in a seperate schema and grant access on this table to the schema with the dynamically created table and code.

Where your code is, create a synonym to this table.

So now your code compiles.

Now you run this dodgy 3rd party code, which creates the table, so now your code will use the table in precedence over the synonym.

I'm sure some of the "fancy-dan" DBAs out there will be able to tell you some reason why you can't do this, but I don't care - I'm off on my summer holidays today.

By the way, I can't try it out, as my DBA won't let me have the CREATE SYNONYM privilege.

CE Received on Fri Aug 02 2002 - 04:44:31 CDT