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.

Query Result Cache: Support collection and object datatypes

User_1871Jul 5 2022

Query Result Cache:
Regarding the Query Result Cache hint /*+ result_cache */ :
15 Tuning the Result Cache
A result cache is an area of memory, either in the Shared Global Area (SGA) or client application memory, that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale.
As far as I can tell, queries with collection and object datatypes aren't supported.
-------------------------------------------------------------
Test #1:
A query with a simple number column successfully invokes the Query Result Cache hint: /*+ result_cache */.

with data (id) as (
  select 1 from dual union all
  select 2 from dual
)
select /*+ result_cache */
    id
from
    data

Line 2 in the explain plan shows the RESULT CACHE being used:

-----------------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     2 |     6 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | 478vfsvhadjt55zu0vzbphb9f5 |       |       |            |          |
|   2 |   VIEW           |                            |     2 |     6 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL     |                            |       |       |            |          |
|   4 |     FAST DUAL    |                            |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |                            |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Result Cache Information (identified by operation id):
------------------------------------------------------
 
   1 - column-count=1; name="..."

Test #2:
The next query is the same, except I've added a varray column (a collection):

with data (id, my_array) as (
  select 1, sys.odcivarchar2list('a', 'b', 'c') from dual union all
  select 2, sys.odcivarchar2list('d', 'e') from dual
)
select /*+ result_cache */
    id,
    my_array
from
    data

The explain plan shows that the RESULT CACHE isn't being used.

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |    74 |     4   (0)| 00:00:01 |
|   1 |  VIEW            |      |     2 |    74 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |       |            |          |
|   3 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

db<>fiddle
I have the same problem for object datatypes like SDO_GEOMETRY.
-------------------------------------------------------------
Idea:
Could the Query Result Cache be enhanced to support queries with collection and object datatypes?

Feel free to let me know if I've misunderstood something.

Comments

Abhishekshahi-Oracle

Feel free to ask a question around this.

2797856

Feel free to ask a question around this.

Hi Abhishek,

Is it only applicable for PL-SQL functions or PL-SQL procedures as well. Can I do this for an Oracle Apps Procedure which is inside a Package.

Regards,

Nikhil

Hi Abhishek,

Is it only applicable for PL-SQL functions or PL-SQL procedures as well. Can I do this for an Oracle Apps Procedure which is inside a Package.

Regards,

Nikhil

Hi Nikhil,

It is applicable for functions and procedures both with certain limitations. Refer - https://docs.oracle.com/cd/E35521_01/user.111230/e17455/dev_web_services.htm#OJDUG1293 for more details.

Yes, you can do it for Oracle Apps Procedures also which are inside packages but you should be aware that it doesn't support refcursor as return type. more limitations etc details are in the provided link.

Let me know if you need further help.

Thanks,

-Kumar

Guravaiah Tata

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Thanks Guru!

Namit Kakkar-Oracle

Hi Kumar,

This is very well explained and helpful

Thanks

Namit.

2807810

Feel free to ask a question around this.

Hi Abhishek,

What do I need to change (or add) to generate a REST web service instead?

Thank you.

Fede.

2807810

Hi Kumar,

How do I add a security header to the web service that validates from a users' table?

Thanks,

Fede.

Abhishekshahi-Oracle

Hi Abhishek,

What do I need to change (or add) to generate a REST web service instead?

Thank you.

Fede.

Hi Fede,

There is no mechanism to generate Rest-ful services on PL/SQL code directly but you can write a light java client on top of PL/SQL code and publish that client as restful service. Please find below link for creating Restful WebServices,

https://docs.oracle.com/cd/E35521_01/user.111230/e17455/dev_web_services.htm#OJDUG1400

Hope it helps.

Regards,

-Kumar

Abhishekshahi-Oracle

Hi Kumar,

How do I add a security header to the web service that validates from a users' table?

Thanks,

Fede.

Hi Fede,

Adding Security to the services are explained here using JDeveloper. https://docs.oracle.com/middleware/1212/jdev/OJDUG/dev_web_services.htm#OJDUG1293

Let me know if you need further help.

Regards,

-Kumar

Charly_Z

Hi!

Thanks for this helpful howto!

I've tried it and it works for simple packages, like the one in the example.

In my real-world environment I have two problems:

1.) Some of my procedures (same for tables) aren't generated. I only get "WARNING: No tables were found matching the following: [ARCHIVDBAP.ARCHIVE]"

--> This table exists and my jdbc-connection is with user archivdbap. Any idea why some objects are not generated. Btw. I can select the objects in the ToplinkDBWebService-Wizard!

2.) How do I change/regenerate the generated  webservice? There is no properties-window. So I only can completely regenerate everything. Is the a way to adapt/change the WEB-Service?

Charly

Florin Marcus

I am trying to make sense of such solution, from architectural point of view:

I can understand you may expose a  PL SQL web service to a third party - that makes sense.

But why would I expose such service for my own consumption ?  I already have JDBC drivers which offer more performant way of calling PL SQL code (statement caching, fetch size ).

Why would I go though all the marshalling/unmarshalling  of web service calls for these use cases?

Thanks,

Florin

user1056463

Hi Kumar Abhishek Shahi,

I've tried your example in JDeveloper 12.1.3, but could not complete it.

When I try to test the webservice created, it gives me a pop-up message Invalid URL with this:

The service endpoint URL is invalid.

Any ideas?


Many thanks,

Tatiana

Abhishekshahi-Oracle

Hi!

Thanks for this helpful howto!

I've tried it and it works for simple packages, like the one in the example.

In my real-world environment I have two problems:

1.) Some of my procedures (same for tables) aren't generated. I only get "WARNING: No tables were found matching the following: [ARCHIVDBAP.ARCHIVE]"

--> This table exists and my jdbc-connection is with user archivdbap. Any idea why some objects are not generated. Btw. I can select the objects in the ToplinkDBWebService-Wizard!

2.) How do I change/regenerate the generated  webservice? There is no properties-window. So I only can completely regenerate everything. Is the a way to adapt/change the WEB-Service?

Charly

Hi Charlie,

I am sorry for the late reply.

1. If table exists and accessible it should be generated. Can you please try regenerating it? Also please check the permissions for the table.

2. There is only way i know is to create a fresh application and regenerate everything. Fixing could not be done automatically using JDeveloper and it could lead to some issues.

Hope it helps.

Thanks,

-Kumar

Abhishekshahi-Oracle

I am trying to make sense of such solution, from architectural point of view:

I can understand you may expose a  PL SQL web service to a third party - that makes sense.

But why would I expose such service for my own consumption ?  I already have JDBC drivers which offer more performant way of calling PL SQL code (statement caching, fetch size ).

Why would I go though all the marshalling/unmarshalling  of web service calls for these use cases?

Thanks,

Florin

Hi Florin Marcus,

Architecture is something which is also done for the best use of resources and to provide an optimal solution. This is a simple approach to reuse complex db packages existing in legacy system.

I agree to your question, if we are allowed to make a db connection to the legacy db system then it would be better managed and faster. But what if you are not allowed to do so? As Web Services allows consumers to have a contract based access, it falls under that category.

Thanks,

-Kumar

Abhishekshahi-Oracle

Hi Kumar Abhishek Shahi,

I've tried your example in JDeveloper 12.1.3, but could not complete it.

When I try to test the webservice created, it gives me a pop-up message Invalid URL with this:

The service endpoint URL is invalid.

Any ideas?


Many thanks,

Tatiana

Hi Tatiana,

I have also attached code sample to the article, Can you please try that? Hope that helps.

Thanks,

-Kumar

Florin Marcus

Hi Florin Marcus,

Architecture is something which is also done for the best use of resources and to provide an optimal solution. This is a simple approach to reuse complex db packages existing in legacy system.

I agree to your question, if we are allowed to make a db connection to the legacy db system then it would be better managed and faster. But what if you are not allowed to do so? As Web Services allows consumers to have a contract based access, it falls under that category.

Thanks,

-Kumar

Abhishekshahi-Oracle wrote:

Hi Florin Marcus,

I agree to your question, if we are allowed to make a db connection to the legacy db system then it would be better managed and faster. But what if you are not allowed to do so?

Then your architect should go in front of management and explain the reasons the application will be 15% slower, 15% more expensive and 15% less scalable.

Of course I am making up those numbers, but this seems like a trend: architects without a solid understanding of technologies take this decisions 'a priori'.

Then people end up saying "ADF is Slow"

user12216356

Good tutorial but I cannot understand what Step 5. Drag Data Control as method->AdfButton. Change label to “Convert.” .Change ActionListener to Action for AdfButton. means? maybe becuse I'm not that familiar with JDeveloper - could you be more specific? I have followed everything up to the last step, very frustrating!

Abhishekshahi-Oracle

Good tutorial but I cannot understand what Step 5. Drag Data Control as method->AdfButton. Change label to “Convert.” .Change ActionListener to Action for AdfButton. means? maybe becuse I'm not that familiar with JDeveloper - could you be more specific? I have followed everything up to the last step, very frustrating!

Hi,

My apologies if it was not as good to be understandable. To clarify your doubt,

Drag Data Control as method->AdfButton. Change label to “Convert.” .Change ActionListener to Action for AdfButton.

Sentence 1, when you expand "Data Control" section from left panel and drag data control instance to your page, it will launch context menu where you will find option of creating ADF Button.

Sentence 2,  If you go to source view of jspx page and see code generated for CommandButton you will find an attribute "actionListener". Replace this attribute with "action".


Hope it clarifies.


Thanks,

-Kumar

G Vijay

Hi Abhishek,

I tried the above approach for one DB procedure... But facing few issues .. Created discussion thread in Oracle Community.. Below is the details of the same.. Can you please see the details and guide me how t oproceed in this kind of scenario..

How to generate REST webservice Using Jdeveloper12c for a PL/SQL Object

Hi,

Thanks for the detail article. I've managed to expose functions as Webservice and all works but I was expecting a slightly different Soap response.

The function I used returns XML, but the response I get from the 'Test Webservice' (Jdeveloper) or in SoapUI, I see that the XML that I am expecting has been wrapped in CDATA. and the start of the XML response <LoadCamt052Response> has been escaped to &lt. I've tried creating XSD's to define the output, but am new to this and the results are the same.

The Functions I've tried are returning a Varchar, an XMLTYPE and a CLOB. Each does the same thing.

Example below

<?xml version = '1.0' encoding = 'UTF-8'?>

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <env:Header/>

   <env:Body>

      <srvc:loadCamt052ClobResponse xmlns:srvc="http://project1/Project1_TopLinkPlSqlProviderServiceService">

         <srvc:result>

            <simple-xml-format>

               <simple-xml>

                  <RESULT>&lt;LoadCamt052Response>

&lt;Id>346&lt;/Id>

&lt;DateProcessed>2016-07-19T13:35&lt;/DateProcessed>

&lt;/LoadCamt052Response></RESULT>

               </simple-xml>

            </simple-xml-format>

         </srvc:result>

      </srvc:loadCamt052ClobResponse>

   </env:Body>

</env:Envelope>

Wondered if you have come across this problem and know a solution.

Thanks

Robin

mmhanaoka

Hi!

Thanks for this helpful howto!

I've tried it and it works for simple packages, like the one in the example.

In my real-world environment I have two problems:

1.) Some of my procedures (same for tables) aren't generated. I only get "WARNING: No tables were found matching the following: [ARCHIVDBAP.ARCHIVE]"

--> This table exists and my jdbc-connection is with user archivdbap. Any idea why some objects are not generated. Btw. I can select the objects in the ToplinkDBWebService-Wizard!

2.) How do I change/regenerate the generated  webservice? There is no properties-window. So I only can completely regenerate everything. Is the a way to adapt/change the WEB-Service?

Charly

I had this same problem while connections of applications resources where closed, but it worked after opening this database connection and using it to create the application as described above.

Hope it helps others with same problem

User_24BD9

Hi Tatiana,

I have also attached code sample to the article, Can you please try that? Hope that helps.

Thanks,

-Kumar

Hi Abhishek,

I did followed all steps mentioned in above blog and getting similar error which Tatlana was getting.

The service endpoint URL is invalid.

Tatlana - was you issue resolved.

Please help me, this blog and solution mentioned will help me a lot.

Thanks,

Kunal

Pritam Biswas

Hi,

I've tried to follow the steps- but after executing

create or replace PACKAGE BODY Utility AS

FUNCTION UsdToInr(usdAmt IN number) 

RETURN number IS 

   inrAmt number(5) := 0; 

BEGIN 

   SELECT usdAmt\*60 into inrAmt from dual; 

   RETURN inrAmt; 

END UsdToInr; 

END Utility;

I'm getting the below error.

pastedImage_0.png

pastedImage_0.png

Could you please help me on that?

Thanks,

Pritam

Hi Abhishek,

We were successful in publishing the PL/SQL package as a webservice.

http://127.0.0.1:7101/SQLFunctionAsService-Project1-context-root/UsdToInrService?WSDL is the URL.

Please let us know how to make it accessible across network/over the internet.

Regards,

Subhashini

User_24BD9

Hi Kumar,

We able to use this solution and publish web service. issue which we are facing is to update webservice after it is published and shared with third party.

simple thing is parameter changes, data type changes, if we have to use this in prod there will be many occasions where we have to change these web services for these issues.

Appreciate your help, pointers.

Thanks KP

Cvele_new_account

Hi,

What is exactly jdev version you used when building that sample?

Because we tried 12.2.1.1, and have problem with WS....

Cvele_new_account

How about deployment process  (on stand-alone WebLogic) of such one web service?

Any info about this?

Pritam Biswas

Hi,

What is exactly jdev version you used when building that sample?

Because we tried 12.2.1.1, and have problem with WS....

Try with Jdev's latest version. I had faced problem using the same version.

Ram Inturi

How can we deploy this web service app in the production server using different database details? Looks like database connection is binding with the app, we can't change it.

I tried to deploy the app into a stand-alone WebLogic server which was running in Jdeveloper 12.2.1.3 integrated server. I got the below error.

"Error weblogic.common.ResourceException: No credential mapper entry found for password indirection user=hr for data source ADF"

Cvele_new_account

How can we deploy this web service app in the production server using different database details? Looks like database connection is binding with the app, we can't change it.

I tried to deploy the app into a stand-alone WebLogic server which was running in Jdeveloper 12.2.1.3 integrated server. I got the below error.

"Error weblogic.common.ResourceException: No credential mapper entry found for password indirection user=hr for data source ADF"

In essence, you should Uncheck 'Auto Generate and Synchronize Weblogic JDBC Description During Deployment

in Deployment option, see this:

https://stackoverflow.com/questions/9360871/weblogic-error-no-credential-mapper-entry-found-for-password-indirection-use…

Ajax_ords

Hi Kumar,

How do I add a security header to the web service that validates from a users' table?

Thanks,

Fede.

I've created web service from plsql using your blog and i was trying to secure the service using https://docs.oracle.com/middleware/1212/jdev/OJDUG/dev_web_services.htm#OJDUG1293

But I'm unable to follow it completely. I created a user and maps it against a group. Then i created a policy and assigned with the role that i created earlier. Finally this policy was assigned to a service.

But it doesn't seem to work. i.e i can access the web service directly with no authentication and authorization.

User_AT1ZX

Hi Abhishek,
I did followed all steps mentioned in above blog and getting similar error which Tatlana was getting.
The service endpoint URL is invalid.
Tatlana - was you issue resolved.
Please help me, this blog and solution mentioned will help me a lot.
Thanks,
Eduardo

1 - 33

Post Details

Added on Jul 5 2022
2 comments
529 views