Search for a command to run...
No comments yet. Be the first to comment.
Oracle APEX already includes Font APEX as the default icon library for Universal Theme. In most cases, that is enough. But in one of my APEX apps, I needed a few icons that were not available there, s
Looking to give your Oracle APEX mobile app a modern, native feel? Here’s a quick hack: add a floating action button that stays anchored at the bottom corner just like in popular mobile apps. This sma
APEX 26.1 has been updated to the Universal Theme. While much of the attention in this release is on AI features, I think the UT improvements are just as important for developers like me who care abou
The apex_barcode package offers a PL/SQL API to generate various types of barcodes. You can output barcodes as SVG (Scalable Vector Graphics) values or as PNG file blobs, making it easy to embed them
Oracle APEX makes it easy to get data on screen fast, but often, the default tabular output isn’t enough. What if you want to highlight statuses, add icons, or display badges all without writing compl
APIs play a vital role in making easy communication between applications and databases. Oracle REST Data Services (ORDS) offers a robust solution for creating RESTful web services that work with Oracle databases. A nice feature provided by ORDS is the FilterObject where developers can optimize data retrieval by applying filtering conditions directly to API endpoints. In this blog post, we will explore its usage with some examples.
FilterObject Syntax
The FilterObject syntax follows a simple and intuitive format. It comprises three main components:
Column Name: Specifies the name of the database column on which the filtering will be applied.
Operator: Defines the filtering operation to be performed, such as equals, greater than, less than, etc.
Value: Represents the value used for comparison.
Using these components, developers can construct complex filter expressions to cater to various filtering requirements.
FilterObject in Action
Suppose we have an endpoint to retrieve employee data from the emp table. We can use the FilterObject to fetch employees with a specific job title, salary range, or any other relevant criterion.
To run the examples we are going to see next, we can use the following code and table below.
declare
l_clob clob;
begin
l_clob := apex_web_service.make_rest_request (
p_url => 'https://<your_server>/ords/<workspace>/hr/employees/',
p_http_method => 'GET');
dbms_output.put_line(l_clob);
end;
-- returns employee named KING (= 'KING')
-- implicit
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":"KING"}
-- explicit
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":{"$eq":"KING"}}
Note: This filtering is case-sensitive when used with strings
-- returns all employees but KING (!= 'KING')
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":{"$eq":"KING"}}
-- returns all employees with salary less than 1000 (< 1000)
https://<your_server>/ords/<workspace>/hr/employees/?q={"sal":{"$lt":1000}}
-- returns all employees with salary greater than 1000 (> 1000)
https://<your_server>/ords/<workspace>/hr/employees/?q={"sal":{"$gt":1000}}
-- returns all employees with salary less than 1000 (<= 1000)
https://<your_server>/ords/<workspace>/hr/employees/?q={"sal":{"$lte":1000}}
-- returns all employees with salary greater than 1000 (>= 1000)
https://<your_server>/ords/<workspace>/hr/employees/?q={"sal":{"$gte":1000}}
-- returns employees with the letters KI (instr('KI') >0)
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":{"$instr":"KI"}}
-- returns employees with the letters KI (instr('KI') = 0)
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":{"$ninstr":"KI"}}
-- returns employees with name ending with K (emp like '%K')
https://<your_server>/ords/<workspace>/hr/employees/?q={"ename":{"$like":"%K"}}
-- returns employees with hiredate between 17-MAY-81 and 17-NOV-81 (hiredate between '17-MAY-81' and '17-NOV-81')
https://<your_server>/ords/<workspace>/hr/employees/?q={"HIREDATE":{"$between":["17-MAY-81","17-NOV-81"]}}
-- returns employees with salary lest than 1000 (sal <= 1000)
https://<your_server>/ords/<workspace>/hr/employees/?q={"SAL":{"$between":[null,1000]}}
-- returns employees with no comission (COMM is null)
https://<your_server>/ords/<workspace>/hr/employees/?q={"COMM":{"$null":null}}
-- returns employees with salary equal to 5000 and name starting with K (sal = 5000 and name like 'K%')
https://<your_server>/ords/<workspace>/hr/employees/?q={"SAL":{"\(and":[{"\)eq":5000},{"ename":{"$like":"K%"}}]}}
-- returns employees with salary equal to 5000 or 1500 (sal = 5000 or sal = 1500)
https://<your_server>/ords/<workspace>/hr/employees/?q={"SAL":{"\(or":[{"\)eq":5000},{"sal":{"$eq":1500}}]}}
Advantages of Using FilterObject:
Simplified Data Filtering: Simplifies the complexity of data filtering, making it easy for developers to filter queries without the need for complex SQL.
Reduced Network Load: By allowing clients to specify precisely the data they need, unnecessary data transfer is minimized.
Dynamic Filtering: Including FilterObject in the API request enables clients to dynamically adjust filtering criteria based on their specific requirements.