Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 473 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
how to create API on oracle database

Hi Experts,
I want to know is there any way to create API on oracle database 12c without any third party tools.
Answers
-
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.
-
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 ??
-
Why create an API for that? REST-enabled SQL is a whole lot easier,
The company I work for has plenty of people who can help with this sort of thing :)
-
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.
-
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.
-
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.
-
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.