plsqlmvc



What is?



This is a framework that has been developed during the last 2 years. What was needed was a way to write pl/sql webapps using some level of abstraction for the front-end: pl/sql gateway, oracle portal portlets or other portal portlets (eg. WSRP).

So this framework enables pl/sql web developers to write an application once and then to plug it in any environment. This is accomplished leveraging the MVC pattern, like Struts or Stripes do for Java developers.



An application is composed by 3 packages:



The business logic package (Model): here the developer will write all the interaction with the data model: DML and possibly queries.



create or replace package srv_test_m

as



procedure on_modify;

procedure on_save;



procedure on_delete;



end srv_test_m;



create or replace package body srv_test_m

as



procedure on_modify

as

begin

for c_cur in (select * from srv_test_t where id= to_number(plsqlmvc.get_value('id'))) loop

plsqlmvc.set_value('name',c_cur.name);

plsqlmvc.set_value('email',c_cur.email);

end loop;

exception

when others then

htp.p(sqlerrm(sqlcode));

end on_modify;





procedure on_save

as

v_id number:= plsqlmvc.get_value('id');

begin

if v_id is null then

insert into srv_test_t values (srv_test_id_seq.nextval,plsqlmvc.get_value('name'),plsqlmvc.get_value('email'));

else

update srv_test_t

set name= plsqlmvc.get_value('name'),

email= plsqlmvc.get_value('email')

where id= v_id;

end if;

end on_save;



procedure on_delete

as

v_id number:= plsqlmvc.get_value('id');

begin

delete srv_test_t

where id= v_id;

end on_delete;



end srv_test_m;



The user intervace package (View): here will use the htp.p to print out the html interface.



create or replace package srv_test_v

as

procedure report;

procedure form;



end srv_test_v;



create or replace package body srv_test_v

as



procedure report

as

begin

plsqlmvc.show_errors;



htp.p('<table>

<tr>

<th>Select</th>

<th>Name</th>

<th>E-Mail</th>

</tr>

');

for c_cur in (select * from srv_test_t) loop

htp.p('<tr>

<td><input type="radio" value="'||c_cur.id||'" name="id"/></td>

<td>'||c_cur.name||'</td>

<td>'||c_cur.email||'</td>

</tr>');

end loop;

htp.p('</table>');

htp.p('<input type="submit" value="Add" name="event_add"/>');

htp.p('<input type="submit" value="Modify" name="event_modify"/>');

htp.p('<input type="submit" value="Delete" name="event_delete"/>');

end report;



procedure form

as

begin

plsqlmvc.show_errors;

if plsqlmvc.is_event('add') then

htp.p('<b>New</b><br/>');

else

htp.p('<b>Modify</b><br/>');

htp.p('<input type="hidden" value="'||plsqlmvc.get_value('id')||'" name="id"/>');

end if;

htp.p('Name: <input type="text" value="'||plsqlmvc.get_value('name')||'" name="name"/><br/>');

htp.p('E-Mail: <input type="text" value="'||plsqlmvc.get_value('email')||'" name="email"/><br/>');

htp.p('<input type="submit" value="Back" name="event_back"/>');

htp.p('<input type="submit" value="Save" name="event_save"/>');

end form;



end srv_test_v;



The controller package (Control): here will be the navigation and check configuration

create or replace package srv_test_c

as



procedure do(name_array plsqlmvc.vc_arr,

value_array plsqlmvc.vc_arr);



end srv_test_c;



create or replace package body srv_test_c

as



c_event_config constant plsqlmvc.vc_list_list:= plsqlmvc.vc_list_list

(



/* report */

plsqlmvc.vc_list('report','modify','form'),--after modify event on report page go to page form

plsqlmvc.vc_list('report','add','form'),--after add event on report page go to page form

/* form */

plsqlmvc.vc_list('form','save','report')--after save event on form page go to page report

);

c_check_config constant plsqlmvc.vc_list_list:= plsqlmvc.vc_list_list

(

/* report */

plsqlmvc.vc_list('report','modify','id','Select',plsqlmvc.check_exists,'You have to select a row.'), -- after modify event on report page check the the http parameter id exists else return an error message : 'You have to select a row.'

plsqlmvc.vc_list('report','delete','id','Select',plsqlmvc.check_exists,'You have to select a row.'),-- after delete event on report page check the the http parameter id exists else return an error message : 'You have to select a row.'



/* form */

plsqlmvc.vc_list('form','save','name','Name',plsqlmvc.check_notnull), -- another example of builtin checks

plsqlmvc.vc_list('form','save','email','E-Mail',plsqlmvc.check_email) – email built-in check

);



procedure do(name_array plsqlmvc.vc_arr,

value_array plsqlmvc.vc_arr)

as

begin

plsqlmvc_gw_session.init_browser_session;

owa_util.http_header_close;

mytemplate.header('Test service');

plsqlmvc_gw_request.load_request(name_array,value_array);

plsqlmvc.show('srv_test_v',

'srv_test_m',

'report',

c_event_config,

c_check_config);

mytemplate.footer;

end do;



end srv_test_c;

Features



User feedback will drive development, submit your requests:

http://sourceforge.net/tracker/?group_id=234975



Download

Source (last release):

http://sourceforge.net/projects/plsqlmvc/files/plsqlmvc/initial/plsqlmvc.zip/download

Use it

  1. Create a database user to contain the sample application

  2. Download sql files: http://sourceforge.net/projects/plsqlmvc/files/plsqlmvc/initial/plsqlmvc.zip/download

  3. sqlplus myuser/myuser @install /public/plsqlmvc/myuser/ myuser/myuser (read the INSTALL.txt)

  4. Test it: http://servername:port/dad/!newuser.srv_test_c.do



To test it via WSRP

  1. use a consumer of your choice or my test wsrp consumer module for Drupal http://sourceforge.net/projects/drupalwsrp

  2. sqlplus myuser/myuser @plsqlmvc_wsrp_impl

  3. sqlplus myuser/myuser @enable_wsrp_impl

  4. enjoy

Troubleshooting



If you get any other problem please submit a support request:

http://sourceforge.net/tracker/?group_id=234975

About

plsqlmvc is developed by Andrea A.A. Gariboldi