Hi, We are using version 220.127.116.11 of oracle exadata. We have below query which fetch the data and is getting executed from informatica. And its just a "SELECT * from TAB1" query. It was taking ~1hr for fetching ~135k rows and then from the sql monitor we found all the time was just spent on client for fetching data. And then we got to know it has one CLOB column which is causing the issue, if we comment the CLOB column(C10) , the data fetch is finishing in few seconds. So as an alternative we were using below SUBSTR option to fetch the column C10 and it was helping us to finish the query in few seconds. But suddenly we got to see failure for this query with error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" and then its found its failing because of few of the values came into column C10 which were holding values >4000 bytes. So want to understand if there is any alternate way we can fetch the clob column here without fail and for large value(>4000bytes)?
SELECT c1,c2,c3...c39 FROM TAB1;
Alternate option to fetch column C10:-
DBMS_LOB.SUBSTR (C10,(SELECT MAX (LENGTH (C10)) FROM TAB1)) C10
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
Below is the sql monitor for one of the slow execution which we used to see with CLOB column fetched in full:-
Query:- SELECT c1,c2,c3...c39 FROM TAB1; Global Information ------------------------------ Status : EXECUTING Instance ID : 4 SQL Execution ID : 67108864 Execution Started : 04/09/2018 06:02:49 First Refresh Time : 04/09/2018 06:02:49 Last Refresh Time : 04/09/2018 06:40:45 Duration : 2277s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 26415 Global Stats ================================================= | Elapsed | Cpu | Cluster | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.69 | 0.69 | 0.01 | 26415 | 27031 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=2531190874) ============================================================================================================================================================ | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | Progress | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | | ============================================================================================================================================================ | -> 0 | SELECT STATEMENT | | | | 2278 | +0 | 1 | 26417 | | | | | -> 1 | TABLE ACCESS STORAGE FULL | TAB1 | 135K | 7212 | 2278 | +0 | 1 | 26417 | | | 6% | ============================================================================================================================================================