Forum Stats

  • 3,767,979 Users
  • 2,252,735 Discussions
  • 7,874,399 Comments

Discussions

ORDS on Multi Schema, AutoREST option and Deployment option

Hi,

I have some questions regarding ORDS.

I have many Oracle Packages addressing very very complex business requirements. They are developed over 20 years, with close to 1000 Functions or Procedures in total. These Functions or Procedures are mostly returning ref cursor.

At present these Oracle Packages are used by only one reporting application producing PDF reports.

Now, a new requirement has come. We are expecting some existing applications (.Net, Java and Angular based) required to present the same reporting application data on the front end. And they want use the same Oracle functions and procedure,

I am asked to expose the existing Oracle functions and procedure as APIs, so that these or any other new future application can use them.

To do this , I am looking at ORDS, and not 100% clear by reading documentation on how to use it works.

I have below question on using ORDS,

1> Current functions and procedure that we plan to expose as API are all defined in Reporting schema.  And then they have inline PL/SQL that may refer other Financial schema tables or functions.

So do I have to enable both Reporting and Financial schema for ORDS?

2> Is there any easy way to REST enable all my Tables, Views and all Functions and Procedures inside all my packages to create related REST endpoints quickly? I see there is an option of AutoREST schema, however it appears that I still have to manually REST enable each object to create end points.

3> From Deployment perspective, Is ORDS expected to be deployed on a separate WebLogic server with no other application on it, or could I deploy it on any existing WebLogic server?

4> is ORDS right choice here ? or there is any other better way to expose existing Oracle Procedures and Functions inside Oracle Packages as API?


Thanks,

Pankaj

Tagged:

Best Answer

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,056 Employee
    Accepted Answer

    I have below question on using ORDS,

    1> Current functions and procedure that we plan to expose as API are all defined in Reporting schema. And then they have inline PL/SQL that may refer other Financial schema tables or functions.

    So do I have to enable both Reporting and Financial schema for ORDS?

    You enable the schema that will be used to EXECUTE these PL/SQL APIs.

    2> Is there any easy way to REST enable all my Tables, Views and all Functions and Procedures inside all my packages to create related REST endpoints quickly? I see there is an option of AutoREST schema, however it appears that I still have to manually REST enable each object to create end points.

    Easiest way would be to write dynamic SQL block to make those calls recursively. However, I would be judicious about what you enable vs what you don't - there's a reason we don't have a 'whole darn schema' button.

    3> From Deployment perspective, Is ORDS expected to be deployed on a separate WebLogic server with no other application on it, or could I deploy it on any existing WebLogic server?

    We don't expect WebLogic at all. Now, if you have one, feel free to use it. You can run ORDS as a standalone java application if you'd like, or throw it into Tomcat or WLS - your choice.

    4> is ORDS right choice here ? or there is any other better way to expose existing Oracle Procedures and Functions inside Oracle Packages as API?

    This is a perfect use case for ORDS. Your application logic is already written (PL/SQL) - and you just need a hook for it from your web apps. If they can make HTTPS calls and deal with JSON, then you're good to go - no drivers to manage, no Oracle Clients to install...

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy
    edited Nov 23, 2021 5:36PM

    I am asked to expose the existing Oracle functions and procedure as APIs, so that these or any other new future application can use them.

    Most development platforms have an ability to execute oracle procedures via OCI, JDBC or others libraries. You don't necessarily need to use ORDS.

  • Pankaj S
    Pankaj S Member Posts: 5 Blue Ribbon

    Thanks User_H3J7U,

    I agree that most tech platform can connect to the Oracle database and execute the Package function or procedures.

    But that means they have to to build and maintain their own connection method to connect to the my data base and understand which package , which function and procedure they have to execute.

    Idea of exposing the Oracle functions business logic as API is to have one point of connectivity to database , and consumer simply calls the API irrespective of their platform.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,056 Employee
    Accepted Answer

    I have below question on using ORDS,

    1> Current functions and procedure that we plan to expose as API are all defined in Reporting schema. And then they have inline PL/SQL that may refer other Financial schema tables or functions.

    So do I have to enable both Reporting and Financial schema for ORDS?

    You enable the schema that will be used to EXECUTE these PL/SQL APIs.

    2> Is there any easy way to REST enable all my Tables, Views and all Functions and Procedures inside all my packages to create related REST endpoints quickly? I see there is an option of AutoREST schema, however it appears that I still have to manually REST enable each object to create end points.

    Easiest way would be to write dynamic SQL block to make those calls recursively. However, I would be judicious about what you enable vs what you don't - there's a reason we don't have a 'whole darn schema' button.

    3> From Deployment perspective, Is ORDS expected to be deployed on a separate WebLogic server with no other application on it, or could I deploy it on any existing WebLogic server?

    We don't expect WebLogic at all. Now, if you have one, feel free to use it. You can run ORDS as a standalone java application if you'd like, or throw it into Tomcat or WLS - your choice.

    4> is ORDS right choice here ? or there is any other better way to expose existing Oracle Procedures and Functions inside Oracle Packages as API?

    This is a perfect use case for ORDS. Your application logic is already written (PL/SQL) - and you just need a hook for it from your web apps. If they can make HTTPS calls and deal with JSON, then you're good to go - no drivers to manage, no Oracle Clients to install...

  • User_H3J7U
    User_H3J7U Member Posts: 632 Silver Trophy

    But that means they have to to build and maintain their own connection method to connect to the my data base

    Nobody reinvents the wheel by implementing low-level HTTP or TNS. The developer takes the library corresponding to the protocol and specifies the connection string: http-url or database-url.

    and understand which package , which function and procedure they have to execute.

    What will change ORDS? In case of REST, it does not imply a list of methods, descriptions of parameters, results and their datatypes.

    consumer simply calls the API irrespective of their platform.

    If consumer is an oracle database, PL/SQL API is simpler to call than REST/SOAP. If the platform is .Net, Java or Javascript, there are no technical difficulties in using one or the other. But REST/SOAP gives significant overhead. While the sql cursor is inherently a stream, converting it to text will require more memory, CPU, and increase latency.