Thursday, 14 August 2014

Getting the instance URL

To get the instance URL of your APEX environment, it is not too uncommon to use OWA_UTIL.GET_CGI_ENV function calls, similar to the below block:

declare
    l_protocol varchar2(5);
    l_host varchar2(150);
    l_script varchar2(15);
    
    l_instance_url varchar2(200);
begin


    l_protocol      := owa_util.get_cgi_env('REQUEST_PROTOCOL');
    l_host          := owa_util.get_cgi_env('HTTP_HOST');
    l_script        := owa_util.get_cgi_env('SCRIPT_NAME');
    
    l_instance_url := l_protocol;
    l_instance_url := l_instance_url || '://';
    l_instance_url := l_instance_url || l_host;
    l_instance_url := l_instance_url || l_script;
    l_instance_url := l_instance_url || '/';
    
    dbms_output.put_line(l_instance_url);
  end;  

Something you might not know, is that there are a couple of helper functions in the APEX API that allow you to do exactly that.

Option 1: APEX_UTIL.HOST_URL('SCRIPT')

Reference: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_util.htm#AEAPI2312

Option 2: APEX_MAIL.GET_INSTANCE_URL()

Reference: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_mail.htm#AEAPI29399

Option 2 seems to work even without being in the context of a HTTP session, but as per the docs: "This function requires that the instance setting Application Express Instance URL for emails is set."

Wednesday, 6 August 2014

APEX5 EA APEX_ZIP usage

I recently saw a link to a presentation that made mention of APEX_ZIP for the upcoming release of APEX (Version 5). Currently in early adopter, you can have a play around. So just thought I'd go and have a bit more of a play to see the possiblities.

On the presentation, and specifically for extracting files the sample code is:

declare
    l_zip_file  BLOB;
    l_unzipped_file     BLOB;
    l_files             apex_zip.t_files;
begin

    select file_content
    into l_zip_file
    from my_zip_files
    where file_name = :P13_file_name;
    
    l_files := apex_zip.get_files(p_zipped_blob => l_zip_file);
    
    for i in 1..l_files.COUNT
    LOOP
        l_unzipped_file := apex_zip.get_file_content(p_zipped_blob      => l_zip_file,
                                                     p_file_name        => l_files(i));
                                                     
        insert into my_files (file_name, file_content)
        values (l_files(i), l_unzipped_file);
    END LOOP;

end;


With a bit of looking around, we can soon find that APEX_ZIP refers to WWV_FLOW_ZIP, and we can grab the package specification by querying all_source. It seems that the package is derived from that code originally shared on the OTN forums here: https://community.oracle.com/message/4509996

Side note: If the zip file contains folders, you may need to get familiar with string functions to weed out only the folders you are interested in.

The other common usage would of course be creating a zip:

declare
    l_output_zip BLOB;
    l_filename varchar2(200) := 'newzip.zip';
begin

    for file in (
        select filename, contents
        from unzipped_files
    )
    LOOP
    
        apex_zip.add_file(
            p_zipped_blob       => l_output_zip
          , p_file_name         => file.filename
          , p_content           => file.contents
        );
    
    END LOOP;
    
    apex_zip.finish(
        p_zipped_blob           => l_output_zip
    );
    
    insert into zipped_files (mime_type, filename, contents) 
        values ('application/zip', l_filename, l_output_zip);


end;

If you wanted to put files in a specific folder, you just need to prepend the file name with the folder name and a forward slash: 'folder/' || file.filename

nb: it is essential to call apex_zip.finish once all files have been added