Custom ORACLENERD T-Shirts

Jeff Smith has said for some time now that I need to market the t-shirts better than I do. Selling them has never been a priority. Yes, it's pretty cool seeing the t-shirts out and about. It's cool that people can express their inner oraclenerd like I seem to do on a daily basis. The real reason I put effort into t-shirts and the like, is katezilla.

Recently we got hit with a couple of things: 1, a $190 iPad app for Kate and 2, her ABA therapy co-pays finally came due. For #1, I ran a GoFundMe campaign and encouraged you to buy t-shirts. The GoFundMe campaign raised $420 in less than a day, more than covering the cost of Kate's iPad app. I used the remaining funds to make a payment on #2, her ABA co-pays. I also sold about 10 shirts just after that post and close to 20 in total since.

I am constantly humbled at your generosity.

What does all of this have to do with the title? Well, people have asked for shirts either through Twitter, IM or email. Shirts with specific sayings or different styles. Two weekends ago on a Friday night, Don Seiler suggested a hoodie. Living in Florida I had never thought about it, but he lives in Wisconsin where it gets a little cold. We went back and forth on twitter, I would go on Spreadshirt and spin something up, screenshot it, and send it across the wire. On Monday I had a final product and I named it after Mr. Seiler.

Mr. Seiler (picture) ordered one and so did Mr. Smith. Awesome.

The Don Seiler Hoodie	The German Austrian

The other shirt there is the German Austrian edition. Martin Berger was responsible for that one (I have a Cyrillic and Russian version in the hopper for Greg Rahn).

Late last week I talked to Lisa Dobson. She got one last year for OpenWorld and wanted a new one, with a twist.

The Lisa Dobson	The Lisa Dobson Pink

Upside down logo (she's British). So I named it after her. Note, the pink version is only available in the US, that brand isn't carried on the European Spreadshirt site.

So, if you have an idea (Mr. Seiler has designed two now, the long sleeve baseball jersey and the hoodie), send it to me and I'll spin something up for you.

How could I forget the APEX version (Joel Kallman)? Or the OBIEE version (Adrian Ward)?

Because I'm forgetful. I'm sure there are others.

Just so it's clear, there are two shops: North America and Europe. They're not exactly the same as it's two separate systems, but I'm trying to keep them in sync.

Send me your suggestions...all proceeds go to a great cause katezilla.

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:

• BI Publisher Web Services documentation.
• APEX and BI Publisher integration example done by Tyler Muth.

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

ATTR	VAL
NS	xmlns: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_URL	http://1.2.3.4:9704/xmlpserver/services/PublicReportService
USERNAME	bip_service_acct
PWD	D486ACCFD
BIP_SERVER	1.2.3.4
BIP_PORT	9999

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

(ā'pěks)

My ode to Joel Kallman.

Expert Oracle APEX

I'm a bit of a fan of APEX; haven't used it consistently in a while but I think it's a great tool. I've annoyed many managers/DBAs about getting it installed and configured for use...most of that to no avail.

Many years ago, almost 6 to be exact, I decided to port my business' J2EE app over to APEX and I needed hosting. Still relatively new, the market was small. Through the forums, I found John Scott and ShellPrompt. It took a few months to write the J2EE application from scratch, it took less than 2 weeks of off-hours work to port. I hosted my site there for a year, maybe a little longer. During that time, John was pure awesome. I blogged about John's customer service once, but that was another life and the blog no longer exists. We have met IRL, but have yet to have a beer together, I'm pretty sure he owes me at least 4 now.

Anyway, John has put together a new book, Expert Oracle Application Express which is a joint effort by some 14 different APEX authors.



While the content must be outstanding, the best part is that all funds will be donated to the families of 2 men, Carl Backstrom and Scott Spadafore. Both men worked for Oracle on the APEX team. Both men were incredibly involved in the community. Both were highly regarded in that community.


John blog's about it here.

If you use APEX, new to APEX, or just want to know WTF it is, go out and (pre)order this book now. Not only do you get a great resource, but you get to help out the families of Carl and Scott.

SOUG: APEX 4.0

The much anticipated APEX 4.0 release is coming soon...in that spirit, Dan McGhan [blog|twitter] will be presenting on the new features. You might remember Dan, he's the one that constantly interrupted me during my first presentation ever, on APEX. We followed that up with a "joint" (by joint I mean I "let" him do all the work) presentation a few months later.

The event goes from 6 until 8 with the first half hour dedicated to eating and greeting. From 6:30 to 8 will be Dan's presentation.

You can read the event details here. Or I can just put it all here to save you a click (you're welcome):

Dan McGhan, our resident APEX expert, will be bringing us up to speed on the recent new release of Oracle's Application Express. APEX 4.0 is expected to be the biggest release in the product's history.

This session will break down many of the exciting new features that everyone has been looking forward to, including Websheets, Dynamic Actions, and Plug-ins, just to name a few. With APEX 4.0, development should be faster and easier than ever.

Dan McGhan has been a long time member of the SOUG. Dan is an Oracle Application Express expert and advocate. In addition to his "day job" with SkillBuilders.com, he is one of the top 10 contributors to the APEX forum, maintains his own Oracle and APEX blog, and has been a speaker at the New York Oracle Users Group, New England Oracle Users Group, and Suncoast Oracle Users Group events.

End User Response Time

Inspired by Robin's [blog|twitter] Measuring real user response times for OBIEE, I remembered a wonderful APEX nugget: DEBUG

Robin's post was the continuation of a discussion between himself and Alex Gorbachev [blog|twitter] about...something. I'm pretty sure I glossed over much of it.

The reason I glossed over it is because I immediately thought about APEX and how easy it was to measure end user response time. OK, a little fib there, the data is there, some enterprising young APEX developer would just need to figure out how to get it.

OBIEE has cruddy instrumentation...wait, no instrumentation, from an end-user perspective.

But APEX does. (BTW, we should lobby to get some from the APEX team to show the OBIEE team how they do it).

 0.00:
0.00: S H O W: application="104" page="1" workspace="" request="" 
session="2529775339668942"
0.01: Language derived from: FLOW_PRIMARY_LANGUAGE, current browser language: en-us
0.01: alter session set nls_language="AMERICAN"
0.01: alter session set nls_territory="AMERICA"
0.01: NLS: CSV charset=WE8MSWIN1252
0.01: ...NLS: Set Decimal separator="."
0.01: ...NLS: Set NLS Group separator=","
0.01: ...NLS: Set date format="DD-MON-RR"
0.01: ...Setting session time_zone to -04:00
0.01: Setting NLS_DATE_FORMAT to application date format: MM/DD/YYYY
0.01: ...NLS: Set date format="MM/DD/YYYY"
0.02: NLS: Language=en-us
0.02: Application 104, Authentication: CUSTOM2, Page Template: 1260921800703249
0.02: ...Determine if user "CJUSTICE" workspace "1122708729156622" can develop 
application "104" in workspace "1122708729156622"
0.02: ...ok to reuse builder session for user:APEX_PUBLIC_USER
0.02: ...Application session: 2529775339668942, user=APEX_PUBLIC_USER
0.02: ...Determine if user "CJUSTICE" workspace "1122708729156622" can develop 
application "104" in workspace "1122708729156622"
0.02: ...Check for session expiration:
0.02: Session: Fetch session header information
0.02: ...Metadata: Fetch page attributes for application 104, page 1
0.02: Fetch session state from database
0.03: Branch point: BEFORE_HEADER
0.03: Fetch application meta data
0.04: Setting NLS_DATE_FORMAT to application date format: MM/DD/YYYY
0.04: ...NLS: Set date format="MM/DD/YYYY"
0.04: Computation point: BEFORE_HEADER
0.04: Processing point: BEFORE_HEADER
0.04: Show page template header
0.05: Computation point: AFTER_HEADER
0.05: Processing point: AFTER_HEADER

That's just the header section.

Now OBIEE can fire off multiple queries for a single report and for that you would probably use the built-in Usage Tracking data...but you still wouldn't have a end-user experience there.

I created a simple APEX page with two reports. One is querying a 10 record table and the other is querying a copy of DBA_OBJECTS.

Check out the instrumentation surrounding the region:



That's the small table and it's pretty quick.

Now let's look at the big table:



You might be able to see up at the top of that last image, the 0.13 IR:Binding...then the 0.17: Printing rows

That'll give you an idea of how long each region takes to run (0.04) which would help you to identify slow parts of your web page. I've used this in the past and it's very nice.

Finally, you have the footer section:

 0.17: Computation point: AFTER_FOOTER
0.17: Processing point: AFTER_FOOTER
0.17: Log Activity:
0.18: Execute Count=0
0.18: End Show:

Basically, the page took 0.18 seconds to load. Start capturing that data (i.e. snapshot), make your changes and then re-evaluate. Alex put it very nicely in the comments:

In this case, it’s a project of migrating from one platform to another. While load simulation provides results (and that’s been done), end users experience (response time) is the only thing that counts.

Without measuring it, users have full control on how they relay their experience to you and there is nothing you can prove or disprove.

Measuring it gives all the answer (and lots of power!).

APEX: Report Column Wrapping

APEX 3.2.1.00.12
- Template 12
Database: 10gR2

Just a quick note, mostly for myself.

I was asked about getting a report column to wrap. The method being used was the CSS Style property of the Report Attribute (Column). The report template was the Standard Alternating Row Colors.

The report looks like this:



Very ugly, I know.

So how do you fix this?

You can fiddle with the CSS Style (I'm lazy) if you want, or you can do something like this.

In the HTML Expression box:



Add the following:

<table style="width:200px;">
  <tr>
    <td>#X#</td>
  </tr>
</table>

That #X# is the column/report name.

Using that will get you this:



The ideal would be to get the correct CSS in there, but like I said, I'm lazy. This worked for me going back to 1.5 (HTMLDB) on Template 12.

APEX Architecture

I'm not talking about how APEX internally or within the database works, I'm talking about how to best manage it from an infrastructure point of view.

I recently raised the issue with my client about how they were using APEX. I'm not here as an APEX consultant, but they didn't have a lot of experience in-house and the DBA who got it implemented had been trying for years. So I wrote up my thoughts and passed them along to my supervisor who then shared them with others.

The current setup was a stand-alone instance that was only used for APEX applications using database links and synonyms to access the source tables. I was arguing to have APEX installed on the database where the data lives.

When I got on the call, I listened to their arguments and concerns.

Then I began to think...what do you do if you have, say, 100 databases? Do you install APEX on each and every one? That's just another piece that has to be supported and maintained right?

After the call, I began to do a little research on the subject...and couldn't find a whole lot of information out there. Perhaps I wasn't searching on the right terms...not really sure.

Thinking about it further and talking with some friends, I came up with a short list of potential architectures.

1. Install it Locally - This is my preferred method. The APEX metadata (application) lives in the same database as the data it manipulates.
2. Database Links - Target Tables Remote, Source Code Local - This is the configuration I spoke of above. There is a single database where APEX is installed which accesses tables across database links. Synonyms would be used for tables and views to "hide" the long names and make them visible. Any source code (PL/SQL) would be local.
3. Database Links - Target Tables Remote, Source Code Remote - Same as #2, but the source code lives in the remote database. Again, synonyms would need to be used to make database objects visible to APEX.

Number 2 and 3 both limit the functionality of APEX. I am aware of the Automated Row Fetch not working across database links. I know, for #3, you can't use the RETURNING clause, which isn't a huge deal...just annoying.

For #2 and #3, the synonyms add additional maintenance and complexity to your application(s).

Also with #2 and #3, you have additional network traffic, which, depending on the environment and/or the number of users, could be significant.

Like I said, my first thought was to install it locally. In addition, just put it on the databases that you will use it, not every single one. I still believe that, but am trying to give a balanced and reasoned answer.

Ultimately, maybe it just depends on how you use it. If this is going to be a "professionally" written application (as opposed to something a power user might build), I think having it installed on the databases where your data lives. If it's going to be driven by those power users as a replacement for the Excel and Access databases, I think the best approach might just be the single instance accessing tables remotely would be the way to go.

Anyway, just my thoughts. Please scream (write) loudly if you have thoughts on the matter.

Developing in APEX

I see 3 possible ways to develop in APEX.

1. Packaged Code: PL/SQL - With this method you take, possibly, an existing application and "skin" it with APEX. Using APEX's built in Form based on a Procedure wizard, this is quick and painless. You spend most of your time just moving buttons around to suit your needs. Maybe I've been reading AskTom for too long, but that's the general approach I take.
2. Declarative - Using all the built-in functionality of APEX which creates all kinds of objects (triggers for example) that will be managed by APEX. This is probably most geared towards the power users who don't yet have the ability to write a lot of PL/SQL but have a pretty good understanding of building an application.
3. Hybrid - This approach mixes and matches the 2 previous approaches. You use packaged code for the bulk of your application and the declarative functionality within APEX for everything else. For example, the multi row INSERT/UPDATE/DELETE. I can write that whole thing out using TYPEs in PL/SQL, populate an APEX collection, convert that collection to a TYPE and then pass that TYPE back to PL/SQL...but do I want to? It saves a boat load of time to use the built-in features. Ultimately it depends, of course, on many different factors.

I think I will always prefer #1...like I said, I read AskTom for years, mostly for kicks.

Mr. Kyte on where to put data, in the DB or in the web tier:

They belong right next to the data - allows you flexible access to the data - you can build a new application WITHOUT reinventing all of that stuff.

where does the logic to display a bunch of numbers in a pie chart belong? In the application.

where does the logic to ensure that if X=0, then Y must be >= 0? That belongs right next to the data.

So, programming logic - some of it in the application - user interface code, user interaction code, error handling, display of data, report generation - in the application.

Much of it in the database - security, access control, data integrity.

I love APEX, but will it be around forever?

ORA-22816: unsupported feature with RETURNING clause

Of course...last night I remembered what it was about using the RETURNING clause across database links.

I was trying to prove a point about APEX across database links but messed up my demo.

If the code lives on the database with APEX installed, then you can't use database links with the RETURNING clause.

Now I create the code in my schema referencing the objects using database links.

CREATE SEQUENCE sq_t
  START WITH 100
  INCREMENT BY 1
  CACHE 10
  NOCYCLE;

CREATE OR REPLACE
FUNCTION create_t
  ( p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2 ) RETURN NUMBER
IS
  l_id NUMBER;
BEGIN
  INSERT INTO t@my_application
    ( id,
      first_name,
      last_name )
  VALUES
    ( sq_t.nextval,
      p_first_name,
      p_last_name )
  RETURNING id INTO l_id;
  
  RETURN l_id;
END create_t;
/
show errors

The function compiles normally, but when you try and run it...

CJUSTICE@TESTING>var c number;
CJUSTICE@TESTING>exec :c := create_t( 'chet', 'justice' );
BEGIN :c := create_t( 'chet', 'justice' ); END;

*
ERROR at line 1:
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at "CJUSTICE.CREATE_T", line 7
ORA-06512: at line 1

Voila!

APEX: Database Links

I've talked about APEX being hardly known among quite a few people in the Oracle world...at least the ones I have worked with. When I first moved to Tampa, people had heard of it, but never got around to trying it out. This was late 2006, around version 2.2. My next job, same thing. The DBA had heard of it, but hadn't used it yet. Thankfully he was open and willing to try out new things. This is the same guy who had an 11gR1 RAC install within months of 11g being released in 2007. Can you say glutton for punishment?

My next assignment had it installed, but it was mostly for reporting so it had it's own instance. APEX utilized database links to access other databases. I was tasked with re-engineering their payment processing application using APEX as a skin. I worked to convince them that APEX needed to be installed on the same machine. The DBA fought me tooth and nail (and ultimately won) on that issue. I didn't get the opportunity to finish that application.

Anyway, the point I try to make is that you take away a lot of the features of APEX when you use it across a database link.

I'm not one to use a lot of the wizards or declarative stuff, I like to use packages and use the "Create Procedure from Form" wizard.

It's amazingly simple and only requires a little tweaking on the front end to finish up a input/update/delete form.

Using database links though, this isn't possible.

For example:

CREATE USER table_owner
  IDENTIFIED BY testing
  DEFAULT TABLESPACE users
  QUOTA 10M ON users;

CREATE DATABASE LINK my_application
  CONNECT TO table_owner 
  IDENTIFIED BY testing
  USING 'TESTING';

That's a loopback database link pointing to my sandbox.

CREATE TABLE table_owner.t
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30)
    CONSTRAINT nn_firstname_t NOT NULL,
  last_name VARCHAR2(30)
    CONSTRAINT nn_lastname_t NOT NULL
);

CREATE SEQUENCE table_owner.sq_t
  START WITH 100
  INCREMENT BY 1
  CACHE 10
  NOCYCLE;

CREATE OR REPLACE
FUNCTION table_owner.create_t
  ( p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2 ) RETURN NUMBER
IS
  l_id NUMBER;
BEGIN
  INSERT INTO t
    ( id,
      first_name,
      last_name )
  VALUES
    ( sq_t.nextval,
      p_first_name,
      p_last_name )
  RETURNING id INTO l_id;
  
  RETURN l_id;
END create_t;
/
show errors

I then create some basic objects in the TABLE_OWNER schema including a function that returns the ID of the newly created record. On to APEX where I have created a simple one page app. This workspace is mapped to my schema, CJUSTICE and the database link is private to that schema.

Now I want to create a new region on the page



Click Next.



Click Next.



Click Next.

Now select your schema. Your workspace can map to multiple schemas, but I have only one, my own.



Click Next.

Now click on the little button thing in red



You'll see a popup window listing out all the procedure and functions in your schema.



So it's not there...but it is in another schema (and since the database link is using the schema owner, there is no need to GRANT EXECUTE on the procedure).



Nothing there.

Let's try entering it in manually



Well that sucks.

Can I do it manually?

First I create the process:



Build a simple form:



Enter in some data, click on submit.



Then verify:

TABLE_OWNER@TESTING>SELECT * FROM T;

        ID FIRST_NAME                     LAST_NAME
---------- ------------------------------ ------------------------------
       100 JOHN                           PIWOWAR

Amusingly, I seemed to recall an issue, a few years back about using the RETURNING clause across a database link. I wonder if that's because it's not a true database link? (Figured it out the next day of course, ORA-22816)

Anyway, the point is, install APEX on the database you plan on building your application. You'll only add unnecessary work and time to your development efforts which means you might as well use Java. :)

APEX: Database Object Dependencies

I have this tendency to talk about APEX a bit...at my current client, they recently began using it. When one of their applications broke (built by a consultant), I was asked to help out.

Love the APEX.

In particular, I was asked if I could find out how the security was being implemented. Active Directory was being used to authenticate, but they weren't quite certain how authorization was used. I was given a package body and got started.

First thing I thought of was the Database Object Dependencies report. This is one of my all-time favorite features of APEX. It allows you to easily find all the references to database objects and includes a link to see each page where said object is used. Makes learning about an application super easy.

To get to this report, go to your application home. Off on the right side, you'll see a link that says Application Reports.



That first section there under Application you should see a link for Database Object Dependencies



You'll probably have a blank page, go ahead and click on the Compute Dependencies button



You'll get a lovely report like this



Now you can drill down and find the pages where the object is referenced. I chose DEMO_CUSTOMERS



Click on the page number to go to...you guessed it, that page. Remember the name of the component, locate it and see your object.

How nice is that?

APEX: Create and Parse Arrays

It's been awhile since I've been able to work with APEX extensively, so I am rusty.

A question came up today whether we could get multiple values into a single variable (Item in APEX).

Yes we can!

APEX_UTILSNeed some data first:

CREATE TABLE t ( some_text VARCHAR2(10) );

INSERT INTO t ( some_text )
SELECT dbms_random.string( 'a', 10 ) some_text
FROM dual
  CONNECT BY LEVEL <= 5;

CJUSTICE@TESTING>SELECT * FROM t;

SOME_TEXT
----------
thrFXviVWJ
kpfGRRwctv
EVxNrcmBHC
gcBlHaKrLa
irYduOZfkS

I want that table data to be in a single item. TABLE_TO_STRING is your function.

VAR C VARCHAR2(100);

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  SELECT some_text
  BULK COLLECT INTO l_table
  FROM t;

  :c := apex_util.table_to_string( p_table => l_table );
END;
/

PL/SQL procedure successfully completed.


C
-----------------------------------------------------------
thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS

Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer.

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_table := apex_util.string_to_table( p_string => :c );
  
  FOR i IN 1..l_table.COUNT LOOP
    d( 'value ' || i || ':  ' || l_table(i) );
  END LOOP;
END;
/

value 1:  thrFXviVWJ
value 2:  kpfGRRwctv
value 3:  EVxNrcmBHC
value 4:  gcBlHaKrLa
value 5:  irYduOZfkS

PL/SQL procedure successfully completed.

Done.

APEX: LDAP Authentication

I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected.

I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide.

Anyway, it was remarkably easy.

Setup
APEX: 3.2.1
Web Server: Apache (OHS)
Database:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

First I fired up the web server:

C:\oracle\http\opmn\bin>opmnctl start
opmnctl: opmn started

C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication."

After I had created the application, I went into Shared Components --> Authentication Schemes --> Create

Select the default and click Next



Select "Show Login Page and Use LDAP Directory Credentials" and click Next



I've already done this so I'm selecting my current Login page, 11, click Next



Enter your LDAP Host and your DN:



Your DN String should look something like this (from article above):

cn=%LDAP_USER%,l=amer,dc=oracle,dc=com

Make sure you use the %LDAP_USER% after the cn= portion of the string.

Name it ldap_test, click Create Scheme:



You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current



To test it just run your application and login using your LDAP (AD) credentials



Success!

APEX: Application Builder Defaults

Home --> Workspace --> Application Builder --> Application Builder Defaults

I'm not sure which version this was released, but it makes life just a little bit easier...which is ultimately the goal of any technology. From your Application Builder home page in the Tasks section, you should see Application Builder Defaults



In the Application Builder Default section, you can set preferences for Tabs, Authentication, Themes and Globalization. What this means is that each subsequent application you create in a given workspace will default to the values you supply in this section.

Your choices with Tabs are No Tabs, One Level of Tabs or Two Levels of Tabs. I've always used Two Levels of Tabs, just in case I want or need to expand the application.



For Authentication, I like to default to No Authentication, preferring instead to add an Authentication schema at a later time.



Theme 12 used to be my favorite...I had heavily customized it in the past and became very familiar with it. However, I'm starting to like Theme 20 now, so I select that.



As for Globalization, I just accept the defaults. I have yet the opportunity to build an application in anything other than English.



Next time you go to create an application, you should be able to just click through as your favorites are now the default.

APEX: Assign Multiple Schemas To A Single Workspace

Home --> Administrative Services --> Manage Workspaces --> Assign Multiple Schemas To A Single Workspace

While "teaching" APEX to a group of folks I was asked how to assign multiple schemas to a single workspace. For the life of me, I couldn't remember or figure out how to do it through the web interface. Strangely, I had recently been playing with the APEX_INSTANCE_ADMIN package and I knew it was possible...just couldn't find the right way through the interface.

We'll start with creating a new workspace using the web interface.

First, click on Create Workspace



Next, name the workspace "TEST" and select Next.



Select Yes from the drop down for "Re-use existing schema?" I picked APEX_TEST as the schema to map to. Then select Next.



I left the default Administrator Username, ADMIN. The password is ADMIN and the email is ADMIN@EMAIL.COM. Select Next.



I'm then prompted to confirm the details of the new workspace. Select Create.



My workspace has been created.



You'll be redirected back to the Manage Workspace page.

Now select the link for Manage Workspace to Schema Assignments



You'll be taken to a page that looks like this, select Create



The check "Existing" when prompted for a New or Existing Schema



Select your newly created workspace, TEST, and click on Next



Either enter the schema you want to map to or select it from the popup, click Next



Confirm your settings and click on Add Schema



And voila! You've now mapped your workspace to 2 separate schemas



Manually
You can also do this if you create a workspace via APEX_INSTANCE_ADMIN, but only on creation, there seems to be no facility in that package to add it after creation. The procedure call is ADD_WORKSPACE.

I'll create a new workspace, MANUAL_WORKSPACE and assign the primary schema as APEX_TEST and the secondary (you can add as many as you want with a colon delimited list) will be APEX_USER:

BEGIN
  apex_instance_admin.add_workspace
    ( p_workspace_id => NULL,
      p_workspace => 'TEST_MANUAL',
      p_primary_schema => 'APEX_TEST',
      p_additional_schemas => 'APEX_USER' );
END;
/

PL/SQL procedure successfully completed.

COMMIT;

And you're done



Update 12/05/2009 10:17 PM
I was wrong, you can add a schema after it has been created using the APEX_INSTANCE_ADMIN package...If you want to add a second schema to an already existing workspace, use the ADD_SCHEMA procedure.

CJUSTICE@TESTING>EXEC apex_instance_admin.add_schema( 'TEST', 'APEX_USER' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
CJUSTICE@TESTING>COMMIT;