Monday, July 31, 2006

Installing APEX 2.2 on XE

As you might have noticed APEX 2.2 is available for download. On the download site nowhere is stated that is doesn’t work for on XE, but installing on XE fails directly. How to solve this?

This is due to the following lines in the upgrade procedure in the file apexins.sql:
declare
t_edition varchar2(30);
edition_is_xe exception;
begin
--select edition into t_edition from v$instance;
execute immediate 'select edition from v$instance' into t_edition;
if nvl(t_edition,'x') = 'XE'
then

dbms_output.put_line('---------------------------------');
dbms_output.put_line('- Ap Ex cant be installed in Express Edition.-');
dbms_output.put_line('-------------------------------------');
raise edition_is_xe;
end if;
exception
when edition_is_xe then raise;
when others then null; -- no edition column, not xe
end;
/
Once the lines are REM’med out the upgrade process runs fine. After finishing the other steps from the upgrade manual I can login, change and run previously defined applications. The main difference with the “lightweight” APEX installed with XE is that there are workspaces defined for every Apex-developer (with the same name).

At this point you are only missing access to the workspace INTERNAL using the ADMIN account. This is easily solved by creating a new admin account by logging in in SQLPLUS as FLOWS_020200 user (unlock the account first) and issue:
begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'admin2',
p_email_address => 'myemail@mydomain.com',
p_web_password => 'admin2') ;
end;
/
commit;
Now we can administer APEX on XE with ADMIN2 as were it a “normal” APEX installation!

Monday, July 24, 2006

How to create neatly formatted Excel documents using PL/SQL?

If there is a requirement to produce output from an application into Excel, you would probably create a CSV (Comma Separated File) with the data and start Excel to show the data - at least that's what I did...until now. The drawback of this solution is that you could only produce data and no nice layout. But Excel is also capable of opening HTML-files and using this you could create Excel files with data and magnificent layout!

Let me give an example:
1. Create a procedure to show the data in formatted in an HTML table.

CREATE OR REPLACE PROCEDURE display_emp_list IS
v_emp_count NUMBER(5);
v_empno NUMBER(8);
v_ename VARCHAR2(50);
v_job emp.job%TYPE;
v_sal emp.sal%TYPE;
v_bg_color VARCHAR2(10) := '';
CURSOR c_emp IS
SELECT empno,
initcap(ename),
job,
sal
FROM emp
ORDER BY ename;
BEGIN
SELECT COUNT(*)
INTO v_emp_count
FROM emp;
owa_util.mime_header('application/ms-excel', FALSE);
htp.p('Content-Disposition: filename="test.xls"');
owa_util.http_header_close;
htp.htmlopen;
htp.bodyopen;
htp.header(5, '<font color="RED"> <center> EMPLOYEE INFO </center></font>');
htp.tableopen(cattributes=>'border="2",bordercolor="BLACK",width="60%",align="CENTER"');
htp.tablerowopen(cattributes => 'bgcolor="GREEN"');
htp.tableheader(cattributes => 'colspan="4", align="CENTER"'
,cvalue => '<font face="ARIAL" size="2.5" color="WHITE">'
|| 'Employees. (List Count:' || v_emp_count || ')');
htp.tablerowclose;
htp.tablerowopen(cattributes => 'align="LEFT", bgcolor="YELLOW"');
htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'No.');
htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Name');
htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Job');
htp.tableheader(cvalue => '<font face="ARIAL" size="2.25" color="BLUE">' || 'Sal');
htp.tablerowclose;
OPEN c_emp;
FETCH c_emp
INTO v_empno,
v_ename,
v_job,
v_sal;
WHILE c_emp % FOUND
LOOP
IF MOD(v_emp_count, 2) = 0
THEN

v_bg_color := 'SILVER';
ELSE
v_bg_color := 'WHITE';
END IF;
htp.tablerowopen(cattributes => 'bgcolor=' || v_bg_color);
htp.tabledata(cattributes => 'align="LEFT"'
,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_empno);
htp.tabledata(cattributes => 'align="LEFT"'
,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_ename);
htp.tabledata(cattributes => 'align="LEFT"'
,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_job);
htp.tabledata(cattributes => 'align="LEFT"'
,cvalue => '<font face="ARIAL" size="2" color="BLACK">' || v_sal);
htp.tablerowclose;
v_emp_count := v_emp_count -1;
FETCH c_emp
INTO v_empno,
v_ename,
v_job,
v_sal;
END LOOP;
CLOSE c_emp;
htp.tablerowclose;
htp.tableclose;
htp.bodyclose;
htp.htmlclose;
EXCEPTION
WHEN no_data_found
THEN

NULL;
END;
2. In the browser the table looks like this:
HTML-table in the browser
3. By adding the owa_util lines in the procedure and executing it in your browser you’ll get a download option window:
Download option window
4. If you select “Openen met” (”Open with”) Excel starts and the document looks like this:
Results in Excel
So that creates the possiblity to create Excel output just the way your user likes it!