Oracle create table based on multiple conditions


I'm trying to figure out the best approach to creating an oracle table If a certain condition is true.

for example.
Create table1 if name <> ' ' and address <> ' '
Create table1 if name <> ' ' and address <> ' ' and phone = ' '

Should a case statement be used in this scenario?
Who is Participating?
schwertnerConnect With a Mentor Commented:
Dynamic SQL could be an answer:

v_sql   VARCHAR2(900);

IF  name <> ' ' and address <> ' '  THEN v_sql := '  Create table1 (col1 VARCHAR2(80), .....)';
ELSIF  name = ' some' and address = ' another' and phone = '906377 ' THEN v_sql := '  Create table2 (col1 NUMBER,.....)';

execute immediate v_sql;



you can't do conditional creates within a single statement.
you'll have to check your conditions first then issue whatever create is appropriate.

what is the intent here?  It's not standard (and certainly not recommended) practice to create tables on the fly in Oracle.

Are you trying to create "temp" tables to work with?  If so, doing it dynamically is the wrong approach
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Are you looking something like this?

Set Serveroutput on
set echo off
spool &1
  if name <> '' and address <> '' then
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE(col1 varchar2(2));');
  end if;

  if name <> '' and address <> '' then
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE (col1 varchar2(2), col2 varchar(2)); ');
  end if;
spool off;

sqlplus -s username/password @File_1.sql my_spool.sql
sqlplus -s username/password @my_spool.sql
phoen08Author Commented:
I'm trying to convert an old process that would export output to a text file to a process that would store the data in oracle tables. One of the sections in the file process outputs columns based on whatever condition is presents.  After further review, I believe this step in the process is only changing the column values based on the condition.  So, I may just need multiple case statements to change the column data based on the condition.  There would only be one table created from all of the condition statements.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.