Please change your user handle to recognizable user name. Refer : Video tutorial how to change nickname available
I currently have a working GET handler with a source type of QUERY running in ords 3.0. The call returns many records (hundreds and sometimes 10's of thousands) in JSON and allows the user to page through the results.
The call accepts several parameters that I simply pass into the where clause of the query behind the handler. Data is retrieved by date range(s).
To prevent runaway queries with long date ranges, I've implemented some logic in the sql statement to limit the requester. The requester has asked for an error to be thrown instead. There are other limits I impose on other inputs that I need to provide useful errors for as well.
I'd like to modify the GET call to use PL/SQL type instead of QUERY type so that I can do validations on the inputs and pass back useful error messages if required, however I cannot seem to find any examples online.
Any examples (or alternatives) would be greatly appreciated.
Refer the following blog articles:
- https://jsao.io/2015/07/relational-to-json-with-ords/ (See the blog post section named "Solution 2 – PL/SQL to the Rescue", first he has explained for SQL)
- Oracle APEX Tips: Exposing procedures for URL access with ORDS
1 person found this helpful
You can use Pipelined Functions to generate your plsql recordset, and then just use a select statement frmo the pipelined function in the service definition.
thank you for taking the time to reply and for the tip on changing my handle. Sorry, first time poster.
On your first link, it would be helpful if they provided the ddl for the put_dept_apex_json procedure as that's what I'm looking for. Without that it unfortunately doesnt do much for me.
The second link is a bit closer as they use htp.p to output content however I need to output many rows and columns and be output as JSON. While it looks like it can be done it seems that it will take quite a bit of development.
The third link is essentially the same as the second except they select a value from dual instead of using htp.p.
While the second and third both get me closer I'm looking for an example that is a bit closer to what i'm after - multiple rows and columns returned as json.
Thanks again for your reply - much appreciated.
D.C - I don't know why I didn't think about this approach before. great idea - I'll give it a shot and get back to this thread with the outcome.
Just wanted to followup with my findings after implementing DC's suggestion. Here's a sample I put together using a pipelined function behind a rest data services GET handler so I could do validation, return useful errors, and yet still return the JSON required. The idea is that the function will take an OWNER and return tables owned by that OWNER, however it should throw an error if the owner requested is SYS.
--create record type
CREATE TYPE t_all_tables_row AS OBJECT (
--create table_type of record type
CREATE TYPE t_all_tables_tab IS TABLE OF t_all_tables_row;
--create pipelined function
CREATE OR REPLACE FUNCTION get_tables_pf(Powner IN varchar2) RETURN t_all_tables_tab PIPELINED AS
--dont allow users to query SYS owned objects
if powner='SYS' THEN
FOR i IN (select owner,table_name from all_tables where owner=Powner) LOOP
WHEN excp_bad_user THEN
raise_application_error (-20001,'You cannot use this for SYS owned objects.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
Here's my GET HANDLER using the appropriate SQL that uses the pipelined function. The user is allowed to input an OWNER and will get back tables owned by it.
Here's the response from the rest endpoint when I pass in the OWNER "AMFC". It looks as expected.
Here's what happens when I try to pass in the owner "SYS". I get the user defined error as expected.
Thanks again D.C!