Create table if not exist - not working - ORA-00922: missing or invalid option-CodePudding

Running Spring Boot App with Liquibase changeset

below is my master xml changeset:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
 
    <include file="db/changelog/db.changelog-quartz.sql"/> 

</databaseChangeLog>

and below is my db/changelog/db.changelog-quartz.sql file.

--liquibase formatted sql

--changeset quartz:quartz-init
--------------------------------------------------------
--  DDL for Table QRTZ_LOCKS
--------------------------------------------------------
CREATE TABLE if not exists QRTZ_LOCKS ("SCHED_NAME" VARCHAR2(120), "LOCK_NAME" VARCHAR2(40));

Insert IGNORE into QRTZ_LOCKS (SCHED_NAME,LOCK_NAME) values ('quartzScheduler','STATE_ACCESS');

Not sure why these syntax are wrong for postgresql.

when I run the Spring Boot App then I am getting below error

Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-quartz.sql::quartz-init::quartz:
     Reason: liquibase.exception.DatabaseException: ORA-00922: missing or invalid option
 [Failed SQL: (922) CREATE TABLE IF NOT EXISTS QRTZ_LOCKS ("SCHED_NAME" VARCHAR2(120), "LOCK_NAME" VARCHAR2(40))]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:672)

Extra information:

version of jdbc : com.oracle.database.jdbc:ojdbc8: 19.11.0.0

can some one please help on this ?

CodePudding user response：

Both of your statements are syntactically invalid for Oracle:

CREATE TABLE if not exists QRTZ_LOCKS ("SCHED_NAME" VARCHAR2(120), "LOCK_NAME" VARCHAR2(40));

Oracle doesn't have a CREATE TABLE IF NOT EXISTS statement. Convert this to a simple

CREATE TABLE QRTZ_LOCKS ("SCHED_NAME" VARCHAR2(120), "LOCK_NAME" VARCHAR2(40));

If you need to ensure your migration works even if the table is already present, you can wrap it in a PL/SQL block and only execute the CREATE statement if the table doesn't exist yet:

declare
  l_cnt pls_integer;
begin
  select count(*) into l_cnt from user_tables where table_name = 'QRTZ_LOCKS';
  if l_cnt = 0 then
    execute immediate 'CREATE TABLE QRTZ_LOCKS ("SCHED_NAME" VARCHAR2(120), "LOCK_NAME" VARCHAR2(40))';
  end if;
end;

Regarding

INSERT IGNORE INTO QRTZ_LOCKS (SCHED_NAME,LOCK_NAME) 
  VALUES ('quartzScheduler','STATE_ACCESS');

Oracle doesn't have INSERT IGNORE. If you want to insert only new values (not sure what INSERT IGNORE does, sorry), you can either catch the error on duplicate insert or use a MERGE statement (sometimes called UPSERT in other RDBMSs):

MERGE INTO QRTZ_LOCKS target 
USING (
  SELECT 'quartzScheduler' AS sched_name,
         'STATE_ACCESS' AS lock_name
  FROM dual
) src
ON (src.sched_name = target.sched_name)
WHEN NOT MATCHED THEN
INSERT(sched_name, lock_name) VALUES(src.sched_name, src.lock_name);

I'm assuming sched_name is your primary key - if that's not the case, modify the ONclause accordingly.

CodePudding user response：

Oracle Database doesn’t include the IF NOT EXISTS clause with its CREATE TABLE statement, like some other DBMSs do.

Therefore, if we don’t want to produce an error due to the table name already being used, we need to use other methods to check for the existence of the table.

Option 1: Check the DBA_TABLES View DBA_TABLES is a data dictionary view that describes all relational tables in the database. Its columns are the same as those in ALL_TABLES.

We can check this table to see if the table already exists, then only run the CREATE TABLE statement if it doesn’t already exist.


Example:

DECLARE
tbl_count number;
sql_stmt long;

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM dba_tables
    WHERE owner = 'HR'
    AND table_name = 'T1';

    IF(tbl_count <= 0)
        THEN
        sql_stmt:='
        CREATE TABLE T1 (
            c1 number(6,0),
            c2 varchar2(10)
        )';
        EXECUTE IMMEDIATE sql_stmt;
    END IF;
END;

Since creating a table is a one time task, why even bother with this method,