Create table from another table in Oracle Procedure

Question

I have a SQL Server stored procedure wherein I am creating a table using another table.

Select Distinct 
 col1
,col2
,col3
,col4
into table_2 
from table_1;

Above statement will create a new table as ' table_2' with the selected list of columns.

I tried to convert the above query into Oracle syntax:

Create table table_2 as 
select
 col1
,col2
,col3
,col4
from table_1;

This is working fine as expected when I am running it in the editor. However, the same statement is giving me errors when I am trying to use this in Oracle Procedure.

I have gone through some links or so, which says that need to create some temp variables or storage or loop in to perform the same task.

I am getting very confused now and don't know how to convert this simple statement into valid Oracle syntax.

Answer 1

If you want to create a table (or do any other DDL) in a stored procedure, you'd need to use dynamic SQL

EXECUTE IMMEDIATE 'create table table_2 as select col1, col2, col3, col4 from table_1';

If you create the table dynamically, though, you'd also need to use dynamic SQL every time you wanted to subsequently query the table. That generally makes your code much harder to build and test. And you won't be able to have two sessions running the procedure at the same time since their create table statements would conflict with each other.

Taking a step back, in SQL Server, it is very common to create local temporary tables inside procedures. It is very, very rare to do that in Oracle. It rarely makes sense to try to port logic from SQL Server to Oracle (or vice versa) directly like this. If you do, you'll find that Oracle is lousy at running SQL Server code (and SQL Server is lousy at running Oracle code). You'd generally want to take the logic from SQL Server and figure out the best way to implement that logic in Oracle.

Oracle does have global temporary tables where the table definition is global but the data is local to your session (or transaction). Potentially, you want to do

create temporary table table_2 (
  col1 number, 
  col2 number,
  col3 varchar2(10),
  col4 date
);

and then insert into that table in your stored procedure rather than trying to create the table dynamically.