How to create table at runtime in oracle sql plsql?

-

 How to create table at runtime in oracle SQL plsql?

Under this concept, creating the table at runtime or dynamically with the help of EXECUTE IMMEDIATE in the plsql block. EXECUTE IMMEDIATE statement is used to execute the dynamically constructed SQL statements. Dynamic SQL Create table statement i.e. DDL statements cannot be directly executed in the PLSQL block without the use of dynamic SQL. Dynamic SQL is slower than static SQL, so use it when it is necessary. If you want temporary table, you need to include GLOBAL temporary in the CREATE TABLE statement. Static SQL in the PLSQL will not allow you to use create table inside a block, so dynamic SQL is required.

Steps:

  • Construct the create table statement: Build the SQL statement with the string. It includes the table name, column, datatypes and constraints.
  • Use of EXECUTE IMMEDIATE:  EXECUTE IMMEDIATE statement is used to execute the dynamically constructed SQL statements.
  • Handle exceptions: It include the error handling to manage and handle the issues like invalid table names or permissions. It handles the failed cases like table creation and table already exists in the system. An error occurred while creating table: ORA-00955: name is already used by an existing object
  • Optional Validate inputs: It ensures that the table name and column definitions are valid to avoid or problem of the SQL injections or errors. It allows the flexibility for table name, columns and other attributes are passed dynamically.DBMS_ASSERT.SQL_OBJECT_NAME is used to validate the table name and prevent SQL injection.
BEGIN
-- Define the dynamic SQL statement
EXECUTE IMMEDIATE '
CREATE TABLE dynamic_table1
(
id number primary key,
name varchar2(20),
created_date DATE DEFAULT sysdate
)';
DBMS_OUTPUT.PUT_LINE('Table has been created successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred while creating table:'|| SQLERRM);
END;
/


select * from dynamic_table1;


Explanation:

The above PLSQL block which dynamically creates the table which is having name as dynamic_table1 with the three columns like ID,name and created_date.It will catch any errors during the exception and outputs the error message using SQLERRM.The user executing the above block must have the CREATE TABLE privilege in the schema.

EXECUTE IMMEDIATE: It will execute the sql string dynamically.
WHEN OTHERS THEN: It catches any exception if the table already exists.


BEGIN
EXECUTE IMMEDIATE '
DECLARE
v_count NUMBER;
BEGIN
select count(*) into v_count
FROM user_tables
where table_name=''DYNAMIC_TABLE4";
if v_count=0 THEN
EXECUTE IMMEDIATE ''
Create table dynamic_table4
(name varchar2(20),age int,created_date date default sysdate
)";
DBMS_OUTPUT.put_line("Table which has been successfully created.");
ELSE
DBMS_OUTPUT.PUT_LINE("Table already exists.");
END IF;
END;';
EXCEPTION 
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occurred:'||SQLERRM);
END;
/


select * from employees_temp;

desc employees_temp;


Table created with the help of parameters:

DECLARE
v_sql varchar2(1000);
v_table_name varchar2(40):='temp_dynamic_table';
BEGIN
v_sql := 'CREATE TABLE ' || v_table_name || ' (
name varchar2(10),
age number
)';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dynamic table '|| v_table_name || ' created.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Table will first get drop and recreate using below lines of code:
BEGIN
   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE emp_runtime';
   EXCEPTION
      WHEN OTHERS THEN
         IF SQLCODE != -942 THEN
         -- ORA-00942: table or view does not exist.
            RAISE;
         END IF;
   END;

   EXECUTE IMMEDIATE '
      CREATE TABLE emp_runtime (
         empid     NUMBER,
         empname   VARCHAR2(20),
         hiredate  DATE
      )
      ';
END;

Table emp_runtime has been created with the column names as empid,empname & hiredate.
/


Conclusion:

This concept will not work in that an environment where the DDL statements are restricted. Parameterize is done to create the different tables dynamically.



Comments

Post a Comment

Popular posts from this blog

Difference between union and union all in oracle sql plsql

-
Image
"This is one of the most important questions that interviewer might be asking in an interview.Infyosis,Linked group,Kyndryl,Vodafone Idea, IBM and Mphasis etc." INTRODUCTION: It is said to be set operators which is used to combine, add or concatenate two or more result sets. It is used in the multiple select statements where to combine to produce the final desired results. In other words, multiple select statements of similar column name in same order and having same data type to produce the unified result. It merges the content of the two structurally compatible organized tables into single table.  In MIS datalake,when you are adding the modules of GST reconciliation which is to be used in micros in Excel or VB script, union and union all set operator is preferred for eliminating duplicate records or keeping duplicate records as per business requirement. There are several alternatives like bulk collect,join,set operators like intersect & minus for combining the data from...
Read more

Explain oracle pl sql datatypes interview question and answer for fresher and experienced.

-
Image
 Oracle PLSQL Data Types : PLSQL constant,value,variable,parameter,function which return the value are having the data type. It will determine the storage space information occupied and occurred in the memory or storage format and range constraints for valid values and operations as per specified size limit. It will determine how the data will be going to stored, handled and treated by the oracle engine during the period of processing and storage. It is used in the PLSQL blocks. It allows you to define your sub-types. PLSQL codes are embedded into the java program. Subtypes are used to make the data types compatible with data types used in the PLSQL program while embedding the PLSQL code into the java program. Subtype will be compatible with ANSI/ISO & IBM. It provides the pre-defined data types. Pre-defined data types are categorized into the four types: Composite LOB Reference Scalar Scalar Datatypes: It stores the values without the internal components. It holds the single ...
Read more

Difference between full join and cross join-oracle sql plsql

-
Image
 Difference between full join and cross join in sql:- They behave differently to combine the rows from the two tables. FULL OUTER JOIN: It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows. Syntax: select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id; select e.ename,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno; When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL. CROSS JOIN: It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows. Syntax: select c.*.d.* from tableC c CROSS JOIN tableD d; select e.ename,d.dname from emp e CROSS JOIN dept d; ENAME ...
Read more