Skip to Main Content
  • Questions
  • How to pass a parameter to a GET Handler in APEX?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alexander.

Asked: December 14, 2020 - 4:27 pm UTC

Last updated: January 13, 2021 - 2:22 pm UTC

Version: 20.2

Viewed 1000+ times

You Asked

Hello,

I created a PL/SQL function that returns a list of open balances as a table result, where all amounts are converted to the currency provided as an input parameter:

function my_pkg.my_func (pi_currency in NUMBER default NULL) return amount_tab pipelined; 


I created an Oracle REST Data Service with only GET handler:

select * from table(my_pkg.my_func(:to_currency)) ;


I tested it by Advanced REST Client and it is working as expected with an additional header for the to_currency parameter. In APEX I declared a REST Data Source related to the above REST service, then I made an APEX page with IG region based on the above REST source and it is working well as long as I am not trying to provide a parameter, i.e. until to_currency is null. When I try to populate {"to_currency":"USD"} in the External Filter attribute, this causes the application crash. I googled the problem but found nothing.

Is any other standard way to pass the non-column parameter to the GET handler in APEX or I should write my own procedure to call REST service, e.g. by using APEX_EXEC?

Thank you and best regards,
Alex

and Connor said...

I couldn't reproduce your error. Here are my steps - hopefully they help you

Defined a basic service to ensure ORDS/REST was ok

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'ASKTOM',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'asktom',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

BEGIN
  ORDS.define_service(
    p_module_name    => 'm1',
    p_base_path      => 'm1/',
    p_pattern        => 't/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM t',
    p_items_per_page => 0);

  COMMIT;
END;
/


tested that with CURL. Then built up a pipelined function that takes a parameter

create or replace 
type asktom.emp_obj as object (
 EMPNO                         NUMBER(4)
,ENAME                                  VARCHAR2(10)
,JOB                                    VARCHAR2(9)
,MGR                                    NUMBER(4)
,HIREDATE                               DATE
,SAL                                    NUMBER(7,2)
,COMM                                   NUMBER(7,2)
,DEPTNO                                 NUMBER(2)
)
/

create or replace
type asktom.emp_row_list as table of asktom.emp_obj
/



create or replace 
function asktom.pipe_demo(p_empno number) return emp_row_list pipelined is
begin
  for i in ( SELECT * FROM t WHERE empno = p_empno or p_empno is null)
  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;
/

select * from asktom.pipe_demo(7369);


and then added to the template to allow access to the pipeline function

BEGIN
  ORDS.define_template(
   p_module_name    => 'm1',
   p_pattern        => 't/:empno');

  ORDS.define_handler(
    p_module_name    => 'm1',
    p_pattern        => 't/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'select * from asktom.pipe_demo( :empno)',
    p_items_per_page => 0);
    
  COMMIT;
END;
/



And checked that to ensure it could be accessed via curl

C:\>curl -i -X GET  http://localapex:8089/ords/asktom/m1/t/7369
HTTP/1.1 200 OK
Date: Wed, 13 Jan 2021 12:46:15 GMT
Content-Type: application/json
ETag: "bxqrFasTUPeM0bb0JjQaaSBEpTVhlTzO63sUolMoCNM4jM11gAj25djNQpBb9zngULKD1BXy0ltOUDMBdW2fkA=="
Transfer-Encoding: chunked

{"items":[{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-16T16:00:00Z","sal":800,"comm":null,"deptno":20}],"hasMore":false,"limit":0,"offset":0,"cou
nt":1,"links":[{"rel":"self","href":"http://localapex:8089/ords/asktom/m1/t/7369"},{"rel":"describedby","href":"http://localapex:8089/ords/asktom/metadata-catalog/m1/t/item"}]}


Then created a Rest Datasource in APEX and made sure it was defined as an ORDS one not simple http

RESTDEMO1

and then using it as an external source with filter works as expected

RESTDEMO2

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mr

A reader, January 15, 2021 - 10:34 am UTC

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/:empno

You 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

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.