AnsweredAssumed Answered

PL\SQL API calls

Question asked by Holmer Jordan on Apr 10, 2018
Latest reply on Apr 10, 2018 by Holmer Jordan

Hello everyone! I am looking for some much needed assistance integrating Canvas and our Oracle database (11g). I've been following some tutorials online for how to make http requests in PL/SQL but have hit a roadblock. Here is what I have done so far:

 

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'canvas_acl_file.xml',
description => 'ACL from canvas rest api calls',
principal => 'JHS_ATND',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;

begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'canvas_acl_file.xml',
host => 'jayson.instructure.com');
end;

-----------------------------------------------------------------------------------

create or replace
procedure canvas_add_assignment
( p_name in varchar2
) is
req utl_http.req;
res utl_http.resp;
token varchar2(4000) := '<THIS IS MY TOKEN>';
url varchar2(4000) := 'https://jayson.instructure.com/api/v1/courses/2927/assignments';
name varchar2(4000);
buffer varchar2(4000);
content varchar2(4000) := '{"assignment[name]":"'||p_name||'","access_token":"'||token||'"}';
-----------------------------------------------------------------------------------
begin
req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
utl_http.set_header(req, 'content-type', 'application/json');
utl_http.set_header(req, 'Content-Length', length(content));

utl_http.write_text(req, content);
res := utl_http.get_response(req);
begin
loop
utl_http.read_line(res, buffer);
dbms_output.put_line(buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
end canvas_add_assignment;

-----------------------------------------------------------------------------------

begin
canvas_add_assignment('API Assignment');
end;

-----------------------------------------------------------------------------------

 

I then get the error ORA-29024: Certificate validation failure. Any clue what I need to do now? I would love help from someone who has already integrated canvas' API calls into their Oracle database.

 

Thank you!

Outcomes