Just the other weekend I went to a party, one of those dance parties with loud, fast music, free-flowing alcohol and the type of beautiful women who wear short skirts, irrespective of the temperature outside. Y'know, just the average weekend for your typical Oracle Application Express blogger.
The thing to do, when you find yourself in this situation, is sit back, have a sip of alcohol to soothe your nerves, and survey the scene. Step back into the shadows of evolution: it is 2000BC again and you are a hunter on the plains of the Serengeti overlooking your prey. Choose your quarry and then step onto the dancefloor, making gentle rhythmic moves. Circle her (or him) gradually and then move in, bringing your arms and your head into your dance. Whisper to them, "May I have this dance?"
Guaranteed success, young one. Or your money back.
Real life is nothing like Hollywood. When you're dancing with(/against) a female in real life you don't have a troupe of other dancers dancing behind you in formation, matching your every move perfectly, like the dancers in Thriller, Step Up 3D or any of these movies. Real life does not naturally come with back up.
Which is why, of course, we need to back up our Apex applications ourselves. The question you will have to answer is this: should you back up your full application in one file or back it up page by page?
If the application you are building is a website that you publish to the internet then in many ways your challenge is an easy one. You will need a development environment, perhaps a test environment and a production environment. There's no reason for one environment to pollute any of the others.
Export your full application after each major code change and back it up wholly. Backing up apex applications page by page is complicated; there's no need to take unnecessary risks.
However if, like us, you are creating a huge system which spans multiple apex applications and hundreds of pages which you intend to sell to multiple customers who have divergent upgrade schedules and obliquely differing requirements, then the picture is slightly more complicated. Your application may never be in a state where you can export it fully. You will need to export each page and each shared component as each bit of development is completed.
Application Express's versioning capabilities are simplistic at best; you can version the application, but not the page. To keep a record of the various versions of your pages you will need to use the page comments.
If you choose to export your pages individually you will need to bear in mind that exporting the page does not export any shared components that you may use on that page. You'll need to do that separately. And, unfortunately, there's no built-in way to export application items. As usual, Apex forces you to dance a merry dance to achieve what you need.
Talking about dancing, you'll want to forget that "dance like no one's watching" nonsense. You don't code like no one's going to use your software, do you? This is 2011, my friend; and Youtube has a very long memory.
Oh, and if you've already built your application and need help exporting your individual pages the following procedure should help. You'll need to run it in SQL*Plus and have set up a directory.
/*
** This procedure will write out an apex page.
*/
create or replace procedure export_apex_pages( p_dir in varchar2, app_id number )
is
l_thePage htp.htbuf_arr;
l_output utl_file.file_type;
l_lines number default 999999999;
vWorkspace number;
vLength number := 0;
vFileName varchar2(100);
begin
-- Find out what the workspace is.
for i in (select workspace_id
from apex_applications
where application_id = app_id) loop
vWorkspace := i.workspace_id;
end loop;
-- Loop through all the pages for the application.
for j in (select page_id
from apex_application_pages
where application_id = app_id ) loop
-- Determine the file name.
vFileName := 'f'||app_id||'_page_'||j.page_id||'.sql';
vLength := 0;
OWA.num_cgi_vars := 0;
apex_util.export_application_page(p_application_id=>app_id,p_workspace_id=>vWorkspace, p_page_id=>j.page_id);
l_output := utl_file.fopen( p_dir, vFileName, 'w' );
l_lines := 99999999;
owa.get_page( l_thePage, l_lines );
for i in 1 .. l_lines loop
utl_file.put( l_output, l_thePage(i) );
/*
** We need to calculate the length cos we can only write up to 32k.
** If it starts getting close to that we need to close the file, and then reopen it.
*/
vLength := vLength + length(l_thePage(i));
if vLength > 30000 then -- it's getting close to our limit. Look for a point where we can close it.
if l_thePage(i) like '%'||chr(10) then -- the line ends with a page return. Perfect place to close and reopen the file.
vLength := 0;
utl_file.fclose(l_output);
l_output := utl_file.fopen(p_dir,vFileName,'A');
end if;
end if;
end loop;
utl_file.fclose( l_output );
end loop;
end export_apex_pages;
/