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

Charlie Edwards wrote:

> 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

Brilliant. Thanks for adding another tool to the toolbox.

And tell your DBA that I think they are .... well you know what I'm thinking.

Daniel Morgan Received on Fri Aug 02 2002 - 10:44:43 CDT