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