SOAP Calls from Oracle

Hello,

Webservices are becoming a staple in inter-business data exchange.

If you’re running Oracle, the whole ETL process can be integrated in a few short steps.

First, you need to setup the ACL for the service requested.

exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

acl          => ‘companyX.xml’,

description  => ‘Network permissions for CompanyX server’,

principal    => ‘LocalOracleAccount’,

is_grant     => TRUE,

privilege    => ‘connect’);

EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

acl         => ‘companyX.xml’,

host        => ‘companyX.dataservices.com’,

lower_port  => 80);

GRANT XDB_WEBSERVICES to LocalOracleAccount;

GRANT XDB_WEBSERVICES_OVER_HTTP to LocalOracleAccount;

Then you’re ready to fetch the data from the service: Note: You need to know the EndPoint for initial WSDL/WADL. See the v_target_url variable.

DECLARE

extract_clob    CLOB;

v_soap_action     varchar2(255);

v_soap_request  varchar2(32767);

v_soap_response  varchar2(32767);

v_http_req   utl_http.req;

v_http_resp   utl_http.resp;

v_target_url in varchar2 default ‘http://companyx.dataservices.com/DATA/oltpservices.asmx?WSDL’;

v_soap_envelope  varchar2(5000):=’<soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope“

xmlns:oltp=”dataservices/DATA/oltpservices/”>

<soap:Header/>    <soap:Body>

<oltp:DailySalesaggregation>

</soap:Body>

</soap:Envelope>’;

BEGIN

DBMS_LOB.createtemporary(extract_clob, FALSE);

v_soap_request := v_soap_envelope;

http_req:= utl_http.begin_request( v_target_url, ‘POST’, ‘HTTP/1.1′);

utl_http.set_body_charset(v_http_req, ‘UTF-8′);

utl_http.set_header(v_http_req, ‘Content-Type’, ‘text/xml;charset=”UTF-8″‘);

utl_http.set_header(v_http_req, ‘Content-Length’, length(v_soap_request));

utl_http.set_header(v_http_req, ‘SOAPAction’, v_soap_action);

utl_http.write_text(v_http_req, v_soap_request);

v_http_resp:= utl_http.get_response(v_http_req);

LOOP

UTL_HTTP.read_text(v_http_resp, v_soap_response, 32767);

DBMS_LOB.writeappend (extract_clob, LENGTH(v_soap_response), v_soap_response);

END LOOP;

EXCEPTION   WHEN UTL_HTTP.end_of_body THEN

UTL_HTTP.end_response(v_http_resp);

…

At this point you can manipulate the lob to transform and load the extracted data in the database.

 

Pierre

Lost your APEX admin password?

Recently I stumbled on an Apex 4.0 installation containing critical applications to which new users were needed. The problem was that the consultants involved in the development/deployment had left the site. In order to proceed I needed the admin password which was unknown to me.

So I proceeded like this:

• Logged into the database as SYSDBA
• Changed the APEX user’s password, in this occurence the username was: APEX_040000
• Once logged as APEX_040000 I set the security group id to 10:

        exec wwv_flow_api.set_security_group_id(p_security_group_id=>10);

• Created a new ADMIN user to gain access:

        exec wwv_flow_fnd_user_api.create_fnd_user(p_user_name => ‘tempo_admin’, p_web_password => ‘tempo_admin’) ;

• Then went to URL: http://server:port/pls/htmldb/htmldb_admin (if you don’t know the port, look in portlist.ini).
• Entered the temporary admin user and password.
• At this point I was prompted to change the password.
• Once in, I changed the ADMIN user password, created the new users in their respective workspace and dropped the temporary admin user.

 

I hope this will help.

Pierre

Some Oracle 10g Best Practices

• Learn to decipher Awr reports.

• Create index only where needed, drop unneeded indexes.

• Use outlines if code is not modifiable.

• Use RAID-5 for OLTP systems, 85-95% of io’s are reads anyway.

• Create tablespace with extent management local and segment space management auto

• If you cannot use the datapump:
  • TIP #1: Export with buffer=several hundred MB, recordlength=65535, direct=y
  • TIP #2: Import with commit=n, buffer=several hundred MB, recordlength=65535; and a large UNDO.

• Use SGA_TARGET, don’t bother setting up the sga yourself.

• In the Grid Control, set tablespace alert threshold to 92%, the default at 97% is too risky. Better, create a monitoring template.

• Rebuild indexes online regularly; forget about validating the need to since some application indexes might be locked all the time. And as it is time consuming to find which indexes really need a rebuild.
  • TIP: If you can afford, recreate the index instead, it’s better than a rebuild

• On a normal OLTP database, let Oracle gather the stats. Don’t bother.

• Reorganize the tables by using alter table move tablespace ….

• Cache all small static table into a buffer keep pool.

• Scan the db regularly for costly full table scans.

• Forget about the myth of restarting the database weekly to supposedly clean the instance, it flushes your cache and some views might suffer from this. In one instance, the view took 1h30 to re-cache itself.

• If you see queries with lots of outer joins, validate each and every outer joins, some developers don’t take any chances!!! At one client, I saved them 2 cpu by removing 7 outer joins in a table trigger.