APEX

Feel free to ask a question around this.

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

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Hi Kumar,

Its helpful post.

Thanks & Regards,

Guravaiah Tata.

Thanks Guru!

Hi Kumar,

This is very well explained and helpful

Thanks

Namit.

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.

Hi Kumar,

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

Thanks,

Fede.

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

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

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

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

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

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

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"

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!

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

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

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

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

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.

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

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

Hi,

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

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

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

Any info about this?

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.

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"

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…

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.

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