Figure 1: The Portal Interface to Copy folders
In this article we show you step by step how we created a portlet that enable users to copy a folder and its sub folders anywhere, including across content areas. We also show you a very similar portlet that lets users move a folder and its sub folders anywhere. These portlets make use of the undocumented part of the Portal API. They are really not very complex once you know what calls to make and what tables to look at.
Step 1: Creating an application in Portal
The first step is to create a place in Portal from which we can call our Copy and Move utility. There are several ways to do this in Portal. We chose to create an application with 2 forms. The first form is used to call the copy utility. The second form is used for the move utility. Each form captures values for two parameters required by our utilities: the source folder and the destination folder. Both forms also make use of a list-of-value module in order to facilitate folder selection for the user. All components are illustrated in Figure 2.
Figure 2: The components of the Copy and Move Utilities
The list of value component is defined as a popup format and uses this hierarchical query:
select LPAD(title,length(title)+(level-1)*6,'.'), name||'::'||to_char(id)||'::'||to_char(siteid)||'::'||language
where siteid >10 -- only user defined folders
start with parentid = 0
connect by parentid = prior id and
siteid = prior siteid and
language = prior languag
The table wwv_corners is owned by the Portal30 schema in the database, unless the person who installed Portal on your server used a different schema. Wwv_corners contains all folder information for all folders defined in Portal, including the internal folders that come with the product. Folders constitute a hierarchy: a folder may contain other folders that in turn may contain others, etc.
There are a few tricks to the table wwv_corners. It also contains content area information. A content area is really a top level folder. All content area folders have their ID set to 1 and the parent folder ID set to 0. Yes, ID is NOT the primary key. Incidentally you will find that there is no folder record with ID equal to 0 so you could not have a foreign key from parentid to id. Each content area corresponds to one and only one site referred to by the column siteid. Site information can be found in table wwsbr_sites$.
The select statement used by a Portal List-of-Values is always formatted in the same way. Only 2 columns are taken into account. The first column provides the values displayed as a picking list to the users. The second value is the one returned to the module calling the list of value. In our case you will notice that the first column presents a hierarchy of folder titles. The hierarchy is constructed with the 'start with' and the 'connect by' clauses of the select statement. The second column is a concatenation of four columns in the wwv_corners table: name (Folder Name), id (Corner ID), SiteID (ID of the content area to which the folder belongs) and Language. (id, siteid, language) compose the primary key of the wwv_corners table. They truly identify a given Portal folder in a unique way, across content area and at any level of the folder hierarchy.
The resulting list of value is illustrated in Figure 3.
Step 2: Creating the package called by the interface
The two forms COPY_F and MOVE_F are based on the two procedures documented
in Listing A: copy_f and move_f. We used the Portal wizard to create a
standard tabular form based on the procedure that you pick. The wizard
creates a field for each parameter of the procedures chosen. You can then
give each field a user-friendly prompt and associate them with the list-of-value
module defined above.
procedure move_f (p_f_source
p_f_dest in varchar2
portality_db.move_f (p_f_source, p_f_dest);
end; -- procedure move_f
end; -- package body portality_i
The procedures on which the parameter forms are based are only stubs owned by the Portal schema that call the real package that performs the actual work.
Once the three modules are defined within the application, it is important to publish the forms 'Copy_f' and 'Move_f' as Portlets so that you can use them within a page. This can be done by first checking that the application is exposed as a provider: Click on link 'Grant Access' for the application, and check the box 'Expose as Provider'. Then for forms 'Copy_f' and 'Move_f', click on their link 'Grant Access' and check the box 'Publish to Portal'.
Step 3: Coding the Copy
Now we are in pure PL/SQL territory. This section is not intended to teach PL/SQL to the reader. PL/SQL is a prerequisite for any Portal API-based utility programming. What we will reveal here is a number of undocumented calls that you can make to the Portal API. This API is also used by the Portal product itself, using it is a very safe way to create and manipulate content. What is not safe at all is to try to issue direct inserts and updates to the Portal database tables.
For the sake of brevity, the code presented in the continuation of this article has been simplified. We removed auditing, debugging, validation, presentation and exception processing because it does take about half of the total number of lines and does not provide readers any information that they do not have already. We also removed most comments because this article will explain the code much better than the comments do. The complete code is available from the author.
In Step 1 we explained how we encoded a folder unique ID composed of 4 parts: folder name, id, siteid and language. The separator we use between each part is '::'. So in Listing B, the parameters p_f_source and p_f_dest would receive values like 'Admin::5210::34::us' which designates the Admin folder whose internal ID is 5210, belongs to Content Area 34 and whose language is 'English US'. You can get a list of the 47 languages available in a Portal 3.0.6 standard install from http://www.iherve.com.
So in Listing B we used a great number of lines (15-58) to do a simple thing: split up 2 parameters into 8 variables. The snip_between function that we use is one of several re-usable components that we have grouped in a 'util' package. Snip_between is using substr and instr to crop text between the nth occurance of a certain tag and the nth occurance of another tag. You can also get this code from the author who will soon start working on a split function like PERL's.
Once we have parsed the values for l_f_source_name, l_f_source_id, l_f_source_siteid, l_f_source_language, l_f_dest_name, l_f_dest_id, l_f_dest_siteid and l_f_dest_language we validate them and throw exceptions if necessary. We chose not to show this mundane code to keep the readers focused on the meat of this article.
In Listing B, lines 61 to 65 are a little puzzling and deserve a detailed explanation. They are a workaround a feature of the Portal 3.0.6. API. It turns out that the API wwv_cornerdb.copy procedure requires the name of the folder copy to be different from the name of the original folder in only one case: when you choose the root folder level (ID=1) of a content area for the destination. This is the error that Portal will report if that rule is violated:
ERROR at line 1:
ORA-06512: at "PORTAL30.WWSBR_STDERR", line 437
ORA-06512: at "PORTAL30.WWV_CORNERDB", line 724
ORA-06512: at line 4
So the workaround is simple: give a temporary name to the copy folder, run the API copy (lines 67-77) and update the name afterwards (lines 80-84).
The API function wwv_cornerdb.copy has other parameters that we do not use. You may extend the functionality presented here by making use of p_newtitle to create a new title for the copy folder. p_copylayout can be used to prevent the copy of the source folder layout. Setting p_copychildcorners to 0 prevents the copy for the sub-folders of the source folder. You can also set p_copyitems to 0 so that only folders are copied, not the items they contain. Finally, the access rights are taken care of by p_copyaccess.
So with minimal programming we have put together a copy_f procedure that can copy a folder at any level of the hierarchy, including the root level, anywhere within that same hierarchy, even across content areas. This procedure has limitations though. When copying across content areas, copy_f does not copy categories, custom folder types and custom item types and their attributes. The only way to make this work is to create these categories, folder type, item types and attributes as Shared Objects. We could also enhance copy_f and move_f to do this for you.
Step 4: Coding the Move
The move_f procedure is very similar to the copy_f procedure explained in step 3. It basically copies the source to the destination and then deletes the source. Only the delete part is a little bit more complex and dangerous.
Lines 17-22 are the same workaround as we explained in the previous step. Lines 24-34 run the Portal API standard copy routine and lines 35-41 conclude the workaround started in 17-22.
Once the content is moved, lines 43 to 52 take care of the deletion of the source folder. If the source folder was the root folder of a given content area then we choose not to delete the whole content area because that would also remove categories, navigation bars, perspectives, styles and custom types. Instead we only delete all the folders within that content area. This is done in lines 44 to 45 by calling our generic procedure f_content_delete detailed in Listing D.
In Listing D, the cursor defined in lines 4-9 retrieves all folders within a given folder. The next cursor in lines 10-15 retrieves all items in that same folder. Lines 18-24 make use of the first cursor to delete each folder's sub-folders and their own sub-folders. Lines 26-34 do the same thing with items unless the folder is a root level one in which case it will contain no items.
Step 5: Running the Portlets
So now that you have the elements necessary to copy and move folders across content areas, all that is left to do is present these utilities as portlets in a user friendly page and maybe even make that page a generic utility page and the default page of an administrative user. Or you could just run these 2 utilities from the Application Tab in Portal if you want them to remain a super-user module only.