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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Timo Hahn

Have you read https://www.jobinesh.com/2010/04/invoking-applicationmodule-from-servlet.html?m=1
This blog sites what to do to access an am from a servlet.

Timo

dvohra21

since the conversion no AM is available.
What happens when AM is accessed?

Filip Huysmans

@dvohra21 the binding is not found, so no AM.

dvohra21

Did Timo's reference to getting a binding context help?

BindingContext bindingContext=null;
try {
    bindingContext = BindingContext.getCurrent();
    DCBindingContainer dcBindingContainer =
    bindingContext.findBindingContainer("view_dummyPageDef");
    JUCtrlRangeBinding depts =
    (JUCtrlRangeBinding)dcBindingContainer.findCtrlBinding("DepartmentsView1");
    dcBindingContainer.refreshControl();
    // Business logic goes here...
 }finally {
   // Clean up goes here...
}
Filip Huysmans

Hi @dvohra21 and @timo-hahn1 ,
Thx for your info.
This works for 1 operation of my authentication servlet, getting the user info.
Another operation I have is setting the language, which I get from the SSO application, in the AM, so this can be used to fetch the correct data throughout all the views. This means I need the same instance of the AM to get this working and so creating an new instance of the AM with Configuration.createRootApplicationModule, will not help me.
A second problem is that my servlet does not have a seperate url, it is the same url as the pages, as it verifies for each request that the user is valid. If I want to implement the solution of adding manually a pagedef file, to which url should I then bind this pagedef file? The same challenge exist for setting this language in the FacesContext environment.
Thank you in advance.
Filip

Filip Huysmans

@dvohra21
BindingContext.getCurrent() returns null in my filter.

FYI

Timo Hahn

Have you read the blog and configured the ADFBindingFilter to ADFServletFilter for the servlet?
Only then can you get the binding context.

Timo

dvohra21

Only a code snippet was posted but other config is also needed.

Filip Huysmans

web.xml.txt (10.49 KB)I uploaded my web.xml file, I removed the init-params and context-params specific for my application.
I just also added the adfauthentication filter to the mappings of the adfbinding filter, without succes. Still no access to FacesContext.getCurrentInstance().
Am I still missing something?
How do you " configured the ADFBindingFilter to ADFServletFilter for the servlet"?
Small remark: My code is in a filter, not a servlet.

Thx

Filip Huysmans

Hi everyone,
Some things I tried:
Oracle Support Doc 2423831.1 : adfc variable is empty/null in my case

adfc = ADFContext.initADFContext(httpRequest.getServletContext(), httpRequest.getSession() , httpRequest, servletResponse);

Amis Document : bcx variable in the ConfigurePageDef method, remains null

BindingContext bc = DCUtil.getBindingContext(httpRequest);

BindingContext method remains null

BindingContext bc2 = BindingContext.getCurrent();

All these calls are done in the doFilter method of my AuthenticationFilter class, as defined in the web.xml.

Thx for your help on this.

Timo Hahn

Filip,
you can look at
https://github.com/oracle/adf-samples where you find a sample 'Custom Servlet Filter to Register a Globally-Available Data Control.' This sample from way back implements your use case. I'm not sure it still runs as it uses struts as view technology, but the filter implementation should still work.
If this doesn't help, you can provide a complete test case that we can use to reproduce the problem. Just looking at some files and code snippets won't help.
Timo

Filip Huysmans

Hi [Timo Hahn](/ords/forums/user/Timo Hahn) & dvohra21 ,
sorry for the delay, but due to holidays I had to postpone my search.
Attached an example. No additional actions are required to run this example.
DemoAuthFilterWithContext.zip (349.21 KB)I got the bindingContext working, through the correct web.xml configuration:
image.pngI used to use URI Path, but the Servlet config is working better.
The only thing I'm still missing is access to the FacesContext.
I added the call in my CustomAuthenticationFilter.java class at line 55. Running the app will result in a NPE.
Is there a way to get this FacesContext instance? I use it to set the local on the ViewRoot:

FacesContext.getCurrentInstance().getViewRoot().setLocale(Locale.FRENCH);

All help is more than welcome.

Thx

dvohra21

The issue could be :
My code is in a filter, not a servlet.
Shouldn't the code be in a servlet?

Filip Huysmans

Hi @dvohra21 ,
indeed, good question. Because it was like that in 11g.
I created a new version of my demo app, with the same code in a servlet.
DemoAuthFilterWithContext.zip (455.8 KB)This results in the same problem. FacesContext is not accessible.
Thank you for your help on this.
Filip

dvohra21

How is the CustomServletFilter class a servlet? It doesn't extend the javax.servlet.http.HttpServlet.
Please refer to https://blogs.oracle.com/groundside/post/integrating-adf-and-servlets

Filip Huysmans

Hi @dvohra21 ,
thanks for the example. But unfortunately this is not my usecase.
The code in my AuthenticationFilter is to add security checks and settings before a page is being called.
We are using an SSO-solution in front of our ADF Application.
In the case of the servlet, it can not stop the call to a page, nor can it set security settings in the session context before the execution of a page or taskflow.
Therefore I believe the use of a servlet is not a solution for my situation. Do you agree?

Thx

dvohra21

Thanks for the update, even though the earlier message is "I created a new version of my demo app, with the same code in a servlet."

Filip Huysmans

@dvohra21 yes, in the hope this could also work, but it seems that for my use case I need a filter.
Is this assumption correct?
If so, what can I do to make it work?
Thx

dvohra21

A servlet is needed. "The FacesServlet servlet manages the request processing lifecycle in JSF applications. FacesServlet creates an object called FacesContext, which contains the information necessary for request processing"
https://docs.oracle.com/cd/E16340_01/web.1111/b31974/adf_lifecycle.htm

Filip Huysmans

Hi @dvohra21 ,
ok, but how can I couple my servlet to this FacesServlet and make sure mine is executed after the FacesServlet one?
We can not put 2 servlets on the same URI path.
Thx
Filip

Timo Hahn

@filip-huysmans I'm somewhat confused by the long discussion. Can you summarize the current state, please?
I understood that you had two problems.
Can you access the application module from your filter?
Can you set the language inside the filter?

Timo

Filip Huysmans

Hi @timo-hahn1 ,
I am able to access the AM from within the filter.
Now I still want to access the FacesContext from inside this filter, to set the language.
Accessing the FacesContext still fails.

Thx

Timo Hahn

Do you have access to the DCDataControl?
In this case, you can call setLocaleContext() on the DCDataControl the rest should be handled for you. This internally will call the setLocaleContext() on the ADF BC Session object and the application module.

Timo

Filip Huysmans

Hi [Timo Hahn](/ords/forums/user/Timo Hahn) ,
I am not accessing the MC, but the FacesContext:

FacesContext.getCurrentInstance().getViewRoot().setLocale(Locale.FRENCH);

I suppose this is not the same thing as the ADF BC Session object or am I wrong?

Timo Hahn

The access to the viewRoot will not work as it is not present inside the filter.
I would set the locale directly to the DcDataControl. This will set it to the application module, and this should allow you to get the correct data inside the application module.

Timo

Filip Huysmans

Hi @timo-hahn1 & @dvohra21 ,
I stop this thread, since we can not find a solution to my request:
Access the application module and FacesContext from a filter
This works without any issue with jspx, but once the facelets are enabled, this fails.
No idea why there is a difference between those 2 approaches at the level of the filters.

Thx a lot for you help.

Filip

dvohra21

Which two servlets? The downloadable sample zip does not include any servlet.

1 - 27

Post Details

Added on Jul 30 2019
20 comments
28,118 views