CODEWITHSUNDEEP
jsonoracle-databaseparsingplsqloracle-apex
Apex_MAN
Apex_MAN

Reputation: 127

Error at line 1, col 1: Unexpected character "<" in Apex oracle

when I send a file to another database, I get a response, I parse it with the command apex_json.parse (v_clob_post); and write down the change I need. But there is an error that I cannot resolve:

ORA-20987: Error at line 1, col 1: Unexpected character" <"

How can I solve this?

My code:

set serveroutput on
Declare     
v_clob_post     CLOB;
v_clob          CLOB;
l_body_b        BLOB;
g_EXTERNAL_ID  varchar2(300);   
v_count int;
BEGIN 

  for r in (select id from SEC_OBJECT)

  loop  

    select MYFILE INTO l_body_b from SEC_OBJECT  where id = r.id;
    apex_json.initialize_clob_output;
    apex_json.open_object;
    apex_json.write('snapshot', blob_to_clob_base64(l_body_b)); 
    apex_json.close_object;
    V_clob  := apex_json.get_clob_output;
    apex_json.free_output;
   if   l_body_b is not null then
                    apex_web_service.g_request_headers(1).name := 'Content-Type';  
                    apex_web_service.g_request_headers(1).value := 'application/json';  
                    v_clob_post  := apex_web_service.make_rest_request(p_url => 'myURL',p_http_method => 'POST'
                    ,p_body =>  v_clob );
   if apex_web_service.g_status_code != 200 then
      dbms_output.put_line('id ' || r.id || ' status ' || apex_web_service.g_status_code);
      dbms_output.put_line(v_clob_post);
    else
      apex_json.parse(v_clob_post);     
    end;


 apex_json.parse(v_clob_post);     
 g_EXTERNAL_ID := apex_json.get_varchar2(p_path => 'id_document'); 
 update SEC_OBJECT set EXTERNAL_ID = g_EXTERNAL_ID where id = r.id;
    end if;
end loop;
end;
/

My answer (JSON I’m parsing):

successful: true
id_document: "112118e18"

Upvotes: 0

Views: 3736

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

It appears one of the REST calls you are making results in an error on the server end, and the response you get has an HTML error page instead of JSON data.

To help you identify what you are getting back and which call is causing a problem, add debugging to your code, e.g.:

set serveroutput on

Declare     
...
BEGIN 

for r in (select id from SEC_OBJECT where id >1500)
  loop  
...
    v_clob_post  := apex_web_service.make_rest_request(p_url => 'http://myURL',p_http_method => 'POST'
      ,p_body =>  v_clob );

    dbms_output.put_line('id ' || r.id || ' status ' || apex_web_service.g_status_code);
    dbms_output.put_line(v_clob_post);

    apex_json.parse(v_clob_post);     
...
  end loop;
end;
/

Depending on your client you might need to do something else to see the output; set serveroutput on will work in SQL*Plus, SQLcl and SQL Developer (if run as a script), the latter also has a "View->Dbms Output" menu to open another window, others will have their own equivalents.

You may be able to reduce the output, and narrow the problem, by doing:

    v_clob_post  := ...

    if apex_web_service.g_status_code != 200 then
      dbms_output.put_line('id ' || r.id || ' status ' || apex_web_service.g_status_code);
      dbms_output.put_line(v_clob_post);
    else
      apex_json.parse(v_clob_post);     
    end;

Hopefully the status and response will tell you why it didn't like that particularly v_clob request.

Upvotes: 3

Related Questions