Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetching CLOB column Faster

User_OCZ1TJul 30 2019 — edited Aug 5 2019

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% |

============================================================================================================================================================

This post has been answered by AndrewSayer on Jul 31 2019
Jump to Answer

Comments

Dave Berry-Oracle
As of the 10.3 release, OSR does not support Oracle 11g Database yet but that said, I would bet this is a db driver issue.
Helge Aarstein
Experiencing same problem with Oracle Service Reposiotry 10_3 and Oracle Databse version 10.2.0.3. Tried with several oracle jdbc drivers, both bundled with WebLogic Servers and with the database installation, but allways with the same error.
Helge Aarstein
Experienced the same problem both on initial install and using the setup script.

Discovered that there is a binding to Java version 1.4 as my installation was initially done with a 1.5 JDK. Problem was solved by running Java under JDK 1.4.
423765
Please check whether you have submitted all the drivers
553097
HI Helge Aarstein ,
Can you please let me know where you made a change to java version to 1.4. As i tried running the jar file from a location where i have installed jdk .4 but the problem still continues.Am stuck with this problem for quite a long time now any pointers will be of great help.

Thanks
553097
Following is the details from my install.log

#
#Wed Mar 18 05:23:38 IST 2009
porting.context=registry
db.system.name.condition=oracle
porting.oracle.admin.password=***
porting.oracle.https.port=443
oracle.server.host=localhost
alldb.create.datasource.name=jdbc/registryDS
porting.oracle.home=C\:\\product\\10.1.3.1\\OracleAS_1
porting.oracle.http.port=80
alldb.create.datasource.indirect.password=***
install.server.admin.mail=[ admin e-mail ]
install.os.is.win.andcondition=true
create.desktop.icons=yes
porting.oracle.admin.name=oc4jadmin
porting.make.deploy=yes
alldb.create.datasource.indirect.password.username=
porting.https.use=yes
porting.oracle.ormi.port=6003
alldb.install.registry.name=OracleAS Service Registry
install.server.admin.name=admin
account.backend.type.condition=database
alldb.install.demo.data=yes
install.server.admin.password.confirmation=***
dist.version=10.1.3.1
alldb.create.datasource=yes
install.type.condition=standalone
alldb.install.demo.data.settings=
create.menu.items=no
alldb.create.drop.condition=createSchema
porting.oracle.instance.name=home
oracle.database.name=ORCL
porting.application.name=registry
install.directory=c\:\\oracle\\registry_10_1_3
install.server.admin.password=***
oracle.server.port=1522
porting.oracle.ormi.host=localhost
db.showall.condition=false
porting.server.type.condition=standalone
porting.make.jazn=yes
alldb.jdbc.drivers.paths=C\:\\oracle\\product\\10.2.0\\db_1\\jdbc\\lib\\ojdbc14.jar
porting.hostname=pintu-pc
porting.type.condition=oraclefull
oracle.database.user=uddiuser
install.windows.menu=Oracle Application Server Service Registry 10.1.3
oracle.database.password=***
[echo] Expanding C:\Users\PINTU\Desktop\OracleServiceRegistry\oracle-service-registry-10.1.3.1.jar to c:\oracle\registry_10_1_3 ...
[echo] Building scripts ...
[echo] Platform is Windows
[echo] Preparing 'admin' account ...
[echo] Preparing account_list ...
[echo] Preparing permission_list ...
[echo] Preparing approval management ...
[echo] Creating standalone configuration ...
[java] Buildfile: c:\oracle\registry_10_1_3\etc\setup\setupserver.xml

[java] installprops:

[java] props:

[java] props2:

[java] changeSecurity:

[java] install.security:

[java] install.ssl.transport:
[java] [java] Done.
[java] [java] Done.
[java] [java] Done.
[java] [delete] Deleting: C:\oracle\registry_10_1_3\conf\authToken.crt

[java] changeports:
[java] [echo] Preparing ports ...
[java] [delete] Deleting: C:\oracle\registry_10_1_3\etc\ssl.properties
[java] [style] Processing C:\oracle\registry_10_1_3\conf\serverconf.xml to C:\oracle\registry_10_1_3\conf\serverconf.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\serverconf.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\web.xml to C:\oracle\registry_10_1_3\app\uddi\conf\web.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\web.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\web_ui.xml to C:\oracle\registry_10_1_3\app\uddi\conf\web_ui.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\web.xsl

[java] buichangeports:
[java] [mkdir] Created dir: C:\oracle\registry_10_1_3\tmp
[java] [unjar] Expanding: C:\oracle\registry_10_1_3\app\uddi\bsc.jar into C:\oracle\registry_10_1_3\tmp
[java] [style] Processing C:\oracle\registry_10_1_3\tmp\conf\web.xml to C:\oracle\registry_10_1_3\tmp\conf\web.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\web.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\tmp\conf
[java] [style] Processing C:\oracle\registry_10_1_3\tmp\conf\bsc.xml to C:\oracle\registry_10_1_3\tmp\conf\bsc.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\web.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\tmp\conf
[java] [jar] Building jar: C:\oracle\registry_10_1_3\tmp\bsc.jar

[java] buichangeports_sync_dist:
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\dist
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi
[java] [delete] Deleting directory C:\oracle\registry_10_1_3\tmp
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\configurator.xml to C:\oracle\registry_10_1_3\app\uddi\conf\configurator.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\configurator.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\node.xml to C:\oracle\registry_10_1_3\app\uddi\conf\node.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\node.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\security.xml to C:\oracle\registry_10_1_3\app\uddi\conf\security.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\security.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf

[java] changeTModelPorts:

[java] changePortsOperationalBE:
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\data
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\data
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\data
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\data

[java] importOperationalTM:

[java] importOperationalBS:

[java] changePortsOtherTModels:
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\data

[java] importOtherTModels:

[java] changeDemoDataPorts:

[java] changePortsDemoData:
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\demos\conf
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\demos\conf

[java] importDemoData.yes:

[java] copyDocDemos:
[java] [echo] Copying data to 'c:\oracle\registry_10_1_3/doc/demos'...
[java] [copy] Copying 13 files to C:\oracle\registry_10_1_3\doc\demos\bsc
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\doc\demos

[java] create.desktop.icons.yes:

[java] create.menu.items.no:

[java] applicationCore:
[java] [echo] Preparing applicationCore ...
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\application_core.xml to C:\oracle\registry_10_1_3\app\uddi\conf\application_core.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\application_core.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [delete] Deleting: C:\oracle\registry_10_1_3\etc\smtp.properties

[java] setup:
[java] [echo] Configuring Registry...

[java] type.standalone:
[java] [delete] Deleting directory C:\oracle\registry_10_1_3\registry_10_1_3

[java] BUILD SUCCESSFUL
[java] Total time: 1 minute 38 seconds
[java] Buildfile: c:\oracle\registry_10_1_3\etc\setup\install.xml

[java] properties:

[java] license:

[java] [java] License key has been accepted.


[java] BUILD SUCCESSFUL
[java] Total time: 4 seconds
[java] Buildfile: c:\oracle\registry_10_1_3\etc\setup\directory.xml

[java] directory:
[java] [delete] Deleting: C:\oracle\registry_10_1_3\etc\ldap.properties
[java] [echo] Preparing LDAP backend ...
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\directory.xml to C:\oracle\registry_10_1_3\app\uddi\conf\directory.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\directory.xsl
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [delete] Deleting: C:\oracle\registry_10_1_3\app\uddi\conf\directory.xml.new

[java] account_core:

[java] account_core.database:
[java] [echo] Preparing database account backend ...
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\account_core.xml to C:\oracle\registry_10_1_3\app\uddi\conf\account_core.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\account_core_backend.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\group_core.xml to C:\oracle\registry_10_1_3\app\uddi\conf\group_core.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\group_core_backend.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf

[java] BUILD SUCCESSFUL
[java] Total time: 3 seconds
[java] Buildfile: c:\oracle\registry_10_1_3\etc\setup\database.xml

[java] database:

[java] database.createSchema:

[java] database.create:

[java] account_core:

[java] account_core.database:
[java] [echo] Preparing database account backend ...
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\account_core.xml to C:\oracle\registry_10_1_3\app\uddi\conf\account_core.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\account_core_backend.xsl
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\group_core.xml to C:\oracle\registry_10_1_3\app\uddi\conf\group_core.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\group_core_backend.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\app\uddi\conf

[java] server.database:
[java] [echo] Copying JDBC drivers C:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar...

[java] db.create.schema:

[java] uddi.class.path.jars_zips:

[java] oracle.database:

[java] create_schemas:
[java] [echo] Creating schema ...

[java] create_schema:
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\etc\db\oracle

[java] create:
[java] [java] Properties:
[java] [java] user:uddiuser
[java] [java] password:***
[java] [java] charSet:UTF8
[java] install.schema:

[java] uddi.class.path.jars_zips:

[java] oracle.data:

[java] changeDBParams:
[java] [style] Processing C:\oracle\registry_10_1_3\etc\db\data\operationalBE.xml to C:\oracle\registry_10_1_3\etc\db\data\operationalBE.xml.new
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\operationalBE.xsl
[java] [move] Moving 1 files to C:\oracle\registry_10_1_3\etc\db\data
[java] [copy] Copying 1 file to C:\oracle\registry_10_1_3\app\uddi\conf
[java] [style] Processing C:\oracle\registry_10_1_3\app\uddi\conf\tmp.xml to C:\oracle\registry_10_1_3\app\uddi\conf\database.xml
[java] [style] Loading stylesheet C:\oracle\registry_10_1_3\etc\bin\database.xsl
[java] [delete] Deleting: C:\oracle\registry_10_1_3\app\uddi\conf\tmp.xml
[java] [delete] Deleting: C:\oracle\registry_10_1_3\etc\db.properties

[java] importData:
[java] [echo] Importing system data ...

[java] importGroup:
[java] [echo] c:\oracle\registry_10_1_3/etc/db/data/groups.xml
[java] [java] ERROR: database_core.com.systinet.uddi.database.ApiManager - Can not connect database.
[java] [java] EXCEPTION: (12021) Cannot obtain new connection.
[java] [java] com.systinet.uddi.database.DatabaseCoreException: (12021) Cannot obtain new connection.
1 - 6

Post Details

Added on Jul 30 2019
20 comments
28,307 views