PL/SQL CREATE OR REPLACE PROCEDURE Using CREATE TABLE Statement

Question

I am engaged at the moment in some serious PL/SQL programming. The format for creating procedures is

[CREATE [OR REPLACE]]
   PROCEDURE procedure_name[(parameter[, parameter]...)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
   [PRAGMA AUTONOMOUS_TRANSACTION;]
   [local declarations]
BEGIN
   executable statements
[EXCEPTION
   exception handlers]
END [name];

and I place executable statements under BEGIN. Why am I not allowed to place a CREATE TABLE statement as an executable statement within a procedure?

Frank Schmitt · Accepted Answer · 2014-05-06 10:34:38Z

1

Because CREATE TABLE is a DDL statement, and you may not execute DDL statements from PL/SQL (at least not directly).

If (and that's a big if) you really need to do that, you can either use the DBMS_SQL package or EXECUTE IMMEDIATE (easier):

create or replace procedure do_it as 
begin
  execute immediate 'CREATE TABLE foo(pk number not null)';
end;

But this is usually not necessary. People coming from a SQL Server background often are trained to create lots of temporary tables from their stored procedures - in Oracle, you'd normally use a Global Temporary Table for that.

answered May 6, 2014 at 10:34

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mushy Over a year ago

Are you suggesting I should not make a stored procedure? If I wanted even needed to, I could do that and be correct yes?

Frank Schmitt Over a year ago

I suggest that if you ever feel the need to execute DDL statements from a stored procedure, you should question whether that is really necessary. There are valid use cases for this, but in my experience, they are quite rare. Stored procedures per se are fine - packages IMHO are even better.

Mushy Over a year ago

Would you like to provide a simple but elegant package example? I am using the PL/SQL Programming book to read on Packages but would like to see a good example.

Frank Schmitt Over a year ago

For example, I'd suggest you have a look at Tyler Muth's logger package (github.com/tmuth/Logger---A-PL-SQL-Logging-Utility ) - well written, and quite useful to integrate into your own appliations.

Collectives™ on Stack Overflow

PL/SQL CREATE OR REPLACE PROCEDURE Using CREATE TABLE Statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related