Forum Stats

  • 3,781,434 Users
  • 2,254,520 Discussions
  • 7,879,694 Comments

Discussions

Categories

how to create API on oracle database

Chandler Bing
Chandler Bing Member Posts: 187 Bronze Badge
in General Database Discussions

Hi Experts,

I want to know is there any way to create API on oracle database 12c without any third party tools.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond

    And API means what to you? How do you define an API?

    Oracle database APIs are designed and developed using PL/SQL. These can be exposed as is (via OCI/Oracle Call Interface), or via XDB Native Web Services and/or REST over HTTPS.

    · Share on TwitterShare on Facebook
  • Chandler Bing
    Chandler Bing Member Posts: 187 Bronze Badge

    Hi @Billy Verreynne

    By API I mean to make changes to the database tables using the API or fetch the stored the records from table and maniplulate the existing ones 

    I have to develop an API and use it in Android platfrom to be able to fetch and save data inside our database

    Can it be done through PL/SQL or purely database services ??

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,360 Silver Crown

    Why create an API for that? REST-enabled SQL is a whole lot easier,

    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.2/aelig/rest-enabled-sql-service.html

    The company I work for has plenty of people who can help with this sort of thing :)

    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond
    edited Jul 14, 2021 8:15AM

    An API is an abstraction layer, hiding the underlying complexities and structures.

    Operating systems have each their own kernel API. The Win32 API is such an API.

    Oracle has the Oracle Call Interface (OCI) API for interacting with the database.

    The best language for developing an API for end-user application to interface with an Oracle database is PL/SQL. Such an API can be a PL/SQL package for creating a shopping cart, add & remove items to & from a shopping cart, checking out a shopping cart, and so on.

    PL/SQL abstracts detailed process flows and expose these as logical business or client actions.

    Likewise PL/SQL can abstract SQL queries by exposing a business data report interface call. Caller for example requests all blue widget products available for immediate shipping, sorted by highest reviews. PL/SQL API constructs the relevant SQL cursor, and returns this as a reference cursor for the client caller app to consume.

    The client can now create its own interface by for example constructing a shopping cart class with methods that calls the PL/SQL shopping cart API.

    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond

    Why create an API for that? REST-enabled SQL is a whole lot easier,

    An API abstracts SQL. So does REST. Which makes REST pretty much a specific type of API. A rose by another name.

    · Share on TwitterShare on Facebook
  • JohnWatson2
    JohnWatson2 Member Posts: 4,360 Silver Crown

    I suppose so, Billy, but it really is so easy: you just embed a SQL statement in your https post. The example in the docs,

    curl -i -X POST --user ORDSTEST:ordstest --data-binary "select sysdate from dual" -H "Content-Type: application/sql" -k https://localhost:8088/ords/ordstest/_/sql

    I would hope that Android comes with the ability to do just that, and to handle the JSON doc returned. I was sceptical about the value of REST and JSON for some time, but now it does look like the way to go for connecting third party products to a database across the net.

    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,648 Red Diamond

    John, it comes down to the type and level of abstraction.

    Your example uses SQL directly. But the complexities of creating, parsing, describing, and consuming a cursor is abstracted.

    This is however just a technical call interface abstraction. It does not abstract the underlying database model, entities, and table structures, or the SQL language.

    I typically prefer to design more abstract interfaces, in order not to require the caller to know how to construct proper SQL cursors for example.

    It is also allows for better security and instrumentation in my view. I also can transparently hide major db structural changes that a SQL API can't.

    · Share on TwitterShare on Facebook