In Oracle PL/SQL, you can create a table within a PL/SQL procedure using the EXECUTE IMMEDIATE
statement to execute a dynamic SQL statement that contains the CREATE TABLE
statement. Here's a step-by-step guide on how to create a table in a PL/SQL procedure in Oracle:
Create PL/SQL Procedure to Create a table
First, you need to create a PL/SQL procedure that will contain the dynamic SQL statement to create the table. Here's an example of a simple procedure:
CREATE OR REPLACE PROCEDURE createTableProcedure AS BEGIN -- Dynamic SQL statement to create a table EXECUTE IMMEDIATE ' CREATE TABLE my_new_table ( id NUMBER, name VARCHAR2(100) )'; DBMS_OUTPUT.PUT_LINE('Table created successfully.'); END; /
In the above example, we define a procedure named createTableProcedure
that uses EXECUTE IMMEDIATE
to create a table named my_new_table
with two columns: id
and name
.
Execute the PL/SQL Procedure
To create the table, you can simply execute the PL/SQL procedure you defined above:
BEGIN createTableProcedure; END; /
When you run the above anonymous PL/SQL block, it will execute the createTableProcedure
, which, in turn, will create the specified table.
Verify the Table Created Through the PL/SQL Procedure
After executing the procedure, you can verify that the table has been created successfully by querying the user_tables
data dictionary view:
SELECT table_name FROM user_tables WHERE table_name = 'MY_NEW_TABLE';
This query will return the name of the newly created table, which should be 'MY_NEW_TABLE' in this example.