0 Replies Latest reply: Mar 26, 2008 8:19 PM by 630526 RSS

    PL/SQL http 500 error:

    630526
      Re: SR: Calling Web Services from PL/SQL gives http 500 errors
      Posted: Mar 26, 2008 8:32 AM in response to: Kenzo.Kong Reply


      I am new to web services and am having trouble getting the utl_http.set_header call to work using pl/sql. listed below is the code.
      CREATE OR REPLACE PACKAGE BODY adm_iModules_test
      AS
      /******************************************************************************
      NAME: Admissions iModules testing program
      PURPOSE:

      Ver Date Author Description
      REVISIONS:
      --------- ---------- --------------- ------------------------------------
      1.0 01/04/08 Julie Michael 1. Created this package body.
      ******************************************************************************/
      PROCEDURE adm_iModules_test_output
      IS
      --cursor to select data to populate output file
      CURSOR c_iModules_main
      IS
      SELECT DISTINCT spriden_id sprid_id,
      spriden_pidm sprid_pidm,
      spriden_last_name sprid_last,
      spriden_first_name sprid_first,
      spriden_MI sprid_mid,
      spriden_create_date sprid_create_date,
      spriden_activity_date sprid_activity_date,
      spbpers_pidm pers_pidm,
      spbpers_name_prefix prefix,
      spbpers_name_suffix suffix,
      spbpers_dead_ind deceased_ind,
      spbpers_dead_date deceased_date,
      spbpers_birth_date, --added birth date
      spbpers_sex, --added gender
      goremal_pidm emal_pidm,
      goremal_email_address email_address,
      saradap_pidm app_pidm,
      zzrimod_member_id imod_id,
      gobtpac_external_user user_id
      FROM spriden,
      spbpers,
      goremal,
      saradap,
      zzrimod,
      gobtpac
      WHERE spriden_pidm = spbpers_pidm
      AND spriden_change_ind IS NULL
      AND spriden_pidm = goremal_pidm
      AND goremal_emal_code = 'PERS'
      AND goremal_preferred_ind = 'Y'
      AND spriden_pidm = saradap_pidm
      AND spriden_pidm = 120116
      AND spriden_pidm = gobtpac_pidm
      AND spriden_pidm = zzrimod_pidm(+)
      AND goremal_preferred_ind = 'Y'
      AND saradap_term_code_entry in ('200810','200820','200830');

      v_record VARCHAR2 (32000) := null;
      v_record_counter NUMBER := 0;
      f_iModules_output_test UTL_FILE.file_type;
      f_iModules_filename_test VARCHAR2 (50) := 'Adm_iModules_Test.TXT';
      f_iModules_dir_test VARCHAR2 (100) := 'TMP';
      v_separator VARCHAR2 (50) := ',';
      v_id VARCHAR2 (90) := '';
      v_fname VARCHAR2 (40) := '';
      v_lname VARCHAR2 (40) := '';
      v_user VARCHAR2 (90) := '';
      v_login VARCHAR2 (100) := '';
      v_password VARCHAR2 (100) := '';
      -- v_link VARCHAR2 (32767) := 'http://imodules.com/Web%20Services/';
      --the below item 'http://admin.imodules.com - was https://admin.imodules.com
      v_link VARCHAR2 (32767) := 'http://admin.imodules.com/ws_10/MemberQuery.asmx?WSDL';
      v_non_existing_ids VARCHAR2 (100) := '"false"';
      v_style VARCHAR2 (5) := '"1"';
      v_imod_member_id VARCHAR2 (100) := '';
      v_spriden_first_name VARCHAR2 (60) := '';
      req Utl_Http.req; --for posting
      resp Utl_Http.resp; --for posting
      v_msg VARCHAR2(80); --for posting
      H_Name VARCHAR2(255); --for posting
      H_Value VARCHAR2(1023); --for posting
      v_data_xml VARCHAR2(10000);


      BEGIN
      DBMS_OUTPUT.ENABLE(1000000);
      f_iModules_output_test := UTL_FILE.fopen (f_iModules_dir_test, f_iModules_filename_test, 'w',32767);
      v_record := '';
      --needed for posting

      -- Utl_http.set_proxy(proxy => 'xxx.yyy.com',
      -- no_proxy_domains => 'xxx.com');

      req := Utl_Http.begin_request(url => v_link,
      method => 'POST');


      -- FOR v_iModules2 IN c_iModules_main
      -- LOOP
      -- v_record_counter := v_record_counter + 1;
      -- END LOOP;

      -- v_record := 'DUQ'||','||v_record_counter||','||to_char(SYSDATE,'mm/dd/yyyy');
      UTL_FILE.put_line (f_iModules_output_test, v_record, false);
      v_record := null;
      FOR v_iModules IN c_iModules_main
      LOOP
      v_id := v_imodules.sprid_id;
      v_fname := v_imodules.sprid_first||'test';
      v_lname := v_imodules.sprid_last;
      v_user := '"'||v_imodules.email_address||'"';
      v_imodules.imod_id := '29';
      v_imod_member_id := '"'||v_imodules.imod_id||'"';
      v_spriden_first_name := '"SPRIDEN_FIRST_NAME"';

      v_login := '"Dxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx"';
      v_password := '"Xxxxxxxxxxxxxxxxxx"';

      v_record :='<?xml version="1.0" encoding="utf-8"?>'||
      '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'||' '||
      'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">'||
      '<soap:Body>'||'<Update xmlns:m='||v_link||'>'||'<AUTHENTICATION login='||v_login||' '||
      'password='||v_password||' '||'addNonExistingIDs='||v_non_existing_ids||' '||'style='||
      v_style||'/>'||'<MemberInformation ZZRIMOD_MEMBER_ID='||v_imod_member_id||'>'||
      '<SPRIDEN_FIRST_NAME>'||v_fname||'</SPRIDEN_FIRST_NAME>'||'</MemberInformation>'||'</Update>'||
      '</soap:Body></soap:Envelope>';

      -- v_record := v_record := v_record||'<Update xmlns:m='||v_link||'>'||'<AUTHENTICATION login='||v_user||'>'||' '||
      -- 'password='||v_password||' '||'addNonExistingIDs='||v_non_existing_ids||' '||'style='||
      -- v_style||'/>'||'<MemberInformation ZZRIMOD_MEMBER_ID='||v_imod_member_id||' '||
      -- 'column='||'"SPRIDEN_FIRST_NAME"'||' '||'newValue='||v_fname||'/>'||'</Update>';

      dbms_output.put_line('code: '||v_record);

      --v_record := v_record||v_separator||v_id||v_separator||v_name||v_separator||v_email;

      --Http posting calls
      Utl_Http.set_header(r => req,
      name => 'Content-Type',
      value => 'text/xml');

      Utl_Http.set_header(r => req,
      name => 'Content-Length',
      value => to_char(length(v_record)) );

      --JAM 03/25/08 - added to accomodate the missing soap action error
      Utl_Http.set_header(r => req,
      name => 'SOAPAction',
      value => 'http://admin.imodules.com/ws_10/MemberQuery.asmx?WSDL');

      Utl_Http.set_header(r => req,
      name => 'MessageType',
      value => 'CALL');

      -- Utl_Http.set_authentication(r => req,
      -- username => 'zzz',
      -- password => 'ppppp',
      -- scheme => 'Basic',
      -- for_proxy => FALSE);

      Utl_Http.write_text(r => req,
      data => v_record);

      resp := Utl_Http.get_response(r => req,
      return_info_response => TRUE);

      DBMS_OUTPUT.put_line('Status Code: ' || resp.status_code);
      DBMS_OUTPUT.put_line('Reason Phrase: ' || resp.reason_phrase);
      dbms_output.put_line('testing');

      FOR i IN 1 .. Utl_Http.get_header_count(r => resp)
      LOOP
      Utl_Http.get_header(r => resp,
      n => i,
      name => H_Name,
      value => H_Value);

      --DBMS_OUTPUT.put_line( ... );
      END LOOP;

      BEGIN
      LOOP
      Utl_Http.read_text(r => resp, DATA => v_msg);
      DBMS_OUTPUT.put_line(v_msg);
      END LOOP;

      EXCEPTION
      WHEN Utl_Http.end_of_body THEN
      NULL;
      END;

      Utl_Http.end_response(r => resp);

      -- EXCEPTION
      -- WHEN Utl_Http.request_failed THEN
      -- DBMS_OUTPUT.put_line('Request failed: '||Utl_Http.Get_Detailed_Sqlerrm);
      --
      -- WHEN Utl_Http.http_server_error THEN
      -- DBMS_OUTPUT.put_line('Http_Server_Error: '||Utl_Http.Get_Detailed_Sqlerrm);
      --
      -- WHEN Utl_Http.http_client_error THEN
      -- DBMS_OUTPUT.put_line('Client Error: '||Utl_Http.Get_Detailed_Sqlerrm);
      --
      -- WHEN others THEN
      -- DBMS_OUTPUT.put_line('Others2: '||Utl_Http.Get_Detailed_Sqlerrm);
      -- Utl_Http.end_response(r => resp);
      -- END;

      --End of Htp posting calls



      --UTL_FILE.put_line (f_iModules_output_test, v_record, false);
      --htp.p('<post>'||v_record||'</post>');


      END LOOP;

      EXCEPTION
      WHEN Utl_Http.request_failed THEN
      DBMS_OUTPUT.put_line('Request failed: '||Utl_Http.Get_Detailed_Sqlerrm);

      WHEN Utl_Http.http_server_error THEN
      DBMS_OUTPUT.put_line('Http_Server_Error: '||Utl_Http.Get_Detailed_Sqlerrm);

      WHEN Utl_Http.http_client_error THEN
      DBMS_OUTPUT.put_line('Client Error: '||Utl_Http.Get_Detailed_Sqlerrm);

      WHEN others THEN
      DBMS_OUTPUT.put_line('Others2: '||Utl_Http.Get_Detailed_Sqlerrm);
      Utl_Http.end_response(r => resp);
      END;

      -- close output file
      --UTL_FILE.fclose (f_iModules_output_test);
      --COMMIT;
      /*EXCEPTION
      WHEN OTHERS
      THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line ('Other Exception:' || SQLERRM);
      -- DBMS_OUTPUT.put_line ('Other Exception:' || Utl_Http.Get_Detailed_Sqlerrm);
      --COMMIT; */
      -- END adm_iModules_test_output;
      END adm_iModules_test;
      /

      Message was edited by:
      user627523