Hello,
I am getting the infamous/dreaded Internal Server Error when trying to retrieve a binary file from a REST API call.
My environment consists of the following infrastructure components:
Database: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Tomcat : apache-tomcat-7.0.72
ORDS : ords.3.0.7
APEX : 5.0.4
I have defined the service as follows:
DECLARE
lc$module_name VARCHAR2(30) := 'doc_lib';
lc$pattern VARCHAR2(30) := 'get_file/';
lc$method VARCHAR2(30) := 'GET';
BEGIN
ORDS.define_template(
p_module_name => lc$module_name,
p_pattern => lc$pattern);
ORDS.define_handler(
p_module_name => lc$module_name,
p_pattern => lc$pattern,
p_method => lc$method,
p_source_type => ORDS.source_type_media,
p_source => 'select content_body FROM doc_lib where id = :id',
p_items_per_page => 0);
ords.define_parameter(p_module_name => lc$module_name,
p_pattern => lc$pattern,
p_method => lc$method,
p_name => 'ID' ,
p_bind_variable_name => 'id',
p_source_type => 'HEADER',
p_param_type => 'INT',
p_access_method => 'IN');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(SQLERRM);
END;
/
Table defined as follows:
CREATE TABLE DOC_LIB (
id NUMBER ,
title VARCHAR2(1000),
content_type VARCHAR2(1000),
content_body BLOB ,
CONSTRAINT DOC_LIB_PK PRIMARY KEY (ID) ENABLE);
Contents of table (Blob contents not displayed):
ID TITLE CONTENT_TYPE
---------- ------------------------------ ------------------------------
1 alter_table.sql text/plain
2 alter_table.zip application/zip
When I use the following PL/SQL code to test the service I encounter the error.
set serveroutput on;
DECLARE
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'http://x.x.x.x:x/ords/ordstest/doc_lib/get_file/';
response_body varchar2(4000);
lc$id NUMBER := 1;
lc$blob BLOB;
lc$raw RAW(2000);
BEGIN
DBMS_LOB.createtemporary(lc$blob, FALSE);
req := utl_http.begin_request(url, 'GET',' HTTP/1.1');
utl_http.set_header(req, 'content-type', 'application/octet-stream');
utl_http.set_header(req, 'ID', lc$id);
res := UTL_HTTP.get_response(req);
UTL_HTTP.read_text(res, response_body, 32767);
dbms_output.put_line(response_body);
-- Copy the response into the BLOB.
BEGIN
LOOP
UTL_HTTP.read_raw(res, lc$raw, 2000);
DBMS_LOB.writeappend (lc$blob, UTL_RAW.length(lc$raw), lc$raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
UTL_HTTP.end_response(res);
END;
INSERT INTO tmp_blob VALUES (lc$id, lc$blob);
COMMIT;
DBMS_LOB.freetemporary(lc$blob);
dbms_output.put_line('Response> Status Code: ' || res.status_code);
dbms_output.put_line('Response> Reason Phrase: ' || res.reason_phrase);
dbms_output.put_line('Response> HTTP Version: ' || res.http_version);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(sqlerrm);
END;
/
Below is the log entry for Tomcat:
Caused by: java.sql.SQLException: Invalid column type: getString/getNString not implemented for class oracle.jdbc.driver.T4CBlobAccessor
at oracle.jdbc.driver.Accessor.unimpl(Accessor.java:288)
at oracle.jdbc.driver.BlobAccessor.getString(BlobAccessor.java:250)
at oracle.jdbc.driver.GeneratedStatement.getString(GeneratedStatement.java:327)
at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:973)
at oracle.dbtools.rest.resource.generator.media.MediaResourceGenerator.service(MediaResourceGenerator.java:104)
Any clue what is going on?
Thanks in advance,
- Sergio