Dear ASK TOM,
Thank you very much for the valuable tutorial. I did everything step by step and it seems to work perfectly, but it is not. And this helped me a lot to identify the real problem.
Your data source is based on the first endpoint /m1/t, not the /m1/t/:empnoYou do not need the function and the second endpoint (/m1/t/:empno) to create just the same data source. You can also delete the second endpoint now, and the data source will continue working in the same way. If you keep the second but delete the first endpoint, then the data source stops working at all.
Since I didn’t realize the above, I tried to pass a parameter {“currency”:”USD”}, and then I received the error. And now it is clear why.
Now let’s make some changes to be closer to my case: “to pass a non-column parameter”1. Change the function
create or replace
function pipe_demo(p_currency varchar2) return emp_row_list pipelined is
begin
for i in (
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL * decode(p_currency, null, 1, 'USD', 1, 'EUR', 0.82) as SAL
,COMM * decode(p_currency, null, 1, 'USD', 1, 'EUR', 0.82) as COMM
,DEPTNO
FROM emp
)
loop
pipe row ( emp_obj(
i.EMPNO
,i.ENAME
,i.JOB
,i.MGR
,i.HIREDATE
,i.SAL
,i.COMM
,i.DEPTNO
) );
end loop;
return;
end;
2. Test the function
select * from pipe_demo('USD');
select * from pipe_demo('EUR');
select * from pipe_demo(null);
3. Be sure that all old REST services are deleted and create the new one
BEGIN
ORDS.define_template(
p_module_name => 'm1',
p_pattern => 't/:currency');
ORDS.define_handler(
p_module_name => 'm1',
p_pattern => 't/:currency',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'select * from pipe_demo( :currency)',
p_items_per_page => 0);
COMMIT;
END;
4. Test the REST service - I tested with chrome:
http://.../m1/t/USD
http://.../m1/t/EUR
5. Delete the previous and create a new REST Data Source
It is a little bit tricky here – in order to see the last two fields, you should press Tab instead of Next, or you may press Next and then Back to see them.
If the field Value left empty, then on the Discovery step we have an error. Now it is clear that APEX tries to find an endpoint without a parameter.
If you populate the filed Value with USD or EUR or *, then the Discovery step and creation of the data source are performed successfully.
Now APEX is using the right endpoint, but I cannot find a way to override the parameter value. Any ideas?
Thank you again,
Alex