mod_plsql and flexible parameter passing

This is a continuation on a previous entry, where I introduced a HTML wrapper. This wrapper has a nice way to produce a combo box:

htp.p(htmlform.selecttable(label => 'Directory:'
, name => 'p_directory'
, tablename => 'dba_directories'
, textcol => 'directory_name'
, listsize => 10
, orderby => '1'
, p_default => r_sel_pd.directory_name ) );

This little piece of code will actually display a combo box, 10 lines deep, based on the dba_directories table. It will display the column directory_name, and pass it back in a parameter, called p_directory. The list will be ordered on the (one) column displayed.
As the select_table function is overloaded, the fact that listsize is mentioned means it will be a multi select combo box. For the HTML coders amongst you, the select element will have multiple="multiple" added.

This means the call to the database will pass an unknown number of parameters. how to handle this?
Well, this is where the flexible parameter passing of mod_plsql kicks in. First of all, we'll have to tell mod_plsql we're passing an unknown number of parameters. this is done by changing the call to the procedure that will handle the request. in stead of this:

HTP.p(htmlform.formstart(p_action => 'fablogspr.bsavepd'));

the call is changed to this:

HTP.p(htmlform.formstart(p_action => '!fablogspr.bsavepd'));

Note the exclamation mark. That's all there is to change the mode of mod_plsql.

Procedure changes.
The original procedure just had three parameters, id, profile and directory name. The declaration part looks like:

procedure bsavepd (
p_id IN VARCHAR2,
p_profiel IN VARCHAR2,
p_directory IN VARCHAR2 );

The new procedure declaration is:

procedure bsavepd(
name_array IN OWA.vc_arr,
value_array IN OWA.vc_arr)

As I designed the page to use three parameters originally, and the first two do not change, the combo box procedding can be:

begin
for i in 3 .. name_array.LAST
loop
savenewpd(l_prf, value_array(i));
end loop;
EXCEPTION
WHEN extra_page
-- Just return the generated error or warning page.
THEN
null;
WHEN others
THEN
htp.p(format.errorpage(SQLERRM));
END;

I do not need the name_array(i) in the update loop, as it will always contain the name of the parameter I defined in the htmlform.selecttable above, p_directory.
Enjoy modding!

Space advisor - ORA-20000

Ran against a bug today, with dbms_space:

ORA-12012: error on auto execute of job 8897
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1344
ORA-06512: at "SYS.DBMS_SPACE", line 1560

Turns out to be bug 4707226, which "will be fixed in release 11.0", explained in Note:343795.1. One of the symptoms is that you have a tablespace referenced in table DBA_AUTO_SEGADV_CTL, that no longer exists.
In my case, I used drop tablespace xxxx including contents and datafiles;
The note ends with "No workaround available". Well, here is one that worked for me:
- recreate the tablespace with a minimal sized datafile (100k will do)
- exec dbms_space.auto_space_advisor_job_proc
That should now run without problems.
- use drop tablespace xxx;
- exec dbms_space.auto_space_advisor_job_proc
Should still run without errors.
You may check DBA_AUTO_SEGADV_CTL, so verify your tablespace is not mentioned anymore.

Help save €200,000,000

Although I like France, I think this should stop.
Wasting €200 million each year... Just take a moment and sign the petition:

Formatting blogs

Today, I finally got around some nagging problem with formatting. Just reread some old posts to see the difference... Code snippets now actually look like code snippets - I'm in the process of updating all old posts, but it requires editing the HTML code directly, so it's error prone.
Anyway - that's my current backlog for now: reformat old posts...