CODEWITHSUNDEEP
sqlspringoracle-databasehibernatejpa
duppydodah
duppydodah

Reputation: 195

Hibernate/JPA - Save/Insert functions failing org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a];

I am getting this error every time I try to hit one of my POST endpoints in my spring app that simply calls 1 of these 3 JPA save methods:

save()
saveAll()
saveAndFlush()

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet .... Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist .... Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist

A few things I want to first clarify:

  1. I am logged in through a non-table owner user that has been granted select, insert, update, & delete privileges. I have tested in an SQL client to confirm that my user does have access to these 4 sql operations.
  2. There's NO issue using any of the other JPA methods like findAll, findAllById, count, etc. Even methods that I created under my @Repository classes. Tables are able to be found and queried against with no issue. Additionally, there is nothing abnormal about the tables or repositories.
  3. When using the "owner" user of the table I am able to successfully save any data without issue.
  4. The tables/repositories are imported as a dependency from some other project.

The only thing that I have found that relates to this specific JPA save issue is this article where it mentions that

Hibernate, PostgreSQL : Could not extract resultset, SQLState , Your actual problem is simple: you called a save() operation in a read-only transaction. But, because you swallow your exceptions with: } <pgsql-jdbc(at)postgresql(dot)org> Subject: Re: Update ResultSet: Date: to extract the name of the table to update or throw an "ResultSet is not updateable

I have no idea where to start on trying to solve this error. These are the versions of related dependent code, although I have also played with these without any luck.

I am also using this as my property which does successfully validate my DB schema otherwise the service would fail on start up. I have seen people say to use "update" but, that didnt help and should not be the solution.

spring.jpa.hibernate.ddl-auto=validate

Any help is appreciated. I don't think it is a code problem but, at the same time I cannot find anything on the oracle DB side that seems out of the ordinary.

Upvotes: 1

Views: 7057

Answers (3)

Anshul
Anshul

Reputation: 11

just use update query of hibernate in properties files

spring.jpa.hibernate.ddl-auto=update

It will refresh and update your database

I have same problem but resolved by this,it was working in spring boot

Upvotes: 1

duppydodah
duppydodah

Reputation: 195

SOLUTION:

After enabling

spring.jpa.show-sql=true

I realized that this was the first line of sql it was executing before crashing.

Hibernate: select hibernate_sequence.nextval from dual

The reason, every JPA save operation utilized this sequence was because I am using a @GeneratedValue(strategy = GenerationType.AUTO) on my @Id for my tables. To resolve, I simply executed grant permissions to the non_owner_acct to that SEQUENCE.

GRANT SELECT ON "SCHEMA"."HIBERNATE_SEQUENCE" TO "NON_OWNER_ACCT";

Upvotes: 1

Alexey Gorelik
Alexey Gorelik

Reputation: 81

some thoughts that came to my mind:

  • Check entity relations. Maybe you save another entity by @OneToMany (etc.) inside your entity and you don't have permissions for another table
  • If it's Oracle - check sequences permissions for the tables that you're using. You also need privileges for them
  • If there are any triggers - you also can review your access there

Upvotes: 1

Related Questions