CODEWITHSUNDEEP
oracle-database
HLee
HLee

Reputation: 1

If statement inside create table query -Oracle-

I would like to create table which asks user input first. Then based on the input, it select which columns are added.

for example, if the response is 'N', then table is created including columns col1, col2, col3. If the response is 'Y', table is created including columns col1, col2, col3, col4, col5.

Is this possible? If yes, please provide me simple and primitive query so that I can apply it to my case.

Thanks,

Upvotes: 0

Views: 789

Answers (2)

Thomas
Thomas

Reputation: 366

CREATE OR REPLACE FUNCTION tmp_custom_DDL( p_input  VARCHAR2 IN, p_resp CHAR IN OUT)  RETURN CHAR
AS

v_str    VARCHAR2(4000);

IF p_resp = 'Y' THEN 

v_str := 'col1 varchar2(10), col2 varchar2(10), col3 varchar2(10)';

ELSE v_str := 'col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col4 varchar2(10) ' ; 

EXECUTE IMMEDIATE v_comm_1  || v_str  || v_comm2;
--v_comm_1  is the first half of create table command till the specified cols
--v_comm_2 is the rest of the create table command


RETURN p_resp;

END;

this is only a quick draft, fix the few lexical bug and the missing definitions :) (this is the first step)

Upvotes: 0

Husqvik
Husqvik

Reputation: 5809

Using SQL*Plus it's simple:

ACCEPT table_option -
PROMPT 'Create more columns? '

SET TERM OFF
COLUMN extra_columns NEW_VALUE extra_columns
SELECT
    CASE '&table_option'
        WHEN 'Y' THEN ', C4 NUMBER, C5 VARCHAR2(255), C6 DATE'
    END extra_columns FROM DUAL;

CREATE TABLE tmp (
    C1 NUMBER,
    C2 VARCHAR2(255),
    C3 DATE &extra_columns
);

SET TERM ON

You can store the script as a file and invoke it from SQL*Plus using @filename.

Upvotes: 0

Related Questions