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?

Thanks,

Laszlo

laszlo_spamhole (AT) freemail (DOT) hu wrote:
What is it you are trying to achieve, really? In all probability,
creating tables via EXECUTE IMMEDIATE is not the correct solution.
Mayeb you want a GLOBAL TEMPORARY table?

You can SELECT FROM the tables created on the fly with another EXECUTE
IMMEDIATE 'SELECT...'. (They must exist when the select is executed).

hth.

Carlos.

andrewst (AT) onetel (DOT) com wrote:
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.

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

Carlos wrote:
Thanks... I figured this would work, but since I need to do a lot of
stuff with the selected data (and don't really feel like funneling my
entire code through EXECUTE IMMEDIATE), this probably isn't the best
solution.

Laszlo

laszlo_spamh... (AT) freemail (DOT) hu wrote:
So why do you need to drop and re-create the tables each time? Do you
generate a different set of columns each time?

andre... (AT) onetel (DOT) com wrote:
No, the columns are always the same, but occasionally (pretty rarely),
the entire set of data changes. The table isn't dropped and recreated
each time, just occasionally.

Hmm... actually, I think I see your point. I'm not sure why I'm
dropping the table instead of doing a DELETE FROM, when the data needs
to be recreated from scratch.

Thanks for the speedy assistance! I appreciate it

Laszlo

andre... (AT) onetel (DOT) com wrote:
No, the columns are always the same, but occasionally (pretty rarely),
the entire set of data changes. The table isn't dropped and recreated
each time, just occasionally.

Hmm... actually, I think I see your point. I'm not sure why I'm
dropping the table instead of doing a DELETE FROM, when the data needs
to be recreated from scratch.

Thanks for the speedy assistance! I appreciate it

Laszlo

laszlo_spamhole (AT) freemail (DOT) hu wrote:
What you want can be done. It is equally true that it should NEVER
be done.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

laszlo_spamhole (AT) freemail (DOT) hu wrote:

Apart from comments pointing towards "you should not be doing this":
a truncate is much faster than a delete from .. However: it is DDL,
not DML
--
Regards,
Frank van Bortel