New Web Services Integration White Paper and Sample Application on OTN

Late last year I promised a white paper and sample application on integrating Application Express and BI Publisher through Web services. Well five months later (hey, that's less than 1/2 a year), it is now available on the Application Express Web Services Integration page on OTN. The sample application allows you to login to a BI Publisher instance as a BI Publisher defined user, browse the folders and reports available to that user, and provides the ability to download the report all from within the Application Express application. The white paper describes in detail how to build the application using PL/SQL and the flex_ws_api package. I hope you find it useful.

20 cents an hour! Who's got that kind of dough?

In these trying economic times, I thought it would be prudent to try to save some money on the cost of my Cloud experiment. (Although a colleague of mine pointed out that my actions were "anti-stimulative.") I wanted to downsize my machine to the 10 cent per hour machine described as:

Small Instance (Default) 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform

I didn't want to have to start from scratch; I wanted to keep the database and all the applications that I installed intact. But how could I do that? I knew full well that once I shut my instance down everything was gone. This is where the beauty of Amazon's Elastic Block Storage (EBS) comes in. It allows you to create persistent volumes that you can then attach, format, and mount on an EC2 instance. If you shutdown the EC2 instance for any reason your EBS volume persists and you can then mount it on another EC2 instance.

EBS has its own charges and I estimated it would cost me about $18 per month for my 10GB volume. But I am saving about $72 month by decreasing my hourly costs by 10 cents, so my net savings will be more than $50 a month. Not bad.

So the idea is that I create an EBS volume, move all the database files necessary to run the database to that volume, and then start a database using those data files on the cheaper machine. With some help from my friends at the Oracle Cloud Computing Center, I was able to do just that and I have detailed the steps below.

First I needed to create my EBS volume. This is very simple using Elasticfox and detailed in the Elasticfox Getting Started Guide. Click on the Volumes and Snapshots tab and then the create icon. Enter the size in GB (I chose 10) and also choose an availability zone. This is important since you can only attach EBS volumes in the same availability zone as your EC2 instance. My instance was in the us-east-1b zone so I chose that.

Now that I have the EBS volume I attached it to my instance by going to the Instances tab in Elasticfox, right clicking on my instance, and choose Attach an EBS volume. I gave it a device name of /dev/sdf1. Next I need to format the volume as a file system before I can use it so I issue (as root):

$ mkfs.ext3 /dev/sdf1

Figure 1, Create an EBS volume

Mount it to a temporary place, like:

$ mount /dev/sdf1 /mnt

My instance had a mount point of /u02 which contained all of the file necessary for my database including the datafiles, control files, redo log files, etc.

$ df -m

Filesystem 1M-blocks Used Available Use% Mounted on
/dev/sda1 9647 7116 2042 78% /
none 871 539 332 62% /dev/shm
/dev/sda2 342668 3000 322262 1% /u02

I copied all those files to the temporary mount point on /mnt. First I shutdown the database or the files will be unusable.

$ su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> exit
$ cp /u02/* /mnt/
$ exit #this will return me to the root user

Finally I un-mounted the current /u02 mount point and remount /dev/sdf1 to /u02. I only did this to prove I can start the database using the EBS volume with these data files. If I can't start the database using this EBS volume mounted as /u02, I don't want to proceed until I can.

$ umount /dev/sda2
$ umount /dev/sdf1
$ mount /dev/sdf1 /u02
$ su - oracle
$ sqlplus / as sysdba
SQL> startup

In my case, the database started up so I did a shutdown immediate. I un-mounted /dev/sdf1 and then detached the EBS volume from this instance. I kept this instance available until I successfully started the database on the new smaller instance.

SQL> shutdown immediate
SQL> exit
$ exit
$ umount /dev/sdf1

With the database shutdown and the EBS volume detached, I created a snapshot of the volume. The snapshot is a point-in-time backup of the EBS volume and it is stored using Amazon's S3. Once I have a snapshot, I can create new EBS volumes based on that snapshot. This gives me the flexibility of shutting down all instances and deleting my EBS volume, and then I only pay S3 storage charges. I can create a new EBS volume based on this snapshot sometime in the future, start an EC2 instance, attach the EBS volume and I am good to go.

Figure 2, Create a snapshot of the EBS volume

Next, I started a new instance using the same 11gR1 32 bit AMI on a smaller instance with Elasticfox. It was important that I chose the same availability zone as my EBS volume, us-east-1b.

Figure 3, Launch the smaller EC2 instance

I connected to the instance using Putty and chose "No" when asked if I wanted to create a database at this time. This gave me an instance with the Oracle software installed in an Oracle home, but without a database. Now for the magic. I created a /u02 mount point under /, attached the EBS volume to this instance as /dev/sdf1, and then mounted it to /u02. To start the database on this instance I also needed to set my ORACLE_SID environment variable and create symlinks in $ORACLE_HOME/dbs to the spfile and password file in the admin directory on /u02.

$ mkdir /u02
$ mount /dev/sdf1 /u02
$ su – oracle
$ export ORACLE_SID=orcl
$ cd $ORACLE_HOME/dbs
$ ln -s /u02/admin/orcl/dbs/spfileorcl.ora spfileorcl.ora
$ ln -s /u02/admin/orcl/dbs/orapworcl orapworcl
$ sqlplus / as sysdba
SQL> startup

And my database started on the new 10 cent/hour machine!

Test Drive Oracle Application Express 3.2 for 60 cents (USD)

Would you like to kick the tires of Oracle Application Express 3.2 on your own instance but do not have the spare hardware? Do you have 60 cents (USD)? Read on.

Amazon has a service called the Elastic Compute Cloud (EC2) where you can fire up virtual machines in the cloud on a whim. They have partnered with other software vendors to provide images with pre-configured software. Oracle is one of those vendors and you can read about the offerings at the Oracle Cloud Computing Center on OTN. Oracle has an image which contains Enterprise Linux and Oracle Database 11g R1.

The purpose of this post is to describe how I started an 11g R1 instance in the cloud, upgraded it to Application Express 3.2, and then completed the "Converting Your Oracle Forms Applications to Application Express 3.2" and "Utilizing Improved Security Enhancements in Application Express 3.2" Oracle By Example tutorials (OBEs). If you can spare 60 cents you may wish to try this yourself at home.

First, you need to get your AWS account in order. Sign up for AWS at the previous link and then sign up for EC2. Review the EC2 pricing and then establish a payment method. If you already are a customer of Amazon.com you can choose one of the credit cards you have on file or establish a new one. Unfortunately, there is no infrastructure in place for you to insert two quarters and a dime for this test drive.

Now you are going to need to get some software to startup an instance, manage it, and transfer files to it. Get the Elasticfox Firefox plug-in and review the Getting Started Guide. The Getting Started Guide will give detailed instructions on associating your AWS EC2 account with Elasticfox and also creating a KeyPair that you can use to identify yourself when connecting to the machine. You need to download Putty & PuttyGen to connect to your instance via SSH. You should download WinSCP to transfer files to your instance. Finally you need to download Application Express 3.2.

The keypair that you created when reviewing the Elasticfox Getting Started Guide needs to be converted to a format that can be used by Putty and WinSCP. You can use PuttyGen to create this key. Simply start PuttyGen, click load and browse to find the .pem keypair file you created with Elasticfox, then click Save private key to save it as a .ppk file.

Figure 1, Creating a .ppk from your .pem with PuttyGen.

You are ready to view Deploying Oracle Database in the Cloud viewlet that is available on the Oracle Cloud Computing Center page on OTN. The viewlet will show you how to find and start image ami-cecb2fa7 which is 32bit Enterprise Linux with 11g. It will take you through creating and configuring the database. I chose to start a c1.medium instance type which costs 20 cents/hr and is described as:

High-CPU Medium Instance 1.7 GB of memory, 5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each), 350 GB of instance storage, 32-bit platform

Figure 2, Instance Details

You manage the instance by connecting via SSH and you can use Putty as the viewlet demonstrated. Before you can connect to the machine on port 22 from your computer you will have to specifically allow it from your IP address. In Elasticfox first identify the Security Group used to start the instance. It is most likely called "default" unless you added another Security Group. Click the Security Group tab, highlight default and then click the green checkmark under the Group Permissions pane to add a permission. You can either allow a specific host or a network range. Allow your host to connect on port 22 and then create another permission to allow anyone to connect on port 8080 wich is the default port that EPG will be listening for HTTP requests. You choose Network and allow 0.0.0.0/0.

Figure 3, Creating a Security Group

Before logging in to Oracle Application Express on this instance, I had to change the password for the Application Express ADMIN account by following these steps (which changed the password to oracle):

su - oracle
sqlplus / as sysdba
SQL> @?/apex/apxxepwd oracle

Login to Oracle Application Express and create a workspace.

Figure 4, Create a Workspace

Now for the fun part. Start WinSCP and connect to your instance and copy apex_3.2.zip there.

Figure 5, Copy apex_3.2.zip to your instance

Return to your Putty terminal session connected to your instance and install Oracle Application Express 3.2:

su - oracle
unzip apex_3.2.zip
cd apex
sqlplus / as sysdba
SQL>@apexins SYSAUX SYSAUX TEMP /i/
SQL>@apxldimg /home/oracle

Follow Joel's blog post on making Application Express run faster. You are ready to complete the Converting Your Oracle Forms Applications to Application Express 3.2 and Utilizing Improved Security Enhancements in Application Express 3.2 Oracle by Example (OBE) tutorials. An easy way to get the files for the Forms OBE to your machine is to run the following command in your terminal session:

wget http://www.oracle.com/technology/obe/apex32/files/forms_conversion.zip

Figure 6, Application Express 3.2 in the Cloud!

So there you have it. Test drive your own instance of Oracle Application Express 3.2 for around 60 cents (USD). For a limited time (until my manger finds out), you can try out my cloud instance using the following application linked below. I have configured my cloud instance to use the APEX Listener, which is a Java based HTTP listener that should release with the next version of Oracle Application Express.

http://ec2-174-129-117-40.compute-1.amazonaws.com:9999/apex/f?p=104:1

RMOUG, Kaleidoscope 2009, Slight Change to flex_ws_api

I know that I am horribly overdue for a post. I was out at the RMOUG Training Days last week and presented on Integrating Web Services and Application Express. The presentation should be linked from the referenced page soon. Unfortunately I only had 60 minutes to present 90 minutes worth of material. I presented on integrating Web services created from JDeveloper, XDB Native Web Services, BPEL Web services and finally integration with Oracle Content Server (formerly Stellent). The presentation was supposed to end with a discussion of debugging techniques using a variety of tools such as SOAPUI, ProxyTrace and XMLSpy.

Luckily, I will be presenting at the ODTUG Kaleidoscope 2009 in Monterey California in June. I have 90 minutes so I will be able to get to the debugging topics. Please join me if you can swing it.

Lastly, I have made a couple of minor tweeks to flex_ws_api. The changes allow for a more graceful response from parse_xml and parse_xml_clob if the node is not found using the supplied Xpath expression. It now simply returns null.

More Changes to flex_ws_api

I added three new functions to the flex_ws_api. The most significant is the new make_request function which is just like the procedure with the same name but returns an XML type instead of storing the results in a collection. It became apparent to me that you may want to make a request without storing the results in a collection. In my particular case, I was working on a sample application for BiPublisher.

BiPublisher has a service called the PublicReportService. Tyler Muth has blogged about using the scheduleReport operation to schedule a report to be run and delivered vi email or ftp from an Application Express interface. But what if you want the report right now and allow for downloading it directly from that application? There is a runReport operation of the service to allow just that. It returns the report base64 encoded, and you just have to write a process to convert that to a BLOB and download it. No problem!

I set out to build a sample application that would do just that. I noticed that there was an operation called validateLogin so I thought maybe I could create a custom authentication scheme using flex_ws_api to validate the user by making a call to the PublicReportService. That is when I realized that storing the result in a collection won't do any good if the user does not have a valid session yet. So there was a need for a function to return the results as an XMLType. I also added two functions to parse out the results of the XMLType, parse_xml and parse_xml_clob.

I was successful in building the custom authentication scheme after I added these functions. I was also successful in building the rest of the application. You login using the validateLogin operation, view and traverse folders from the BiPublisher repository using the getFolderContents operation, and click on links to download the report using the runReport operation, all in an Application Express application. I plan to make the sample application available on OTN along with a white paper on how it was built. Stay tuned.

The New flex_ws_api and SOAP 1.2 Example

Before I make wild claims like the one I made on October 22, where I said "flex_ws_api now supports SOAP 1.2" I probably should have tested the code against a SOAP 1.2 service. I can now say that I have tested it with a SOAP 1.2 service and I learned a couple of things.

Firstly, when setting the content-type header, the action must appear after the charset declaration or you will get an unsupported media type error from the service. Secondly, there may be an occasion where you have base 64 encoded character data and you want to convert that into binary data, for example, providing the ability to download a document.

Based on these two findings, I have updated the flex_ws_api code available below to set the content-type header properly and added a function called clobbase642blob that takes in a base64 encoded clob and returns a blob.

The services that I tested with are the Oracle Beehive web services. Oracle Beehive "is a collaborative environment built on a unique model that combines the various communication and coordination services into a comprehensive platform." You can test these services via an HTTP interface which came in very handy when I needed to know the structure of the SOAP 1.2 envelope that each service expected. The services that I interacted with were the WorkspaceService to get a list of folders in a workspace and the DocumentService to get a list of documents in a particular folder.

The application I built was a simple three page application. The first page showed a list of folders in the workspaces that the logged in user belongs to. Each folder linked to the second page which would then show the contents of that folder. Each document linked to the third page which has a before header process that downloads the document.

Start by creating an Application Express application with one blank page. Make sure you have compiled the new flex_ws_api in the schema associated with this workspace. Call the blank page something like Get Folders. The first thing to do is to create a before header process on the page to use the flex_ws_api and call the WorkspaceService, operation GetWorkspaces based on the currently logged in user. See the code listing.

Code Listing 1, Call GetWorkspaces Before Header Process on Page 1

declare
  l_env clob;
begin
  l_env := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><soap:Header><wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://www.w3.org/2003/05/soap-envelope" soap:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:Username>'||:APP_USER||'</wsse:Username><wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Welcome1</wsse:Password></wsse:UsernameToken></wsse:Security></soap:Header>
    <soap:Body xmlns:ns1="http://oracle.bee.platform.webservice/">
        <ns1:GetWorkspaces>
            <ns1:uID xmlns:ns2="http://www.w3.org/2001/XMLSchema-instance" ns2:nil="true"/>
            <ns1:wspType>TEAM</ns1:wspType>
            <ns1:wspFilter xmlns:ns3="http://www.w3.org/2001/XMLSchema-instance" ns3:nil="true"/>
        </ns1:GetWorkspaces>
    </soap:Body>
</soap:Envelope>';

  flex_ws_api.make_request(
    p_url => 'http://localhost:7777/ws/WorkspaceService',
    p_version => '1.2',
    p_collection_name => 'GETWORKSPACES_RESPONSE',
    p_envelope => l_env );
end;

One thing to note about the process above is that we are passing the username in the envelope using :APP_USER to reference the currently logged in user. Whatever authentication method your application uses will have to also be a user in the Beehive world. The second thing to note is that the password is hardcoded to Welcome1. You will obviously need to change this to be dynamic and based on the user. Finally, we are storing the response from the Beehive service in an Application Express collection called GETWORKSPACES_RESPONSE.

Now that the page has a process to call the web service you create a report region to show the folders in the workspace for this particular user based on the web service result. You do this by writing a query that first casts the clob001 column in the Application Express collection to an xmltype and then use the table command to shred the document. See the code listing.

Code Listing 2, Report on Result to Show Folders, Page 1

select wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/id','xmlns="http://oracle.bee.platform.webservice/"')) "id"
     , extractValue(value(t),'/*/name','xmlns="http://oracle.bee.platform.webservice/"') "name"
     , extractValue(value(t),'/*/description','xmlns="http://oracle.bee.platform.webservice/"') "description"
 from wwv_flow_collections c,
      table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetWorkspacesResponse/return/libraryIDList','xmlns="http://oracle.bee.platform.webservice/"'))) t
where c.collection_name = 'GETWORKSPACES_RESPONSE'

One thing to note is that the id column might contain colons and slashes, so the report calls wwv_flow_utilities.url_encode2 to encode these characters. You will modify this report slightly to hide the id column and then link the folder name to page 2 which makes the encoding trick necessary. You first create page two.

Page two of the application calls the DocumentService and the GetDocumentsInFolder operation based on the value of a hidden item to hold the ID of the folder.

First create a new blank page (page 2). Add a before header process that uses the flex_ws_api to call the DocumentService as in the following code listing.

Code Listing 3, Call GetDocumentsInFolder Before Header Process, Page 2

declare
  l_env clob;
begin
  l_env := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><soap:Header><wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://www.w3.org/2003/05/soap-envelope" soap:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"><wsse:Username>'||:APP_USER||'</wsse:Username><wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">Welcome1</wsse:Password></wsse:UsernameToken></wsse:Security></soap:Header>
    <soap:Body xmlns:ns1="http://oracle.bee.platform.webservice/">
        <ns1:GetDocumentsInFolder>
            <ns1:folderID>
                <ns1:type xmlns:ns2="http://www.w3.org/2001/XMLSchema-instance" ns2:nil="true"/>
                <ns1:description xmlns:ns3="http://www.w3.org/2001/XMLSchema-instance" ns3:nil="true"/>
                <ns1:name xmlns:ns4="http://www.w3.org/2001/XMLSchema-instance" ns4:nil="true"/>
                <ns1:id>'||wwv_flow_utilities.url_decode2(:P2_FOLDER_ID)||'</ns1:id>
            </ns1:folderID>
            <ns1:docFilter xmlns:ns5="http://www.w3.org/2001/XMLSchema-instance" ns5:nil="true"/>
        </ns1:GetDocumentsInFolder>
    </soap:Body>
</soap:Envelope>';

  flex_ws_api.make_request(
    p_url => 'http://localhost:7777/ws/DocumentService',
    p_version => '1.2',
    p_collection_name => 'GETDOCUMENTS_RESPONSE',
    p_envelope => l_env );
end;

Note that the process calls wwv_flow_utilities.url_decode2 to decode the folder id, which we needed to encode on the prior page, because it was being passed as part of the link. The response is stored in a collection called GETDOCUMENTS_RESPONSE.

The next thing to do is the create a report region that reports on the results of the web service much like page one. See the code listing.

Code Listing 4, Report on GetDocumentsInFolder Result to List Documents

select wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/contentID/id','xmlns="http://oracle.bee.platform.webservice/"')) "id"
     , extractValue(value(t),'/*/content/name','xmlns="http://oracle.bee.platform.webservice/"') "name"
     , extractValue(value(t),'/*/mimeMultipartType','xmlns="http://oracle.bee.platform.webservice/"') "type"
 from wwv_flow_collections c,
      table(xmlsequence(extract(xmltype.createxml(c.clob001),'//GetDocumentsInFolderResponse/return','xmlns="http://oracle.bee.platform.webservice/"'))) t
where c.collection_name = 'GETDOCUMENTS_RESPONSE'

Finally, create a hidden item on page two called P2_FOLDER_ID for the folder id.

Now we return to page one and edit the report region to link the folder name to page two, populating the P2_FOLDER_ID item. To accomplish this:

1. Click the Report link next to the Folders region


2. Uncheck the Show check box corresponding to the id column


3. Click the edit icon next to the name column


4. Scroll down to the Column Link region


5. Click the [name] quick link below the Link Text field


6. Choose Page in this Application from the Target list


7. Enter 2 in the Page field


8. Choose P2_FOLDER_ID for Item 1 from the pop-up list


9. Choose #id# for Value from the pop-up list


10. Click Apply Changes

Figure 1, Column Link Attributes for Folder Name

At this point, you should be able to run the application, see a list of folders, click on a folder, and see the contents of the folder.

Figure 2, List of Folders

Figure 3, List of Contents of Folder

The response from calling the DocumentService GetDocumentsInFolder operation is not only a listing of the documents there but also the data of the documents encoded in base64. Luckily, we can now convert the base64 clob into a blob which allows us to create another page in the application which will download the document.

Create a new blank page (3) in the application. Create an empty HTML region to hold a hidden item for the document ID. Create a hidden item on page 3 called P3_DOCUMENT_ID.

Now create a before header process that will parse the response of GetDocumentsInFolder and determine the size, mime type, name and base64 encoded data. Convert the base64 encoded data to a blob, and then use wpg_docload.download_file procedure to download the file. Use the code in the following listing to create the process.

Code listing 5, Download Document Before Header Process, Page 3

declare
  l_mime      varchar2(48);
  l_name      varchar2(4000);
  l_base64    clob;
  l_blob      blob;
  l_size      varchar2(255);
begin
  l_size := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:size/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_mime := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:mediaType/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_name := flex_ws_api.parse_response('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:name/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_base64 := flex_ws_api.parse_response_clob('GETDOCUMENTS_RESPONSE','//ns0:GetDocumentsInFolderResponse/ns0:return[ns0:contentID/ns0:id/text()="'||wwv_flow_utilities.url_decode2(:P3_DOCUMENT_ID)||'"]/ns0:content/ns0:data/text()','xmlns:ns0="http://oracle.bee.platform.webservice/"');

  l_blob := flex_ws_api.clobbase642blob(l_base64);

 
  htp.init;

  owa_util.mime_header( nvl(l_mime,'application/octet'), FALSE );
  htp.p('Content-length: '||l_size);
  htp.p('Content-Disposition:  attachment; filename="'||replace(replace(l_name,chr(10),null),chr(13),null)||'"');
  owa_util.http_header_close;
  wpg_docload.download_file( l_blob );
 
  apex_application.g_unrecoverable_error := true;

end;

The final step is to modify the report attributes of the report region on page 2, hide the id column and link the name column to page 3, populating the P3_DOCUMENT_ID item like you did on page one for the link from the folder name. Once you have completed this step, you will have a simple three page application the shows a list of folders of workspaces that the currently logged in user belongs to, shows the contents of the folders when click on, and then downloads the document when clicked on.

flex_ws_api Now Supports SOAP 1.2

I recently became aware that not only is the message and envelope format of SOAP 1.2 different from SOAP 1.1, but also the Content-Type HTTP header as well. If you send the Content-Type of a SOAP 1.1 message, text/xml to a SOAP 1.2 document, you will get a message back, formatted in SOAP 1.1 saying that there is a version mismatch. SOAP 1.2 expects a Content-Type HTTP Header like the following (note the SOAPAction is also on this line and shortened to action):

Content-Type: application/soap+xml; action=initiate; charset="utf-8"

The flex_ws_api now accepts a p_version parameter which is defaulted to '1.1', but if '1.2' is passed, the proper Content-Type header will be sent for a SOAP 1.2 message.