Wednesday, August 31, 2011

PL/SQL + BI Publisher + Customer Calendar

by Husam Khalaf
I had a requirement to automate running a set of BI Publisher reports using the corporate fiscal calendar. The problem with BI Publisher scheduler is that is uses the normal calendar and there is no way to integrate a custom calendar instead. So I had to choose between two options to solve this problem:

1 - Utilize BI Publisher's Web Services API using Java code.

2 - Utilize BI Publisher's Web Services API using PL/SQL code.

The first option was more popular, I could google it and I found some examples that I could start with. The problem is that I am not a big fan of Java, and last time I've done coding in Java was a few years ago. On the other hand, I love PL/SQL, and I've done web services calls using custom PL/SQL before, such as integrating to CRM OnDemand and some Online Payment Gateway, but the problem was that I've never done that with BI Publisher. So I had to google this option first and unfortunately I could find almost nothing regarding this, so I had to start almost from scratch. I found two documents that were helpful to some extent:Long story short, I was able to accomplish this goal using PL/SQL, and I thought it may be a nice idea to share my experience if someone comes across a similar requirement. Here is a summary of what I've done:
  • I created a variables table to store some parameters that may be different in different environments (Development, Testing and Production):
BIP_WS_CONFIG
ATTRVAL
NSxmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="xmlns=http://1.2.3.4:9999/oxp/service/PublicReportService
REP_ABS_PATH/National Sales/BIP Reports/DPI/
WSDL_URLhttp://1.2.3.4:9704/xmlpserver/services/PublicReportService
USERNAMEbip_service_acct
PWDD486ACCFD
BIP_SERVER1.2.3.4
BIP_PORT9999

Where 9999 can be replace by your BI Publisher port#, and 1.2.3.4 can be replaced by you BI Publisher server. I am also encrypting the BI Publisher service account password using some custom encryption function that I won't demonstrate here.
  • I created a utility package to handle web service calls in general, I tried not to make it specific to BI Publisher web services for reusability.
create or replace
PACKAGE pkg_webservice_utl 
AS
  g_app_name VARCHAR2(50);
  FUNCTION make_request(
      p_appl             IN VARCHAR2,
      p_url              IN VARCHAR2,
      p_action           IN VARCHAR2 default 'SOAPAction',
      p_version          IN VARCHAR2 default '1.1',
      p_envelope         IN CLOB,
      p_proxy_override   IN VARCHAR2 default null,
      p_wallet_path      IN VARCHAR2 default null,
      p_wallet_pwd       IN VARCHAR2 default null) RETURN XMLTYPE;
  FUNCTION parse_xml   (
      p_appl             IN VARCHAR2,
      p_xml              IN XMLTYPE,
      p_xpath            IN VARCHAR2,
      p_ns               IN VARCHAR2 default null) RETURN VARCHAR2;
  FUNCTION clob_to_varchar2( p_clob_in  CLOB) RETURN VARCHAR2;
  FUNCTION encrypt( P_STR VARCHAR2 ) RETURN RAW;
  FUNCTION decrypt( P_XCRYPT VARCHAR2 ) RETURN VARCHAR2;
END pkg_webservice_utl;
/
show errors 

create or replace
PACKAGE BODY PKG_WEBSERVICE_UTL AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Package WEB_SERVICE_UTL
-- This package provides functions that can be used to
-- invoke web services
-- Example: Invoke web service call to run and schedule BI
-- Publisher reports
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
 
  FUNCTION clob_to_varchar2 (p_clob_in CLOB) RETURN VARCHAR2 
  AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function CLOB_TO_VARCHAR2
-- Purpose:
-- This function coverts a clob to varchar2
-- Returns: The passed clob in varchar2 format
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
    v_strt        INTEGER := 1;
    v_chunk_size  INTEGER := 4000;
    v_return      VARCHAR2(32767) := NULL;
    v_err_return  NUMBER;
  BEGIN
    IF DBMS_LOB.getlength ( p_clob_in ) > 32767 THEN
      RETURN NULL;
    END IF;
    -- Parse the CLOB
    WHILE LENGTH (NVL(v_return,0)) <> DBMS_LOB.getlength ( p_clob_in )
    LOOP
     v_return := v_return || DBMS_LOB.SUBSTR ( p_clob_in,
                                               v_chunk_size,
                                             ( v_chunk_size * ( v_strt - 1 ) ) + 1 );
     v_strt := v_strt + 1;
    END LOOP;

    RETURN v_return;
  EXCEPTION 
    WHEN OTHERS THEN
    --log the error in some error table        
    return null; 
  END clob_to_varchar2;

  FUNCTION make_request (
    p_appl              IN VARCHAR2,
    p_url               IN VARCHAR2,
    p_action            IN VARCHAR2 DEFAULT 'SOAPAction',
    p_version           IN VARCHAR2 DEFAULT '1.1',
    p_envelope          IN CLOB, 
    p_proxy_override    IN VARCHAR2 DEFAULT NULL,
    p_wallet_path       IN VARCHAR2 DEFAULT NULL,
    p_wallet_pwd        IN VARCHAR2 DEFAULT NULL ) RETURN XMLTYPE 
  AS
-----------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function MAKE_REQUEST
-- Purpose:
-- This function submits a web service call in HTTP request
-- and utilizes the oracle package to construct HTTP
-- requests and read HTTP responses
-- Returns: The HTTP response (SOAP) in XML format
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
    TYPE HEADER IS RECORD (NAME VARCHAR2(256), VALUE VARCHAR2(1024));
    TYPE header_table IS TABLE OF HEADER INDEX BY BINARY_INTEGER;
    v_request_cookies   utl_http.cookie_table;
    v_response_cookies  utl_http.cookie_table;
    v_http_req          utl_http.req;
    v_http_resp         utl_http.resp;
    v_hdrs              header_table;   
    v_request_headers   header_table;
    v_hdr               HEADER;  
    v_clob              CLOB;   
    v_raw_data          RAW(512);     
    v_response          VARCHAR2(2000);
    v_name              VARCHAR2(256);
    v_hdr_value         VARCHAR2(1024); 
    v_line              VARCHAR2(1000);
    v_status_code       PLS_INTEGER;
    v_env_len           INTEGER := 0;
    v_err_return        NUMBER;
  BEGIN
    g_app_name := p_appl;
    v_env_len := v_env_len + lengthb(clob_to_varchar2(p_envelope));
    dbms_output.put_line('v_env_lenb= '||v_env_len);
    dbms_output.put_line('Setting proxy');
    utl_http.set_proxy (proxy => p_proxy_override);
    dbms_output.put_line('Setting timeout');
    utl_http.set_persistent_conn_support(true);
    utl_http.set_transfer_timeout(180);  -- 180 seconds

    -- set wallet if needed
    IF instr(lower(p_url),'https') = 1 THEN
      utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
    END IF;

    -- set cookies if necessary
    IF V_request_cookies.count > 0 THEN
      utl_http.clear_cookies;
      utl_http.add_cookies(v_request_cookies);
    END IF;

    dbms_output.put_line('Begining HTTP request');
    v_http_req := utl_http.begin_request(p_url, 'POST');

    -- set standard HTTP headers for a SOAP request
    dbms_output.put_line('Setting HTTP request headers'); 
    utl_http.set_header(v_http_req, 'Proxy-Connection', 'Keep-Alive');

    IF p_version = '1.2' THEN
      utl_http.set_header(v_http_req, 'Content-Type', 'application/soap+xml; charset=UTF-8; action="'||p_action||'";');
    ELSE
      utl_http.set_header(v_http_req, 'SOAPAction', p_action);
      utl_http.set_header(v_http_req, 'Content-Type', 'text/xml; charset=UTF-8');
    END IF;

    dbms_output.put_line('Setting header length');
    utl_http.set_header(v_http_req, 'Content-Length', v_env_len);
    dbms_output.put_line('Setting headers from v_request_headers');

    --set headers from v_request_headers
    FOR i in 1.. v_request_headers.count LOOP
      utl_http.set_header(v_http_req, v_request_headers(i).name, v_request_headers(i).value);
    END LOOP;

    dbms_output.put_line('Reading the envelope and write it to the HTTP request');

    -- read the envelope, convert to UTF8 if necessary, then write it to the HTTP request
    utl_http.write_text(v_http_req, clob_to_varchar2(p_envelope));

    -- get the response
    dbms_output.put_line('getting the response');

    v_http_resp := utl_http.get_response(v_http_req);
    dbms_output.put_line('Response status_code: '   ||v_http_resp.status_code);   
    dbms_output.put_line('Response reason_phrase: ' ||v_http_resp.reason_phrase);  
    dbms_output.put_line('Response http_version: '  ||v_http_resp.http_version); 

    -- set response code, response http header and response cookies global
    v_status_code := v_http_resp.status_code;
    utl_http.get_cookies(v_response_cookies);

    FOR i in 1..utl_http.get_header_count(v_http_resp) LOOP
      utl_http.get_header(v_http_resp, i, v_name, v_hdr_value);
      v_hdr.name  := v_name;
      v_hdr.value := v_hdr_value;
      v_hdrs(i)   := v_hdr;
    END LOOP;

    v_request_headers := v_hdrs;
    dbms_output.put_line('converting the HTTP response');

    BEGIN <>
      LOOP UTL_HTTP.read_raw(v_http_resp, v_raw_data, 512);
        v_clob := v_clob || UTL_RAW.cast_to_varchar2(v_raw_data);
      END LOOP response_loop;

    EXCEPTION 
      WHEN UTL_HTTP.end_of_body THEN    
        dbms_output.put_line('End of body in response loop');
        UTL_HTTP.end_response(v_http_resp);   
      WHEN OTHERS THEN
        dbms_output.put_line('Unkown error in response loop:'||sqlerrm);
        return null;
    END;

    dbms_output.put_line('Response length: '||LENGTH(v_clob) );   
    dbms_output.put_line('HTTP response:'); 

    FOR i in 0..CEIL(LENGTH(v_clob) / 512)-1 LOOP
      v_line := SUBSTR(v_clob, i * 512 + 1, 512);
      dbms_output.put_line('[' || LPAD(i, 2, '0') || ']: ' || v_line);
      EXIT WHEN i > 50 - 1;   
    END LOOP;

    dbms_output.put_line('Closing HTTP request and response');

    IF v_http_req.private_hndl IS NOT NULL THEN      
      UTL_HTTP.end_request(v_http_req);   
    END IF;     

    IF v_http_resp.private_hndl IS NOT NULL THEN
      UTL_HTTP.end_response(v_http_resp);   
    END IF;

    dbms_output.put_line('Converting response text to XML');
    return xmltype.createxml(v_clob);
  EXCEPTION 
    WHEN OTHERS THEN
      --log the error in some error table
      return null;
  END make_request;

  FUNCTION parse_xml 
    ( p_appl  IN VARCHAR2,
      p_xml   IN XMLTYPE,
      p_xpath IN VARCHAR2,
      p_ns    IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2 
  AS
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
-- Function parse_xml
-- Purpose:
-- This function reads SOAP response content in XML format
-- and parses it to
-- extract certain response values
-- Returns: A variable of varchar2 data type
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
    v_response          VARCHAR2(32767);
    v_err_return        NUMBER;
  BEGIN
    g_app_name := p_appl;
    dbms_output.put_line('Parsing result from SOAP response XML');
    v_response := dbms_xmlgen.convert(p_xml.extract(p_xpath,p_ns).getstringval(),1);
    dbms_output.put_line(v_response);
    return v_response;
  EXCEPTION 
    WHEN OTHERS THEN
      --log the error in some error table
      return null;
  END parse_xml;
END PKG_WEBSERVICE_UTL;
/

show errors
I created a function that uses this utility package to schedule a BI Publisher report:
create or replace
FUNCTION fn_schedule_report 
  ( P_REPORT_NM VARCHAR2 , 
    P_FORMAT VARCHAR2, P
    _BURST NUMBER DEFAULT 1 ) RETURN NUMBER 
IS
-------------------------------------------------------------------------------
-----------------------------***************-----------------------------------
-------------------------------------------------------------------------------
-- Function FN_SCHEDULE_REPORT
-- Purpose:
-- This function utilizes the WEB_SERVICE_PKG to schedule / run BIP Publisher
-- reports through web service calls
-- Returns: Job ID if sucess , 0 if failure
-------------------------------------------------------------------------------
-----------------------------***************-----------------------------------
-------------------------------------------------------------------------------
  v_response        VARCHAR2(32767);
  v_ns              VARCHAR2(4000);
  v_url             VARCHAR2(500);
  v_job_name        VARCHAR2(500);
  v_report_name     VARCHAR2(500);
  v_report_abs_path VARCHAR2(500);
  v_report_rel_path VARCHAR2(500);
  v_bip_server      VARCHAR2(500);
  v_username        VARCHAR2(50);
  v_password        VARCHAR2(50);
  v_seq             NUMBER;
  v_soap_env        CLOB;
  v_xml             XMLTYPE;
  v_burst           NUMBER; 
  v_err_return      NUMBER;
  v_port            NUMBER;
BEGIN
  -- get web service paramerters from the BIP_WS_CONFIG variables table
  select trim(val)
  INTO   v_ns
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'NS';

  select trim(val)
  into   v_report_rel_path
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'REP_ABS_PATH';

  select trim(val)
  into   v_url
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'WSDL_URL';

  select trim(val)
  into   v_username
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'USERNAME';

  SELECT trim(val)
  into   v_bip_server
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'BIP_SERVER';

  SELECT trim(val)
  into   v_port
  FROM   BIP_WS_CONFIG
  WHERE  upper(attr) = 'BIP_PORT';

  select pkg_webservice_utl.decrypt(trim(val)) val
  into   v_password
  FROM   BIP_WS_CONFIG
  where  upper(attr) = 'PWD';

  IF p_burst = 1 THEN
    v_burst := 1;
  ELSE
    v_burst := 0;
  END IF;

  -- set report name
  v_report_name:= p_report_nm;

  -- generate a new JOB id
  select bip_job_id.nextval
  into   v_seq
  from   dual;

  v_job_name    := substr(v_report_name,instr(v_report_name,'/')+1 )||' #'||v_seq;

  v_report_abs_path := v_report_rel_path||v_report_name||'/'||v_report_name||'.xdo';
  dbms_output.put_line('absolute path:'|| v_report_abs_path);


  v_soap_env := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
     <soapenv:Body>
        <pub:scheduleReport xmlns:pub="xmlns=http://'||v_bip_server||':'||v_port||'/oxp/service/PublicReportService">
           <scheduleRequest>
              <deliveryRequest>
              </deliveryRequest>
              <reportRequest>
                 <attributeFormat>'||lower(p_format)||'
                 <reportAbsolutePath>'||v_report_abs_path||'
              </reportRequest>
              <userJobName>'||v_job_name||'
              <scheduleBurstringOption>'||v_burst||'
           </scheduleRequest>
           <userID>'||v_username||'
           <password>'||v_password||'
        </pub:scheduleReport>
     </soapenv:Body>
  </soapenv:Envelope>';

  dbms_output.put_line('calling make_request function');

  v_xml := pkg_webservice_utl.make_request
              ( p_appl => P_APP,
                p_url  => v_url,
                p_envelope => v_soap_env );

  v_response := pkg_webservice_utl.parse_xml(p_app, v_xml,'//scheduleReportReturn/text()',v_ns);

  -- v_response is expected to be a numeric value "job id" if the report is
  -- successfully scheduled, check if value is numeric
  IF REGEXP_LIKE (v_response, '^[0-9]*$') THEN
    dbms_output.put_line('Job ID:'||v_response||' submitted successfully');
    return v_response;
  ELSE
    dbms_output.put_line('Report Schedule Request Failed');
    return 0;
  END IF;

EXCEPTION 
  WHEN OTHERS THEN
    -- log error into some error log
    dbms_output.put_line(sqlerrm);
    return 0;
END fn_schedule_report;
  • Finally, I wanted to get the status of the scheduled report. This is a little bit tricky because I could not find a BI Publisher web service operation that returns this information. The only 3 operations that I thought may help were:
    • -getScheduledReportStatus and getScheduledReportInfo operations: Only return info about a job that is still in the scheduler. Once the scheduled report is kicked off, it will be removed from the 'scheduler', so null will be returned.
    • -getScheduledReportHistoryInfo: Always returned null, I could not figure why is that, but it may be for the same reason above.
So after doing some research, I figured that there are two BI Publisher tables that can be utilized to obtain a scheduled report status:
  • XMLP_SCHED_JOB: maintains information about scheduled jobs. Once a report is scheduled an record will be inserted into this table along with information about that job.
  • XMLP_SCHED_OUTPUT: maintains information about running jobs (reports) or completed ( with success or failure) jobs. Once a report is kicked off a record will be inserted into this table along with information about that job. This is the table that we need.
So I wrote a function to get the status of an execute report as follows:
create or replace
FUNCTION FN_GET_REPORT_STAT( P_APP VARCHAR2, P_JOB_ID NUMBER ) RETURN NUMBER 
IS
------------------------------------------------------------
-----------------------------***************----------------
--------------------------------------------------------------  Purpose:
-- This function checks that status of a report by Job ID
-- Returns: 0 if sucess 'S', 1 if failed 'F', 2 if pending 'C'
------------------------------------------------------------
-----------------------------***************----------------
------------------------------------------------------------
  v_stat       CHAR(1) := 'C';
  v_timeout    NUMBER;
  v_duration   NUMBER;
  v_err_return NUMBER;
BEGIN
  IF p_job_id = 0 THEN --invalid job id
    return 1;
  ELSE
    SELECT status
    INTO   v_stat
    FROM   xmlp_sched_output
    WHERE  job_id = p_job_id;
  END IF;
 
  IF v_stat = 'S' THEN  -- success
    dbms_output.put_line('Report job# '||p_job_id||' finished successfully');
    return 0;
  ELSIF v_stat = 'C' THEN   -- Pending
    dbms_output.put_line('Report job# '||p_job_id||' is pending');
    return 2;
  ELSE  -- Failure or others like deleted, suspended..etc
    dbms_output.put_line('Report job# '||p_job_id||' failed');
    return 1;
  END IF;

  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
     --Job is not started yet, so a record won't exist yet in xmlp_sched_output
     dbms_output.put_line('Job# '||p_job_id||' is not started yet and  record  does not exist yet in xmlp_sched_output');
     return 2;
    WHEN OTHERS THEN
    --log error in some error table
      return 1;
END FN_GET_REPORT_STAT;
/
show errors
Putting it all together:
I created a Unix shell script that does the following:

- Call
FN_SCHEDULE_REPROT( P_REPORT_NM => 'Report name',

P_FORMAT => 'Pdf',
P_BURST => 1 )
- Pass the retuned value to function FN_GET_REPORT_STAT

- If returned status from the FN_GET_REPORT_STAT function is 0 then return 'success'

- If returned status from the FN_GET_REPORT_STATfunction is 1 then return 'fail'

- If returned status from the FN_GET_REPORT_STAT function is 2 then loop every X minutes up to Y minutes (using the Unix sleep function) while status = 2 and check for status again as above ..etc

Tuesday, August 30, 2011

OBIEE 11g Performance with Google Page Speed

A few weeks ago I tried out YSlow on OBIEE 11g.

I finally managed to find some time to mess around with Oracle Web Tier (HTTP and WebCache).

The results:



I went from a D to a C. Not terrible.

This time, I also used Google Page Speed.



41 out of 100 using just the application server (WLS).

Now adding in the Oracle Web Tier components, Oracle HTTP Server and Oracle Web Cache.



76 out of 100. Much better.

OBIEE 11g is built on Oracle WebLogic Server. It was not intuitive, to me anyway, that everything was being served via an application server. Therefore, there is no caching of static files or compression.

Putting a web server in front of your application server is a very easy way to increase page load times.

I would assume (stop laughing) that any web server would do the exact same thing as the Oracle Web Tier components...but, you know me.

Wednesday, August 24, 2011

Monday, August 22, 2011

Fun with...

I don't even know how to google this one.

Today, I tried to log in to a client site. It's not a VPN, like the Cisco VPN; it's the RSA SecurID variety (if that makes a difference).

Basically, I log in with my network credentials and then enter a passcode which is generated from a pin I enter.

Friday was the last time I successfully logged into their system. Today, I was unable to get to the page.

tracert (Windows) and traceroute (linux) didn't do much for me.
  1     1 ms    <1 ms    <1 ms  Wireless_Broadband_Router.home [192.168.1.1]
  2     7 ms     6 ms     5 ms  L100.TAMPFL-VFTTP-73.verizon-gni.net [173.65.30.1]
  3    24 ms    27 ms    24 ms  G0-3-3-3.TAMPFL-LCR-22.verizon-gni.net [***.***.***.***]
  4     4 ms     7 ms     4 ms  so-2-0-0-0.TPA01-BB-RTR2.verizon-gni.net [130.81.28.214]
  5    11 ms     9 ms    10 ms  0.ge-3-2-0.XL4.MIA4.ALTER.NET [152.63.1.153]
  6    78 ms    70 ms    69 ms  0.ge-4-1-0.XT2.DEN4.ALTER.NET [152.63.114.201]
  7    70 ms    69 ms    71 ms  POS7-0-1.GW10.DEN4.ALTER.NET [152.63.89.213]
  8    68 ms    69 ms    66 ms  internap-gw.customer.alter.net [152.179.104.90]
  9    67 ms    67 ms    68 ms  mpr2.den.ve3-bbnet2.pnap.net [216.52.40.72]
 10    72 ms    89 ms    70 ms  allmar-4.mpr2.den.pnap.net [66.151.163.98]
 11     *        *        *     Request timed out.
 12     *        *        *     Request timed out.
 13     *        *        *     Request timed out.
 14     *        *        *     Request timed out.
 15     *        *        *     Request timed out.
 16     *        *        *     Request timed out.
 17     *        *        *     Request timed out.
 18     *        *        *     Request timed out.
 19     *        *        *     Request timed out.
 20     *        *        *     Request timed out.
 21     *        *        *     Request timed out.
 22     *        *        *     Request timed out.
 23     *        *        *     Request timed out.
 24     *        *        *     Request timed out.
 25     *        *        *     Request timed out.
 26     *        *        *     Request timed out.
 27     *        *        *     Request timed out.
 28     *        *        *     Request timed out.
 29     *        *        *     Request timed out.
 30     *        *        *     Request timed out.
I have no idea what that means, but I seriously doubt that if it was working correctly I would see the request time outs.

Interestingly, if I just use the host, say ssl.oraclenerd.com, I get this:
  1    <1 ms    <1 ms    <1 ms  Wireless_Broadband_Router.home [192.168.1.1]
  2     3 ms     4 ms     6 ms  L100.TAMPFL-VFTTP-73.verizon-gni.net [***.***.***.***]
  3     4 ms     4 ms     4 ms  G0-3-3-4.TAMPFL-LCR-22.verizon-gni.net [130.81.110.222]
  4     4 ms     4 ms     4 ms  so-2-0-0-0.TPA01-BB-RTR2.verizon-gni.net [130.81.28.214]
  5    11 ms     9 ms     9 ms  0.ge-3-2-0.XL4.MIA4.ALTER.NET [152.63.1.153]
  6     *        *        *     Request timed out.
  7    21 ms    22 ms    23 ms  GigabitEthernet7-0-0.GW11.ATL5.ALTER.NET [152.63.80.53]
  8    51 ms    62 ms    40 ms  oraclenerd.alter.net [157.130.87.138]
  9    25 ms    27 ms    26 ms  connect.oraclenerd.com [10.115.2.235]
Doesn't seem to be an issue there.

More information
I could not connect from any of my virtual machines, nor my host. Well, that's not quite true. While logged in through a Cisco VPN, I could successfully connect to this page (I never tried logging in, I was just testing to see if I could pull up the page). When I disconnected from the VPN, I could no longer access the page.

Over the weekend we had a power failure and I had to reboot the router. Also over the weekend I added an entry to my hosts file for a local connection.

If you know what this is, I will be indebted to you for life. I am sure I could fix it, if only I knew what was wrong. T-Shirts are on the table.

At this point, I'd even offer up my first born. ;)

Update 08/24/2011 12:19 AM EST
Finally resolved the issue. Funny, the last thing I did was call the ISP. I think I took the "blame the user" mentality a tad too far.

I have Verizon Fios, which rocks.



Support is pretty darn good too.

We got the service way back in 2007. This is only the second problem I have had. The last was just a couple of months ago (foreshadowing?).

They bounced the router remotely, then it just started to cycle on and off. They ordered me a new one. 24 to 48 hours. I couldn't wait. I went to the Verizon store down the street and picked up a new (old, used) one for a temporary fix. Tomorrow I'll be getting the new (new) one.

Anyway, plugged it in, navigated to the troublesome page, and voilà!

Much thanks to Martin who walked me through various steps (see comments) over IM yesterday. This isn't the first time he's helped me out either. Top notch guy.

Sunday, August 14, 2011

TFJ: New Bike and Stuff

TFJ = Tired of the Fat Jokes Since purchasing a spinner a few months ago, I've been pretty consistent. I've got a full page of entries now on my workout sheet. I've lost, maybe, 5 or 6 pounds...not great, but I feel a hell of a lot better. I can actually go up a flight of stairs without getting winded. Yay for me. Last weekend I bought a mountain bike, so I could ride around with LC. No Jeff, no cool pedals yet. I'm just getting started. Need I remind you of my heyday? I picked the bike up early Friday morning. LC had asked me to come down to the Y(MCA) to see him swim that afternoon, so I thought that would be a perfect opportunity to break in the new bike. The Y is only a couple of miles away. For normal people. I decided to take the "back" way. Only there wasn't a back way. But I kept going...thinking that at some point I would find a street (I was on the trails, in a park). This park was underwater...well, the roads were. See, I took the back entrance into the park, so I missed the "Road Closed" sign. Every 100 meters or so there was a stretch of trail underwater, for about 100 meters. Did I mention this was my first outdoor ride in ~10 years? After the 2nd or 3rd one, my heart was racing and my arms were killing me. I stopped. Then walked a little. Got back on. Got off. Back on. Finally, the park entrance...and more importantly, a road, a paved road. OMG! I'm like 2 miles north of where I want to be. Make it about a mile. Stop. Half mile. Stop. Finally just push forward, I knew they had sustenance at the Y. I was supposed to be there at 2. I arrived sometime after 3. Bought 2 bottles of Gatorade. Said hi to LC. Went outside and sat down. Exhausted. After about 20 minutes, I gathered up my remaining strength and rode (straight) home. Took 10 minutes. Total time: 2 hours For perspective, this is what I did: Blue line is the direct route to the Y. The red line is the route I took. What can I say? I had no sherpa!

Thursday, August 11, 2011

OBIEE: Stored Proc (Pipelined Function)

Last week I wrote up an issue with Session variables, you can read it here.

That was part of my project to use an Oracle pipelined function as a table source.

Initially, I was only accepting 4 parameters, but I had never gotten it to work perfectly.

Now I have 14 to work with and a deadline fast approaching.

Here's the function call:
foo

( p_01 IN VARCHAR2,
p_02 IN VARCHAR2 DEFAULT NULL,
p_03 IN VARCHAR2 DEFAULT NULL,
p_04 IN VARCHAR2 DEFAULT NULL,
p_05 IN VARCHAR2 DEFAULT NULL,
p_06 IN VARCHAR2 DEFAULT NULL,
p_07 IN VARCHAR2 DEFAULT NULL,
p_08 IN VARCHAR2 DEFAULT NULL,
p_09 IN VARCHAR2 DEFAULT NULL,
p_10 IN VARCHAR2 DEFAULT NULL,
p_11 IN VARCHAR2 DEFAULT NULL,
p_12 IN VARCHAR2 DEFAULT NULL,
p_13 IN DATE DEFAULT NULL,
p_14 IN NUMBER DEFAULT NULL )
RETURN my_object_table PIPELINED;
Here's the table source:
SELECT *

FROM TABLE( FOO( 'VALUEOF(NQ_SESSION.S_P_01)', 'VALUEOF(NQ_SESSION.S_P_02)',
'VALUEOF(NQ_SESSION.S_P_03)', 'VALUEOF(NQ_SESSION.S_P_04)',
'VALUEOF(NQ_SESSION.S_P_05)', 'VALUEOF(NQ_SESSION.S_P_06)',
'VALUEOF(NQ_SESSION.S_P_07)', 'VALUEOF(NQ_SESSION.S_P_08)',
'VALUEOF(NQ_SESSION.S_P_09)', 'VALUEOF(NQ_SESSION.S_P_10)',
'VALUEOF(NQ_SESSION.S_P_11)', 'VALUEOF(NQ_SESSION.S_P_12)',
'VALUEOF(NQ_SESSION.S_P_13)', 'VALUEOF(NQ_SESSION.S_P_14)' )
Note that everything is enclosed in single quotes.

On the report side, I navigate to the Advanced tab



In the Prefix box



I add the following (you'll have to assume that I have created my prompts and presentation variables already, I'm too lazy to go in and create them again).
SET VARIABLE S_P_01='@{P_S_P_01}',S_P_02='@{P_S_P_02}',S_P_03='@{P_S_P_03}',

S_P_04='@{P_S_P_04}',S_P_05='@{P_S_P_05}',S_P_06='@{P_S_P_06}',S_P_07='@{P_S_P_07}',
S_P_08='@{P_S_P_08}',S_P_09='@{P_S_P_09}',S_P_10='@{P_S_P_10}',S_P_11='@{P_S_P_11}',
S_P_12='@{P_S_P_12}',S_P_13='@{P_S_P_13}',S_P_14='@{P_S_P_14}';
(I put hard returns in there for display purposes, remove those).

All is well.

Or not.

I enter in a value in Prompt 1, P_S_P_01, and nothing for the rest. Take a look at the physical SQL:
SELECT *

FROM TABLE( FOO( 'some value', '@{P_S_P_02}',
'@{P_S_P_03}', '@{P_S_P_04}',
'@{P_S_P_05}', '@{P_S_P_06}',
'@{P_S_P_07}', '@{P_S_P_08}',
'@{P_S_P_09}', '@{P_S_P_10}',
'@{P_S_P_11}', '@{P_S_P_12}',
'@{P_S_P_13}', '@{P_S_P_14}' )
Yeah, that's not going to work. Off to Gerard Nico's excellent wiki. There I see you can have a default value for the presentation variable.
@{variables.<variableName>}{<default>}[format]
Excellent. At the end of each session variable assignment, I tack on {NULL}. That didn't work either. That just put NULL in single quotes, like this: 'NULL'. Barnacles.

I then asked for help. Frank suggested wrapping each call to the session variable in the SQL statement in a CASE statement (I swear I had tried that...), so I did...and it worked. Here's my final SQL (Table Source):
SELECT *

FROM TABLE( FOO( 'VALUEOF(NQ_SESSION.S_P_01)',
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_01)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_01)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_02)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_02)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_03)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_03)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_04)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_04)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_05)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_05)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_06)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_06)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_07)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_07)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_08)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_08)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_09)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_09)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_10)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_10)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_11)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_11)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_12)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_12)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_13)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_13)' END ),
( CASE WHEN 'VALUEOF(NQ_SESSION.S_P_14)' = 'NULL' THEN NULL ELSE 'VALUEOF(NQ_SESSION.S_P_14)' END ) ) )
Absolutely hideous! And annoying. But it works.

Hopefully someone (hint hint) will chime in with a better method.

OBIEE 11g: opmnctl start: failed. Can not resolve <hostname> for interface any

I've been working on a proof of concept for OBIEE 11g. Part of that, naturally, includes building out the system.

I'm a fan of rebuilding things over and over and over. Yeah, it's time consuming, but I end up learning a lot...like what can go wrong.

Which brings me to today.
OS: OEL 5 64 bit
Database: Oracle 11gR2
OBIEE 11.1.1.5
Installed the database, created the repository using the RCU and installed the OBIEE software; when it gets to the configuration steps, it fails. I think it was step 12 or 13, (re)starting AdminServer.

Retry.

Fail.

Continue.

Failed again a couple of steps down. opmn something or another.

Log files:
opmnctl start: failed.
Can not resolve obiee11g for interface any
oracle.as.provisioning.exception.ASProvisioningException
 at oracle.as.provisioning.engine.Config.executeConfigWorkflow_WLS(Config.java:872)
 at oracle.as.install.bi.biconfig.standard.StandardWorkFlowExecutor.executeHelper(StandardWorkFlowExecutor.java:31)
 at oracle.as.install.bi.biconfig.standard.InstanceProvisioningTask.doExecute(InstanceProvisioningTask.java:81)
...snip...
Caused by: oracle.as.provisioning.engine.CfgWorkflowException
 at oracle.as.provisioning.engine.Engine.processEventResponse(Engine.java:596)
 at oracle.as.provisioning.fmwadmin.ASInstanceProv.createInstance(ASInstanceProv.java:178)
...snip...
 ... 14 more
Caused by: oracle.as.provisioning.util.ConfigException: 
Error creating ASInstance instance1.
Cause: 
An internal operation has failed: Error in starting opmn server
Operation aborted because of a system call failure or internal error
Action: 
See logs for more details.
 at oracle.as.provisioning.util.ConfigException.createConfigException(ConfigException.java:123)
 at oracle.as.provisioning.fmwadmin.ASInstanceProv._createInstance(ASInstanceProv.java:317)
 at oracle.as.provisioning.fmwadmin.ASInstanceProv.createInstance(ASInstanceProv.java:166)
 ... 18 more
Caused by: oracle.as.management.opmn.optic.OpticException: Error in starting opmn server
Operation aborted because of a system call failure or internal error
 at oracle.as.management.opmn.optic.OpmnAdmin.executeCommand(OpmnAdmin.java:310)
 at oracle.as.management.opmn.optic.OpmnAdmin.startOpmnServer(OpmnAdmin.java:87)
 at oracle.as.provisioning.fmwadmin.ASInstanceProv._createInstance(ASInstanceProv.java:254)
 ... 19 more
Awesome.
Can not resolve obiee11g for interface any
I destroyed the OBIEE deployment, reinstalled the repository, tried again. Fail.

Finally gave up and started from scratch.

Opened up the documentation (finally) to see if I missed any pre-installation configuration steps. Nope.

Before you install Oracle Business Intelligence and related components, do the following:

- Review Chapter 1, "Installation Overview," to ensure you understand the options and features related to installing Oracle Business Intelligence.

- Review Chapter 3, "Oracle Business Intelligence Requirements," to ensure that your environment meets the minimum requirements.

- Install your database before running Oracle Business Intelligence 11g Installer, and have the database running during the installation.

- Run Repository Creation Utility (RCU) to install the schemas that Oracle Business Intelligence requires. See Section 3.3, "Create Database Schemas Using the Repository Creation Utility (RCU)."

- If you are installing on multiple computers, review Chapter 2, "Recommended Installation Scenarios for Oracle Business Intelligence," for common scenarios.

- Verify that you have sufficient administrator privileges to install the software.

- If you plan to install Oracle Business Intelligence on a computer that uses Dynamic Host Configuration Protocol (DHCP), you must perform additional configuration tasks before you install Oracle Business Intelligence. See "Installing on DHCP Hosts" in Oracle Fusion Middleware Installation Planning Guide.

- If you plan to install Oracle Business Intelligence 11g on a computer that contains Oracle Business Intelligence 10g, shut down the Oracle Business Intelligence 10g installation before installing Oracle Business Intelligence 11g. To upgrade from Oracle Business Intelligence 10g to Oracle Business Intelligence 11g, refer to Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence Enterprise Edition for further details.

- Ensure that your database is not hardened for security purposes, as installing Oracle Business Intelligence against a hardened database is very likely to fail.

Did I do anything unusual in the initial setup?

No.

Wait.

I changed the hostname.

Using the Google Machine, I queried for Can not resolve obiee11g for interface any which took me to this OTN post.

I believe you could totally avoid this if your host file were setup in the manner of having each alias on an individual line. I found that the behavior of how the host file works on newer windows versions (Vista/7/2008) is affected by the DNS Client Service

In the Host file try to use the below format, utting everything on an individual line similar to:
127.0.0.1 localhost
127.0.0.1 MYSERVER
::1 localhost
::1 MYSERVER

Hmmm...

I changed the hostname. I didn't like "new-host-4". Really?

I updated my hosts file to match what was suggested:
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost
127.0.0.1       obiee11g
::1             localhost6
::1             obiee11g
Reran everything and the configuration completed without error.

Joy.

Maybe I should have followed the instructions for Installing on DHCP Hosts...

Thursday, August 4, 2011

OBIEE 11g + YSlow

A recent discussion at the OBIEE Enterprise Methodology Group reminded me of something...

A few weeks back I tried out the YSlow extension from Yahoo against OBIEE 11g.

I couldn't figure out how to print or export the results until this morning when I clicked on the Tweet button. That gave me a URL.

For my test this morning, I used the SampleApp Build 10502 General Index (which rocks by the way, tons of great stuff and examples the team has created):



Overall Grade, D.

This page either got an A or a F. The F's were in the following categories:

- Make fewer HTTP requests
- Use a Content Delivery Network (CDN) (no idea)
- Add expires headers
- Compress components with gzip
- Put JavaScript at bottom
- Reduce the number of DOM elements
- User cookie-free domains



You can find the page here.

Interesting stuff.

Google also has an extension called PageSpeed which you can find here.

Tuesday, August 2, 2011

Mastering Oracle Trace Data

From The Twitter Machine today:



Which takes you here.

I had the pleasure of meeting Mr. Millsap in person last year at OOW. Since then, I've seen him present at KScope and more recently at our local user group.

I can't tell you how much I haven't processed yet.

Anyway, he had mentioned to me that he was considering this one day Masterclass event, but hadn't figured out all the details. He has now.

So it looks like he only has an event set up for Dallas/Ft. Worth, I want him to come back and visit Florida. We're often left out of these types of events (everyone neglects America's...ok, I won't go there).

Why?
I have an obsession about proving things...I'm tired of getting beat down by people who say "prove it."

With that in mind, I'm starting a petition of sorts, to lure Mr. Millsap into coming back to Florida.

If you're interested, email me to let me know you're interested in having this event down here in Florida. If enough people are interested, I'll bug Mr. Millsap until he agrees to come back.

Update
When Mr. Millsap was in town, after he presented, he was showing us some material from this class he was thinking about.

If you read Jake's musings over at the AppsLab, you'll know he (and Rich and Anthony) have gone to Google IO for the past 2 years. This year they came away with, among other things, a Samsung Galaxy Tab 10.1 and a Chromebook (which shipped very recently).

Mr. Millsap is doing something similar. Not only do you get to learn all about trace data, you get the tools to do so:

- Mastering Oracle Trace Data booklet, by Cary Millsap
- A limited license for MR Trace, the zero-click trace file collector for Oracle SQL Developer
- A limited license for MR Tools, the premiere software for trace file mining, management, and manipulation
- A Method R “I can help you trace it” T-shirt

So sign up for the DFW class, or let me know so we can bring Mr. Millsap back to Florida.

Monday, August 1, 2011

OBIEE: The repository variable, %S has no value definition

Version: 11.1.1.5
OS: OEL 5
First of all, it's a Session variable, not a repository variable. Whatever.

So I had created a PIPELINED function to serve as the source of a table. From the analysis side, we wanted to use a presentation variable to set a session variable that was referenced in the table source.

Here's the SQL for the Stored Proc call:
SELECT *
FROM TABLE( get_key_value_pairs( 4 ) )
I hardcoded 4 in there just to see how it worked. BTW, Gerard Nico describes the process here.

I then created a session variable along with the init block.
SELECT 5 FROM dual
which would populate the P_LIMIT session variable. Nothing terribly fancy.

I then ran a simple report based off of the new table source (with the hardcoded value):



Cool, now let's swap out the hardcoded value with the reference to the session variable.

Since I don't do a lot of front-end stuff, this post at Siebel Essentials has been my bible.

I locate what I am trying to do in the matrix:
VALUEOF("NQ_SESSION.P_LIMIT")
Like a big jerk, I have to add spaces to it, you know, for readability. My SQL looks like this:
SELECT *
FROM TABLE( get_key_value_pairs( VALUEOF( "NQ_SESSION.P_LIMIT" ) ) )


OK, let's try without the quotes ("):
SELECT *
FROM TABLE( get_key_value_pairs( VALUEOF( NQ_SESSION.P_LIMIT ) ) )
Same thing.



Let's gid rid of the spaces (I've had issues with spaces before, but I can't seem to find the post).
SELECT *
FROM TABLE( get_key_value_pairs( VALUEOF(NQ_SESSION.P_LIMIT) ) )


Voilá!

Fun. I have noticed that certain areas of the RPD treat spaces slightly differently. If I ever find the other post, I'll link it up here.

Lesson? Be careful with your space.