In this post, I will describing the steps required to write a merge/upsert query (insert or update) query and expose it as a rest service in Oracle DBCS Schema as a service.
Merge query
Use merge statement to conditionally insert or update data depending on its presence, a process known as upsert. The Merge statement selects data from one or more source tables and updates or inserts into the target table. The merge statement allows you to specify a condition to determine whether to update data from or insert into the target table.
Syntax:
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
In Oracle Schema as a Service
Oracle schema as a service is a database schema which can be accessed only through browser based application called APEX. There are only two ways to perform data exchange with with schema as a service.
Merge query
Use merge statement to conditionally insert or update data depending on its presence, a process known as upsert. The Merge statement selects data from one or more source tables and updates or inserts into the target table. The merge statement allows you to specify a condition to determine whether to update data from or insert into the target table.
Syntax:
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
In Oracle Schema as a Service
Oracle schema as a service is a database schema which can be accessed only through browser based application called APEX. There are only two ways to perform data exchange with with schema as a service.
- Accessing by coding applications (in ADF or JEE) in associated JCS SX (SaaS Extensions) service.
- Exposing queries as RESTful web services.
We will see the second option with merge query,
- Login to you DBCS Schema as a service.
- Navigate to SQL Workshop --> Restful Services
- Click on Create and provide all mandatory fields as per your database object (URI prefix is the base of your URI .
- Click on "Create Template" to create the URI template for your respective object.
- Create Handler for the upsert operation. You can select either "POST" or "PUT" as your method. Since we are talking about upsert (merge) operation it is good practice to select PUT.
- Enter "application/json" in MIME Types Allowed field.
- Enter your PL sql query in source field.
- Merge query requires the source from another table, but as always we have dummy table dual from which we can fetch our input.
- I am giving the example query to merge into department table below,
begin
merge into dept d
using (select :deptNo deptNo, :dName dname,:loc loc from dual) s
on (d.deptNo = s.deptNo)
when matched then update set dname = s.dname, loc=s.loc
when not matched then insert (deptNo, dname, loc)
values (s.deptNo,s.dname, s.loc);
end;
- You can frame the URL of this REST service by appending the URI's that you had entered for module and template to the base URL of your schema as a service instance.
- You can pass the input in the body of the rest service input if json format as mentioned below,
{
"deptNo":"72",
"dName":"Marketing",
"loc":"Texas"
}
The above steps allows you to expose your merge query as rest service and if you want the service to be secure then select "yes" for Requires Secure Access field(which mandates access over ssl) and to attach authentication please follow the steps mentioned in the oracle documentation for adding Restful Service Privileges.
No comments:
Post a Comment