How to create table in PL SQL Procedure(Updated)

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.

create table in PL SQL Procedure

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.

You May Also Like

Leave a comment