To unfold a bit on the other answers how to create table in pl SQL procedure. Yes, you must use dynamic SQL to produce a table or perform any other kind of DDL (data description language) operation in PL/ SQL.
There are two commonly used methods to execute DDL from PL/ SQL
First, “ roll your own” with EXECUTE IMMEDIATE.
Script create table in PL SQL Procedure
BEGIN
EXECUTE IMMEDIATE ‘create table my_table_test (n number)’;
END;
Second method, use DBMS_UTLIITY.EXEC_DDL_STATEMENT:
Eventually, be veritably cautious about executing DDL inside PL/ SQL programs in the product. You have to check for SQL injection, ripple goods of DDL changes ( changing a column in a table could abate numerous program units).
BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT (
‘create table my_table (n number)’);
END;
Also, remember that whenever you parse (and also execute) a DDL statement, an implicit commit is issued.
So you should presumably put any dynamic DDL inside a stored procedure and make it an independent sale.
Then it is a somewhat lingo-in-impertinence illustration (I say that because you nearly clearly don’t want to make it really easy to drop whole lots of objects).
CREATE OR REPLACE PROCEDURE drop_whatever (
nm IN VARCHAR2 DEFAULT '%'
, typ IN VARCHAR2 DEFAULT '%'
, just_checking IN BOOLEAN DEFAULT TRUE
)
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
dropstr VARCHAR2 (32767);
CURSOR object_cur
IS
SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE UPPER (nm)
AND object_type LIKE UPPER (typ)
AND object_name <> 'DROP_WHATEVER';
BEGIN
FOR rec IN object_cur
LOOP
dropstr :=
'DROP '
|| rec.object_type
|| ' '
|| rec.object_name
|| CASE
WHEN rec.object_type IN ('TABLE', 'OBJECT')
THEN ' CASCADE CONSTRAINTS'
ELSE NULL
END;
BEGIN
IF just_checking
THEN
DBMS_OUTPUT.put_line (dropstr || ' - just checking!');
ELSE
EXECUTE IMMEDIATE dropstr;
DBMS_OUTPUT.put_line (dropstr || ' - SUCCESSFUL!');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (dropstr || ' - FAILURE!');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
END LOOP;
END;
How to create a table inside of a procedure in Oracle?
If you want to create a table inside of a procedure. You can put the create query in a string then execute immediately the string.
Here is an example:
create or replace procedure st.sample is
var1 varchar2(4000);
BEGIN
var1:='create table st.sample2(
id number)';
EXECUTE IMMEDIATE var1;
end sample;
Hope this article is useful to you. Let me know your thoughts.
In case you would like to know more about SQL or PLSQL programming languages make sure to check the video tutorials on this website.
These tutorials are useful for self-learning of SQL and PLSQL and a few other technologies.
In case any queries are associated with the course you may submit your query under the contact us section of this website.