Re: Can you use a PL/SQL procedure to create a table?

Yes this is possible but you will need to use the dbms package. Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle schema objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

However, this design imposes some limitations. For example, the p-code includes references to schema objects such as tables and stored procedures. The PL/SQL compiler can resolve such references only if the schema objects are known at compile time. In the following example, the compiler cannot process the procedure because the table is undefined until the procedure is executed at run time:

However, the package DBMS_SQL, which is supplied with Oracle, allows PL/SQL to execute SQL data definition and data manipulation statements dynamically at run time. For example, when called, the following stored procedure drops a specified database table:

Checkout the dbms_sql package in the pl/sql doc. and the application dev. guide.

hope this helps...

Santanu Lahiri wrote:

> Subject line pretty much says it all...
>
> I need to be able to create several different types of tables on demand from
> within an application. Instead of using a CREATE TABLE sql command, followed
> by a series of CREATE INDEX commands etc, I would like to have the code
> encapsulated within a stored proc, which can then be called as needed.
>
> I can not find anything in the docs I have as to how or even if it can be
> done.
>
> Any suggestions?
>
> Thanks.
>
> Santanu
Received on Sun Mar 21 1999 - 00:38:41 CST