Make a Get Request Using Apex

this question is simple.

¿Why i can't use the next code?

select apex_web_service.make_rest_request(
    --p_url => '
    p_url => '
    p_http_method => 'GET',
    p_parm_name   => APEX_UTIL.string_to_table('start_at:end_at'),
    --p_parm_value  => APEX_UTIL.string_to_table('"2018-01-01":"2018-09-01"')
    --p_parm_value  => APEX_UTIL.string_to_table('"2018-01-01"' || ':' || '"2018-09-01"')
    --p_parm_value  => APEX_UTIL.string_to_table('2018-01-01:2018-09-01', ':')
    p_parm_value  => APEX_UTIL.string_to_table('2018-01-01:2018-09-01')
) as test
from dual;

This is the error:

ORA-00902: tipo de dato no válido
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error en la línea: 20, columna: 22

I also try with this form:

declare
  l_clob clob;
begin
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => '
    p_http_method => 'GET',
    --p_parm_name   => APEX_UTIL.string_to_table('"start_at"' || ':' ||'"end_at"'),
    p_parm_name   => APEX_UTIL.string_to_table('start_at:end_at'),
    --p_parm_value  => APEX_UTIL.string_to_table('"2018-01-01"' || ':' || '"2018-09-01"')
    p_parm_value  => APEX_UTIL.string_to_table('2018-01-01' || ':' || '2018-09-01')
    --p_parm_value  => APEX_UTIL.string_to_table(2018-01-01 || ':' || 2018-09-01)
    --p_parm_value  => APEX_UTIL.string_to_table('2018-01-01:2018-09-01')
  ) ;
    dbms_output.put_line(l_clob);
end;
/

and this is the error:

Informe de error -
ORA-06502: PL/SQL: error  numérico o de valor
ORA-06512: en línea 14
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

I want get this json:

I try with postman, and works

There are several comments, are test's but doesn't work.

Does really works p_parm_name and p_parm_value?

Can somebody help me?

Regards

1 Answer

The problem is with your dbms_output.put_line. dbms_output.put_line is limited to a VARCHAR2, or 32,767 bytes. If you use the following code, you'll see that you're getting a result. The size I got was 74,037.

declare
  l_clob clob;
begin
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => '
    p_http_method => 'GET',
    p_parm_name   => APEX_UTIL.string_to_table('start_at:end_at'),
    p_parm_value  => APEX_UTIL.string_to_table('2018-01-01' || ':' || '2018-09-01')
  ) ;

  dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Sarah Jenkins

Sarah Jenkins

Senior Technology Editor & AI Specialist

Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.

Share this article
Twitter Facebook Pinterest