Re: Issue with creating tables

andrewst_at_onetel.com wrote:
> laszlo_spamhole_at_freemail.hu wrote:
> > Hi. I'm relatively new to plSQL, and I have a bit of a problem. I'd
> > like to make a package with one procedure creating some tables, and
> > another procedure SELECTing from them.
> >
> > Is this even possible? My problem is that (as far as I know), plSQL
> > only allows creating tables through EXECUTE IMMEDIATE. And if the
> table
> > creator procedure uses EXECUTE IMMEDIATE to create the tables, the
> > other procedures can't see that the tables exist, and the whole
thing
> > refuses to compile, giving a "Table or view does not exist" error.
> >
> > Any help with this one?
>
> What is it you are trying to achieve, really? In all probability,
> creating tables via EXECUTE IMMEDIATE is not the correct solution.

Well, I have a graph, defined on two tables (one holds the graph nodes, the other holds the edges). I'm writing a fairly complex search algorithm. The two tables I have to work with are pretty horrible from a heuristics POV, so I want to make new tables into which I can pull the data and organize it better, search-wise.

Basically, I have a procedure that creates the search tables and fills them with data, and a bunch of procedures that work with those tables.

> Mayeb you want a GLOBAL TEMPORARY table?

Since creating the search tables takes a fair amount of time and the graph is fairly non-volatile, I don't want to have to re-generate the search tables every session.

I actually (kind of) solved the problem by generating dummy search tables manually... this allowed my package to compile, and the creator procedure then did what it was supposed to (find the table, drop it, and re-create it with all the necessary data). But while this _does_ work (as long as nobody drops my tables), it's not exactly the most elegant solution.

Laszlo Received on Tue May 24 2005 - 07:39:54 CDT