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
EXECUTE IMMEDIATE ‘create table my_table_test (n number)’;
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).
‘create table my_table (n number)’);
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.