Hi, We are using version 11.2.0.4 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)?
Query:-
SELECT c1,c2,c3...c39 FROM TAB1;
Alternate option to fetch column C10:-
DBMS_LOB.SUBSTR (C10,(SELECT MAX (LENGTH (C10)) FROM TAB1)) C10
Error:-
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% |
============================================================================================================================================================