In this Oracle tutorial, we will learn how to create a table using stored procedures in oracle 21c. Also, we will cover these topics:
- Oracle stored procedure create table
- Oracle stored procedure create table as select
- Oracle stored procedure create table if not exists
- Oracle stored procedure create table insufficient privileges
- Oracle stored procedure create a temporary table
- Oracle stored procedure to create tables dynamically
- Oracle stored procedure execute immediate create table
- Oracle stored procedure to drop and create table
- Oracle creates stored procedure truncate table
Oracle stored procedure create table
In this section, we will learn how to write a stored procedure to create a simple table in oracle database 21c.
A stored procedure is created so that repetition of activity can be avoided. you can create a stored procedure for repetitive activity and can call it every time you need to perform that activity.
In this case, we have to create a stored procedure that can create a table for use every time we execute it.
Use the create statement to create the stored procedure and also mention the replace keyword so that the existing stored procedure is modified.
Then declare a variable to store the query information then being the stored procedure. Inside the begin block, write the query to create a table and store it inside the variable.
Using Execute Immediate statement the table will be created as soon as the stored procedure will be compiled.
Example:-
In the below example, we have created a stored procedure with the name tbl and this will create a table with the name emp with one column as emp_id.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE tbl
AS
qry VARCHAR2(100);
BEGIN
qry := 'CREATE TABLE emp(emp_id VARCHAR2)';
EXECUTE IMMEDIATE tbl;
END tbl;
Also, check: Oracle Change Database Name
Oracle stored procedure create table as select
In oracle, As Select statement is used to create a table from another table within the same user. In this section, we will learn how to write a stored procedure that will create a new table using other tables.
Here is the list of a few things you can do while creating a table using As Select statement:
- Create a table with or without data
- Create a table with all or specific columns
Example:-
In the previous section, we have created a table emp with one column. Here, using that table have created a stored procedure that will use the emp table and create a new table with the name hr.
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE backup_tbl
AS
val VARCHAR2(100);
dataa VARCHAR2(100);
cursor crsr is
SELECT * FROM emp;
BEGIN
FOR item in crsr
LOOP
dataa := 'Create table hr as (SELECT * FROM emp)';
EXECUTE IMMEDIATE dataa;
END LOOP;
END;
/

Read: Database Size in Oracle 21c
Oracle stored procedure create table if not exists
Oracle does not support if not exists a keyword while creating a table like other database languages do but the same thing can be done by creating a stored procedure in oracle database 21c.
Example:-
In the below example, we have created a procedure for a bread company. In the procedure, the program checks if the table already exists in the dba_tables. If the table is not found then an exception is triggered wherein the new table is created with the name bread_man.
CREATE OR REPLACE PROCEDURE bread_comp
AS
bread_id int:=1;
BEGIN
SELECT COUNT(*) INTO bread_id
FROM dba_tables
WHERE table_name = 'BREAD_MAN';
EXCEPTION WHEN NO_DATA_FOUND then
EXECUTE IMMEDIATE
'CREATE TABLE bread_man(bread_id VARCHAR2(100),
bread_name VARCHAR2(100),
type_of_bread VARCHAR2(100),
cost_price FLOAT,
sale_price FLOAT');
END;
/

Read: Oracle how to copy a table
Oracle stored procedure create table insufficient privileges
In oracle, privileges play an important role and as a beginner, one should understand the concept of roles and privileges thoroughly. To fix this error grant the create table privileges to the owner.
Most of the time developers grant the role to the owner and then try to compile the stored procedure at that time they face this error – Insufficient privileges.
Read: Oracle Create User – Detailed Guide
Oracle stored procedure create a temporary table
Temporary tables in oracle are created for a short period of use only. While writing a program, most of the time we require a table to experiment upon. This table is required for a few uses only so you can create a stored procedure to create a table in no time.
Temporary tables are deleted on every commit and can be created using create global temporary table keyword.
Example:-
In this example, we have created a temporary employee table with the name emptesttable which has 3 columns.
CREATE OR REPLACE PROCEDURE testing
AS
BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE emptesttable'
||'('emp_id VARCHAR2(100), '
|| 'emp_name VARCHAR2(100),'
|| 'emp_sal FLOAT';
END testing;
Read: How to create table in Oracle
Oracle stored procedure to create tables dynamically
In oracle, dynamically means something that changes during run time. You can create a stored procedure to dynamically create a table by passing a parameter of the table name and fields you want to add.
Example:-
In the below example, we have created a stored procedure that will accept table_name as an input, and then it will create a table with the provided name in oracle 21c.
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE country (table_name IN VARCHAR2)
AS
dataa VARCHAR2(100);
BEGIN
dataa := 'CREATE TABLE table_name('||
'country_id NUMBER(100),'||
'country_name VARCHAR2(100)'||
'emp_sal FLOAT'||
')';
EXECUTE IMMEDIATE dataa;
END;
/

Read: Oracle Drop Database
Oracle stored procedure execute immediate create table
Execute immediate in oracle allows executing the query so that result can be produced. If the execute immediate is used inside the stored procedure then the query statement will be executed when the stored procedure is called.
It is optional to pass the execute immediate query while writing the stored procedure but most of the developers prefer to pass it with the query to create a table as this saves an extra step.
The table is created while compiling the stored procedure in oracle database 21c.
Example:-
In this example, we have created a stored procedure with the name newtbl and in this table execute immediate is used to create a table while compiling the program.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE newtbl
AS
qry VARCHAR2(1000);
BEGIN
qry := 'CREATE TABLE game('||
'game_id NUMBER(100),'||
'game_name VARCHAR2(100)'||
'rating FLOAT'||
')';
EXECUTE IMMEDIATE newtbl;
END newtbl;
Oracle stored procedure to drop and create table
Drop table statement in oracle is used to delete an existing table whereas create table is to create a new table in the database. Both of these statements are used in an application to make sure that if the table already exists then delete and recreate it with the same name.
Example:-
In the below example, we have created a stored procedure with the name create_table which will accept the table name as an argument. And then it will drop that table name and then recreate it
CREATE OR REPLACE PROCEDURE create_table(tbl_name IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '|| tbl_name;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
EXECUTE IMMEDIATE 'CREATE TABLE ' || tbl_name;
END create_table;
Read: Connect to oracle database
Oracle create stored procedure truncate table
In oracle, a truncate table statement removes all the data and materialized view associated with the table.
Example:-
In the below example, we have created a store procedure with the name tr_tbl which accepts the table name as an argument. The truncate function will be applied to the table given as an input.
CREATE OR REPLACE PROCEDURE tr_tbl(tbl_name IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'truncate table '|| tbl_name;
END tr_tbl;

Also, take a look at some more Oracle tutorials.
In this tutorial, we have learned how to create a table using stored procedures in oracle 21c. Also, we have covered these topics:
- Oracle stored procedure create table
- Oracle stored procedure create table as select
- Oracle stored procedure create table if not exists
- Oracle stored procedure create table insufficient privileges
- Oracle stored procedure create a temporary table
- Oracle stored procedure to create table dynamically
- Oracle stored procedure execute immediate create table
- Oracle stored procedure to drop and create table
- Oracle create stored procedure truncate table
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.