Skip to content
Advertisement

problem with script creating table via if clause

We are trying to build a script which automatically checks if a table is already created and if so the script should end. If the table is not created the script should create it.

    case 
    if select count(*) from ALL_TAB_COLS where table_name = 'GPS_TRACKER_DATA' = 0 then
       create table AZUBI.GPS_TRACKER_DATA as select * from KAKATEL.GPS_POSITION
    else end

this is the error we are getting

    Error starting at line : 3 in command -
    create table FEZ.GPS_TRACKER_DATA as select * from KAKATEL.GPS_POSITION
    else END
    Error report -
    ORA-00933: SQL command not properly ended
    00933. 00000 -  "SQL command not properly ended"
    *Cause:    
    *Action:

Advertisement

Answer

This must be done in PL/SQL block:

DECLARE
    V_CNT   NUMBER := 0;
BEGIN
    SELECT
        COUNT(*)
    INTO V_CNT
    FROM
        ALL_TABLES -- USE ALL_TABLES 
    WHERE
        TABLE_NAME = 'GPS_TRACKER_DATA'
        AND OWNER = 'AZUBI'; -- ADDED OWNER CONDITION

    IF V_CNT = 0 THEN
        EXECUTE IMMEDIATE 'create table AZUBI.GPS_TRACKER_DATA as 
                           select * from KAKATEL.GPS_POSITION';
    END IF;
END;
/

Cheers!!

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement