For versioning reasons, in my case Subversion, I regularly have to create the .sql export files of the following APEX application components:
- the APEX application itself
- the pages that belong to the application
- the workspace in which the application is deployed
As you probably know, APEX offers these three capabilities built in. The default export functionality works perfectly for the application and workspace export. The sole problem I encountered was when I tried to find an efficient way to export each page within my application to a separate .sql file.
My first thought went to using the component export functionality in which I can select all pages and add them to the export. Unfortunately, the result of this export is one big .sql file… no good for Subversion.
Another option I read about was using the Java APEXExport or APEXExportSplitter tool which can be found in the APEX installation folder. I never tried out this option but it seems that it does not offer the functionality I am looking for (correct me if I am wrong).
My last resort was to use the export functionality on page definition level. A time-consuming and impractical solution, especially when your application counts more than, let’s say, a hundred pages.
That’s why I came up with something completely else. I figured out that the wwv_flow_utilities package includes the following three functions:
- export_application_to_clob
- export_page_to_clob
- export_workspace_to_clob
P.S. Packages starting with wwv are internal packages and are subject to change in future versions of APEX. I am currently using version 4.1.1.
The above three functions return a CLOB. This took me thinking since it’s pretty easy to write the content of a CLOB to a file in an Oracle directory. Simply use the procedure below to do so.
procedure pro_clob_to_file(p_clob in clob , p_file_name in varchar2 , p_ora_dir in varchar2) as l_clob_length number; l_start number := 1; byte_length number := 32767; l_buffer varchar2(32767); l_output utl_file.file_type; begin l_clob_length := dbms_lob.getlength(p_clob); l_output := utl_file.fopen(upper(p_ora_dir) , p_file_name , 'wb' , 32767); while l_start < l_clob_length loop dbms_lob.read(p_clob, byte_length, l_start, l_buffer); utl_file.put_raw(l_output, utl_raw.cast_to_raw(l_buffer), true); l_start := l_start + byte_length; end loop; utl_file.fclose(l_output); exception when others then if utl_file.is_open(l_output) then utl_file.fclose(l_output); end if; raise; end pro_clob_to_file;
Update on 21-08-2013: or simply use dbms_xslprocessor.clob2file.
Next, I examined the parameters of the to_clob export functions and wrapped them into custom procedures. Each procedure writes a single APEX application, page or workspace export file to an Oracle directory. I parameterized the procedures so you can call the functions according to your own requirements.
Application export
procedure pro_export_app(p_app_id in number , p_exp_ir_pub_rep in varchar2 , p_exp_ir_priv_rep in varchar2 , p_exp_ir_notif in varchar2 , p_ora_dir in varchar2) as l_clob clob; l_file_name varchar2(255); begin -- example: app117_export.sql l_file_name := 'app' || p_app_id || '_export.sql'; l_clob := wwv_flow_utilities.export_application_to_clob(p_application_id => p_app_id , p_export_ir_public_reports => p_exp_ir_pub_rep , p_export_ir_private_reports => p_exp_ir_priv_rep , p_export_ir_notifications => p_exp_ir_notif); pro_clob_to_file(l_clob , l_file_name , p_ora_dir); end pro_export_app;
Page export
procedure pro_export_app_page(p_app_id in number , p_page_id in number , p_ora_dir in varchar2) as l_clob clob; l_file_name varchar2(255); begin -- example: app117_page40_export.sql l_file_name := 'app' || p_app_id || '_page' || p_page_id || '_export.sql'; l_clob := wwv_flow_utilities.export_page_to_clob(p_application_id => p_app_id , p_page_id => p_page_id); pro_clob_to_file(l_clob , l_file_name , p_ora_dir); end pro_export_app_page;
Workspace export
procedure pro_export_workspace(p_workspace_id in number , p_include_team_dev in boolean , p_ora_dir in varchar2) as l_clob clob; l_file_name varchar2(255); begin wwv_flow_api.set_security_group_id(p_workspace_id); -- set the apex_application.g_user global variable... -- the username you use as argument needs admin privileges in the workspace apex_custom_auth.set_user('admin'); -- example: workspace19238827392_export.sql l_file_name := 'workspace' || p_workspace_id || '_export.sql'; l_clob := wwv_flow_utilities.export_workspace_to_clob(p_workspace_id => p_workspace_id , p_include_team_development => p_include_team_dev); pro_clob_to_file(l_clob , l_file_name , p_ora_dir); end pro_export_workspace;
Put it all together
The last step is to create another procedure which accepts two parameters: an application ID and Oracle directory. This procedure calls the three above wrapper procedures and queries several APEX views to fetch the pages and workspace that belong to the specified application. This way it will automatically create the individual .sql export files in the specified Oracle directory. The code itself is quite self-explanatory.
procedure pro_full_export(p_app_id in number , p_ora_dir in varchar2) as l_workspace_id number; cursor c_app_pages is select page_id from apex_application_pages where application_id = p_app_id; begin -- export the application pro_export_app(p_app_id , 'N' , 'N' , 'N' , p_ora_dir); -- export all pages that belong to the application for r_app_page IN c_app_pages loop pro_export_app_page(p_app_id , r_app_page.page_id , p_ora_dir); end loop; -- get the workspace_id based on the application_id select workspace_id into l_workspace_id from apex_applications where application_id = p_app_id; -- export the workspace in which the application is deployed pro_export_workspace(l_workspace_id , true , p_ora_dir); end pro_full_export;
Finally, an example on how to call the pro_full_export procedure:
declare p_app_id number; p_ora_dir varchar2(200); begin p_app_id := 100; p_ora_dir := 'MY_ORA_DIR'; pck_my_package.pro_full_export(p_app_id => p_app_id , p_ora_dir => p_ora_dir); end;
I have tried to implement this code but, from down in the buts of the wrapped APEX package, I am getting this error stack:
ORA-20987: APEX – Application 101 not found logged in as database user JUPSHAW. – Contact your application administrator.
ORA-06512: at “APEX_040100.WWV_FLOW_ERROR”, line 752
ORA-06512: at “APEX_040100.WWV_FLOW_ERROR”, line 1064
ORA-06512: at “APEX_040100.WWV_FLOW_UTILITIES”, line 9013
ORA-06512: at “US_RISK_DEV.RISK_APEX_UTILITY”, line 32
ORA-06512: at line 10
Were there some particular set of grants that you made to the user who executed the code?
-Joe
LikeLike
Hi Joe. It seems that you figured it out yourself on the OTN forums. I’ll just post the link to your thread for other people who might encounter the same error message.
https://forums.oracle.com/forums/thread.jspa?messageID=10524597
LikeLike
Argh. It worked fine in the one DB. Now, I move it to a different DB and, within PL/SQL while executing, the select from WWV_FLOW_STEPS returns 0 rows. As the same user, from SQL*Plus, all rows are returned.
So, it appears there *was* some grant that is needed. I compare roles between the environment where this works and where it does not. I can’t seem to spot what’s missing. Yes, I know I must have figured it out at one time, and feel like a real dummy having to ask again but, it isn’t a role or object permission that is missing. I’ve compared these. It must be some other sort of grant (perhaps through a built-in APEX package???).
So, after all this time, I need to ask again, were there some particular set of grants that you made to the user who executed the code?
-Joe
LikeLike
Hey Joe,
I’ve tried out the above export functionality on another database (11gR2 with APEX 4.1.1.00.23) and identified an issue with the workspace export. The “pro_export_workspace” procedure constantly returns the following error: ORA-06502: PL/SQL: numeric or value error: NULL index table key value. Next to that, I haven’t experienced any problems with the application and page exports.
Anyhow, that’s not your problem (: I’m pretty sure you don’t need grants or privileges to get the above code working. However, I notice you’re using WWV_FLOW_STEPS instead of apex_application_pages. So I think you need the proper privileges to select from WWV_FLOW_STEPS. But, that doesn’t seem to be the problem since the select statement does not throw an error.
Furthermore, it is important to install the package in the same schema as the parsing schema of the APEX application that you want to export. Failing to do so results in a select from apex_application_pages that returns zero rows. However, I’m not sure if this is the case when you use WWV_FLOW_STEPS instead of apex_application_pages.
Well, I hope you’re able to fix your issue now.
Regards,
Nick
LikeLike
Nick, the issue is that, specifically, we want to connect as one user and dump out all APEX artifacts; workspaces, applications and pages, irrespective of the owning schema. I did find a work around but, I am a bit hesitant to post it here as I am afraid APEX Dev might shut down the “work around.” In a nutshell, I hacked apart the built-in view and removed the built-in check on security groups. In other words, we’re now pulling from a greatly paired down version of WWV_FLOW_STEPS.
The next version just really needs export capabilities that aren’t object-by-object. Seems that it would be fairly simple to implement. We’d settle for this being app by app but, as your original article pointed out, it just isn’t practical to do it page-by-page.
Thanks for taking the time to reply (and for your great blog!)
-Joe
LikeLike
This is a valuable post. Thanks!!
LikeLike