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!!