Stored Procedure - CREATE TABLE problem

[seralex]

Hello Friends!

I've coded a simple stored procedure that creates a table, but I cannot compile it.
Is it because Oracle doesn't let to create tables in stored procedures? I have all
permisions , so security is not the problem. Is there a set of dissallowed statements
in Oracle stored procedures?

Here's the procedure:

CREATE OR REPLACE PROCEDURE proc1 AS

BEGIN
CREATE TABLE schema.tab1 (
field1 NUMBER;
);
END:

Errors:

Line # = 4 Column # = 1 Error Text = PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
The symbol "lock was inserted before "CREATE" to continue.

Line # = 4 Column # = 27 Error Text = PLS-00103: Encountered the symbol "(" when expecting one of the following:

. , @ in <an identifier>
<a double-quoted delimited-identifier> partition subpartition


I will greatly appreciate all replies.

[jmodic]

You can't directly use DDL (Data Definition Language) statements in PL/SQL. You have to use "dynamic SQL".

If you are using 8i then you can use "native dynamic SQL", which makes things extremly simple:

EXECUTE IMMEDIATE 'CREATE TABLE foo (c1 NUMBER)’;

If you are using 8.0 or 7.3 than it gets a *little* more complicated. Look for package DBMS_SQL in your documentation, it is the package that enables you to use dynamic SQL in your PL/SQL procedures.

[seralex]

Hi JModic,
Thank you, thank you very much.
Could you please tell me how to return a recordset from a stored procedure in Oracle8i - I've used MSSQL server, and it was easy - just type "select..." in the end. As I understand Oracle lets you return a resultset only through OUT CURSOR parameters. Am I right there ?

Thank you, Seralex