create/drop tables in stored procedure? [message #369700] |
Thu, 17 August 2000 18:10 |
Nick
Messages: 64 Registered: February 2000
|
Member |
|
|
Can I create and drop tables within the context of a stored procedure? Or, am I forced to use the "type t is table of..." definition?
My problem is this: I have a script that can run in sqlplus which creates a temporary table. The table's column names are taken from one query, and its records are built from several successive queries on several tables. The table I create is like a view, but with varying column names and number of columns, which is why (I think) I can't create a view to do this. I need a way to pass in a parameter -each time- I create this table that tells the script exactly where to find the column names, and since I don't think this is possible with a script, I am left with trying to make procedures/functions that do the same thing as the script. I also don't think that I can use the type definition method because the type would have to be a table of records, and the records have an arbitrary number of columns, meaning I would have to build the type definition statement on the fly, something I've not seen done anywhere. To complicate the situation even further, I would like to dynamically name the table or type based on some parameter that is passed in.
Alternatively, is there a way to call the script (with parameters) from pl/sql? This sounds far-fetched to me, but who knows.
That was a lot of info for one post. Thanks for any ideas related to any part of it. nick
|
|
|
Re: create/drop tables in stored procedure? [message #369702 is a reply to message #369700] |
Fri, 18 August 2000 08:08 |
Rob Rebelo
Messages: 2 Registered: August 2000
|
Junior Member |
|
|
I don't completely understand your question, but I think this may be a solution.
You cannot use DDL statements (ie. create/drop) in a pl/sql stored procedure, but there is a way around this. Use the dbms_sql package to dynamically create DDL statements in your stored procedure, then execute the DDL statement using the dbms_sql package.
As for your last question, if anyone lets you know how to execute a sql script from inside a stored procedure, let me know! I've been trying to find how to do this.
|
|
|