0

I've got a spring project with the following application.yaml:

spring:
  application:
    name: phonebook
  sql:
    init:
      mode: always

  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
        format_sql: true
      show-sql: true

  # Datasource Configuration
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/freepdb1
    username: username
    password: topsecret
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSource
    oracleucp:
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource
      sql-for-validate-connection: select * from dual
      connection-pool-name: phoneBookConnectionPool
      initial-pool-size: 15
      min-pool-size: 10
      max-pool-size: 30
      fast-connection-failover-enabled: false

To initialize the database I've created a schema.sql file with the following content:

DROP TABLE PHONEBOOK PURGE;

CREATE TABLE PHONEBOOK (
    "PHONEBOOK_ID" NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1 CACHE 20 ) NOT NULL ENABLE,
    "FIRST_NAME"   VARCHAR2(40) NOT NULL,
    "LAST_NAME"    VARCHAR2(40) NOT NULL,
    "PHONE_NUMBER" VARCHAR2(12) NOT NULL,
    "CREATE_DATE"  TIMESTAMP DEFAULT SYSDATE,
    PRIMARY KEY ("PHONEBOOK_ID")
);

And a data.sql file with this content:

INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('James', 'Butt', '504-621-8927');
INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('Josephine', 'Darakjy', '810-292-9388');
INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('Art', 'Venere', '856-636-8749');
INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('Lenna', 'Paprocki', '907-385-4412');
INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('Donette', 'Foller', '513-570-1893');
INSERT INTO PHONEBOOK (FIRST_NAME, LAST_NAME, PHONE_NUMBER) VALUES ('Simona', 'Morasca', '419-503-2484');

schema.sql and data.sql lives under the resources directory in my project. However when I run the application it stops with the following error:

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist

Which makes total sense as the table doesn't exist. Is there a way of configuring Spring so the application doesn't quit and just ignores the error and creates the table and moves on?

I'm using Oracle 19c and Spring 2.7.10

Tallen67
  • 33
  • 3
  • 1
    Does this answer your question? [Oracle: If Table Exists](https://stackoverflow.com/questions/1799128/oracle-if-table-exists) – markalex Apr 19 '23 at 22:01
  • If you are using Oracle 23c: then you can use If Table Exists clause "drop table IF EXIST PHONEBOOK; " , or else you can check if the table is present in ALL_TABLES and then Drop it. – Himanshu Kandpal Apr 19 '23 at 23:57
  • I tried the `WHENEVER SQLERROR CONTINUE NONE DROP TABLE PHONEBOOK PURGE;` statement but it gives `ORA-00900: invalid SQL statement` – Tallen67 Apr 20 '23 at 14:07
  • Found the solution, I needed to add `sql.init.continue-on-error: true` – Tallen67 Apr 20 '23 at 18:13

1 Answers1

0

sql: init: mode: always continue-on-error: true

Tallen67
  • 33
  • 3