Oracle stored procedure create table

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.

• Oracle get database name
• Oracle Add Row Number
• Oracle to_char Date Format
• Oracle Add Foreign Key

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