Exception ORA-08103: object no longer exists on using setfetchsize of Hibernate
Most likely that a cursor is opened based on a global temporary table(GTT), which had been created with ON COMMIT DELETE ROWS
option. And the cause of the ORA-08103: object no longer exists
error is commit
statement that followed right after the delete
statement. Here is a simple example:
SQL> declare
2 type t_recs is table of number;
3 l_cur sys_refcursor; -- our cursor
4 l_rec t_recs;
5
6 begin
7
8 -- populating a global temporary table GTT1 with sample data
9 insert into GTT1(col)
10 select level
11 from dual
12 connect by level <= 1000;
13
14 open l_cur -- open a cursor based on data from GTT1
15 for select col
16 from GTT1;
17
18 -- here goes delete statement
19 -- and
20 commit; <-- cause of the error. After committing all data from GTT1 will be
21 -- deleted and when we try to fetch from the cursor
22 loop -- we'll face the ORA-08103 error
23 fetch l_cur -- attempt to fetch data which are long gone.
24 bulk collect into l_rec;
25 exit when l_cur%notfound;
26 end loop;
27
28 end;
29 /
ORA-08103: object no longer exists
ORA-06512: at line 24
Recreation of global temporary table with on commit preserve rows
clause will allow to safely fetch data from a cursor that is based on that table without being afraid of facing ORA-08103:
error.
ORA-08103: object no longer exists - insert query fails
The table is partitioned as I've mentioned, so between midnight - 3:00AM the partitioned changes and this under some instances the error occurs.
How to rollback all committed transactions if any error encountered while reading csv file records using hibernate
Once a transaction is comitted it can't be reversed. Not in Hibernate, not in any database!
To keep your operations reversible you need to keep them in the same single, not yet comitted transaction and if any (by default runtime) exception arises it will automatically be rollbacked and all changes reversed.
You tagged your question as Spring, in Spring you can use @Transactional annotation over the method, and all changes in that method will be reversed in case of any transaction, including other methods called from that method.
more here https://www.baeldung.com/transaction-configuration-with-jpa-and-spring
also there is any example here https://javamondays.com/spring-transactions-explained/
How to create and use temporary table in oracle stored procedure?
Just create it first (once, outside of your procedure), and then use it in your procedure. You don't want to (try to) create it on every call of the procedure.
create global temporary table tmp(x clob)
on commit delete rows;
create or replace procedure...
-- use tmp here
end;
No more data to read from socket error
For errors like this you should involve oracle support. Unfortunately you do not mention what oracle release you are using. The error can be related to optimizer bind peeking. Depending on the oracle version different workarounds apply.
You have two ways to address this:
- upgrade to 11.2
- set oracle parameter
_optim_peek_user_binds = false
Of course underscore parameters should only be set if advised by oracle support
Related Topics
How to Store Longitude & Latitude as a Geography in SQL Server 2014
Oracle Pivot Query Gives Columns with Quotes Around the Column Names. What
How to Take Sum of Column with Same Id in SQL
SQL Server If Not Exists Usage
What Is "Structured" in Structured Query Language
Linq to SQL Every Nth Row from Table
How to Determine Values for Missing Months Based on Data of Previous Months in T-Sql
Is Bigint(8) the Largest Integer MySQL Can Store
How to Treat Max() of an Empty Table as 0 Instead of Null
How to Perform a Bitwise Group Function
#1146 - Table 'Phpmyadmin.Pma_Tracking' Doesn't Exist
Why Is Selecting Specified Columns, and All, Wrong in Oracle SQL
In SQL Is There a Difference Between Count(*) and Count(<Fieldname>)
Pros and Cons of Autoincrement Keys on "Every Table"
Ssdt Failing to Publish: "Unable to Connect to Master or Target Server"