Portal 3.0. Moving Folders Across Content Areas with its API

By Hervé Deschamps, Oracle Corporation.

Portal has a very friendly user interface that lets you copy and move folders. This is illustrated in Figure 1. However that interface only lets you move and copy within the same content area. This is very frustrating for the users because they have a genuine need to re-use content from one area of their web site into another or to re-organize its structure. As of Portal 3.0.8 and earlier, users are forced to re-create the source content in the destination content area or duplicate the source content area and trim the excess. Not an ideal workaround, especially if they need to merge two content areas.

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
from wwv_corners
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.

Figure 3: Hierarchical List of Value

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.

Listing A

create or replace package body portality_i as
   procedure copy_f (p_f_source      in varchar2,
                     p_f_dest        in varchar2
                    ) is
   begin
      portality_db.copy_f (p_f_source, p_f_dest);
   end; -- procedure copy_f

   procedure move_f (p_f_source      in varchar2,
                     p_f_dest        in varchar2
                    ) is
   begin
      portality_db.move_f (p_f_source, p_f_dest);
   end; -- procedure move_f
end; -- package body portality_i
/

Code for the procedure called by the Portal Form Components

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.

Listing B

1     procedure copy_f (p_f_source in varchar2,
2                       p_f_dest   in varchar2
3                        ) is
4       l_f_source_name     varchar2(1000);
5       l_f_dest_name       varchar2(1000);
6       l_f_source_language varchar2(1000);
7       l_f_dest_language   varchar2(1000);
8       l_newname           varchar2(1000);
9       l_f_source_id       number;
10       l_f_source_siteid   number;
11       l_f_dest_id         number;
12       l_f_dest_siteid     number;
13
14     begin
15       -- Decompose the parameters
16       l_f_source_name   := util.snip_between
17            ( p_source => p_f_source,
18               p_begin => null,
19               p_end    => '::');
20       l_f_source_id     := util.tonumber
21            ( util.snip_between (p_source => p_f_source,
22                                  p_begin => '::',
23                                  p_end    => '::',
24                                  p_begin_occur => 1,
25                                  p_end_occur   => 2));
26       l_f_source_siteid := util.tonumber
27            (util.snip_between (p_source => p_f_source,
28                                p_begin => '::',
29                                p_end    => '::',
30                                p_begin_occur => 2,
31                                p_end_occur   => 3)
32                                );
33       l_f_source_language := util.snip_between
34            (p_source => p_f_source,
35             p_begin => '::',
36             p_end    => null,
37             p_begin_occur => 3);
38       l_f_dest_name   := util.snip_between
39            (p_source => p_f_dest,
40             p_begin => null,
41             p_end    => '::');
42       l_f_dest_id     := util.tonumber
43            (util.snip_between ( p_source => p_f_dest,
44                                 p_begin => '::',
45                                  p_end    => '::',
46                                  p_begin_occur => 1,
47                                  p_end_occur   => 2));
48       l_f_dest_siteid := util.tonumber
49            (util.snip_between ( p_source => p_f_dest,
50                                  p_begin => '::',
51                                  p_end    => '::',
52                                  p_begin_occur => 2,
53                                  p_end_occur   => 3));
54       l_f_dest_language := util.snip_between
55                                ( p_source => p_f_dest,
56                                  p_begin => '::',
57                                  p_end    => null,
58                                  p_begin_occur => 3);
59
60       -- Call Copy. l_newname calculation is a workaround a Portal feature.
61       if l_f_dest_id = 1 then
62          l_newname := 'temporary_Name';
63       else
64          l_newname := l_f_source_name;
65       end if;
66
67       l_result := wwv_cornerdb.copy (
68                          p_id               => l_f_source_id,
69                          p_newparentid      => l_f_dest_id,
70                          p_newname          => l_newname,
71                          p_copylayout       => 0,
72                          p_copychildcorners => 1,
73                          p_copyitems        => 1,
74                          p_copyaccess       => 1,
75                          p_level            => 1,
76                          p_siteid           => l_f_dest_siteid,
77                          p_copysiteid       => l_f_source_siteid);
78
79       if l_f_dest_id = 1 then
80          update wwv_corners
81          set     name = l_f_source_name
82          where   id = l_result and
83                  siteid = l_f_dest_siteid and
84                  language = l_f_source_language;
85       end if;
86     end; -- procedure copy_f

The core code necessary to copy a folder across content areas.

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-20102:
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.

Listing C

1     procedure move_f (p_f_source in varchar2,
2                       p_f_dest   in varchar2
3                        ) is
4       l_f_source_name     varchar2(1000);
5       l_f_dest_name       varchar2(1000);
6       l_f_source_language varchar2(1000);
7       l_f_dest_language   varchar2(1000);
8       l_newname           varchar2(1000);
9       l_f_source_id       number;
10       l_f_source_siteid   number;
11       l_f_dest_id         number;
12       l_f_dest_siteid     number;
13     begin
14       -- Decompose the parameters
15       <same code as in copy_f>
16
17       -- Call Copy. l_newname calculation is a workaround a Portal feature.
18       if l_f_dest_id = 1 then
19          l_newname := 'temporary_Name';
20       else
21          l_newname := l_f_source_name;
22       end if;
23
24       l_result := wwv_cornerdb.copy (
25                          p_id               => l_f_source_id,
26                          p_newparentid      => l_f_dest_id,
27                          p_newname          => l_newname,
28                          p_copylayout       => 0,
29                          p_copychildcorners => 1,
30                          p_copyitems        => 1,
31                          p_copyaccess       => 1,
32                          p_level            => 1,
33                          p_siteid           => l_f_dest_siteid,
34                          p_copysiteid       => l_f_source_siteid);
35       if l_f_dest_id = 1 then
36          update wwv_corners
37          set     name = l_f_source_name
38          where   id = l_result and
39                  siteid = l_f_dest_siteid and
40                  language = l_f_source_language;
41       end if;
42
43       if l_f_source_id = 1 then -- if the source folder is a root folder then
44          f_content_delete ( p_siteid   => l_f_source_siteid,
45                             p_language => l_f_source_language);
46       else -- if not root
47          wwv_cornerdb.remove (
48                             p_id               => l_f_source_id,
49                             p_language         => 'us',
50                             p_siteid           => l_f_source_siteid
51                              );
52       end if;
53     end; -- procedure move_f

The core code necessary to move a folder across content areas.

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.

Listing D

1     procedure f_content_delete (p_siteid   in number,    -- mandatory
2                                 p_f_id     in number     default 1,
3                                 p_language in varchar2   default 'us') is
4         cursor sub_f is
5            select id
6            from   wwv_corners
7            where parentid = p_f_id and
8                   siteid = p_siteid and
9                   language = p_language;
10         cursor items is
11            select id
12            from   wwv_things
13            where cornerid = p_f_id and
14                   siteid = p_siteid and
15                   language = p_language;
16     begin
17         -- for each sub-folders
18         for cur_sub_f in sub_f loop
19            -- remove the folder
20            wwv_cornerdb.remove (
21                   p_id               => cur_sub_f.id,
22                   p_language         => p_language,
23                   p_siteid           => p_siteid);
24         end loop; -- each sub folders
25
26         if p_f_id != 1 then -- Root folders do not contain items
27            -- for each item in sub-folder
28            for cur_item in items loop
29               wwv_thingdb.removeitem(
30                     p_thingid            => cur_item.id,
31                     p_siteid             => p_siteid,
32                     p_language           => p_language);
33            end loop; -- for cur_item in items
34         end if;
35     end; -- procedure f_content_delete

Generic procedure to delete the content of a given folder, but not the folder itself.

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.

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others. He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve@iherve.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.