Home » RDBMS Server » Server Administration » create/drop tables in stored procedure?
create/drop tables in stored procedure? [message #369700] Thu, 17 August 2000 18:10 Go to next message
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 Go to previous message
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.
Previous Topic: Oracle Message ORA-01488
Next Topic: Multiple Oracles on NT
Goto Forum:
  


Current Time: Fri Oct 30 03:33:47 CDT 2020