Home » SQL & PL/SQL » SQL & PL/SQL » Pass Checkbox Parameters from HTML Form to a stored procedure (merged)
Pass Checkbox Parameters from HTML Form to a stored procedure (merged) [message #383192] Tue, 27 January 2009 11:56 Go to next message
jonesp99
Messages: 3
Registered: January 2009
Junior Member
Hi, I'm having problems with a form's delete function. I developed a web page that contains a web form at the top of the page, and all the records in the database displayed on the bottom half of the page. The form allows users to create new records (my create new records function works correctly), or click the checkbox for one or more records and select the Submit button so that the delete function can run and delete the records marked for deletion. I'm using the sample Oracle HR schema.

I created a package and delcared a Table type since I want folks to be able to select more than one checkbox. Here's the code for my Package specification:

CREATE OR REPLACE PACKAGE HR.DYN_HR_PAULA_SAMPLE_APP 
AS
/*I'm declaring a Table type here because I want to be able to process one or more checkboxes
*/
TYPE delete_records_table IS TABLE OF employees.employee_id%TYPE;
p_checkboxes delete_records_table;
PROCEDURE show;
PROCEDURE get_addnew_record (
    p_first_name        IN VARCHAR2 DEFAULT NULL,
    p_last_name         IN VARCHAR2,
    p_email             IN VARCHAR2,
    p_phone_number      IN VARCHAR2 DEFAULT NULL,
    p_hire_date         IN VARCHAR2 DEFAULT NULL,
    p_job_id            IN VARCHAR2 DEFAULT NULL,
    p_salary            IN VARCHAR2 DEFAULT NULL,
    p_commission_pct    IN VARCHAR2 DEFAULT NULL,
    p_manager_id        IN VARCHAR2 DEFAULT NULL,
    p_department_id     IN VARCHAR2 DEFAULT NULL);

PROCEDURE get_pop_form_current_data (p_employee_id IN employees.employee_id%TYPE DEFAULT 212);

PROCEDURE get_update_record (
    p_employee_id       IN    employees.employee_id%TYPE,
    p_first_name        IN    employees.first_name%TYPE,
    p_last_name         IN    employees.last_name%TYPE,
    p_email             IN    employees.email%TYPE,
    p_phone_number      IN    employees.phone_number%TYPE,
    p_hire_date         IN    employees.hire_date%TYPE,
    p_job_id            IN    employees.job_id%TYPE,
    p_salary            IN    employees.salary%TYPE,
    p_commission_pct    IN    employees.commission_pct%TYPE,
    p_manager_id        IN    employees.manager_id%TYPE,
    p_department_id     IN    employees.department_id%TYPE);

PROCEDURE get_delete_employee (p_checkboxes delete_records_table);
    
END DYN_HR_PAULA_SAMPLE_APP;
/


My delete procedure in the Package body looks like this:
PROCEDURE get_delete_employee (p_checkboxes IN delete_records_table)
AS
        
BEGIN
    FOR i IN 1..p_checkboxes.count
        LOOP
        DELETE FROM employees
        WHERE employee_id = p_checkboxes(i);
        END LOOP;
COMMIT;   

htp.htmlOpen;
htp.headOpen;
htp.title('Update Confirmation Page');
htp.headClose;
htp.bodyOpen;
htp.p('<CENTER>');
htp.p('<h2>You successfully deleted records.</h2>');

htp.anchor('dyn_hr_paula_sample_app.show', 'Go Back to Main Page');
htp.bodyClose;
htp.htmlClose;

END get_delete_employee;


And, here's a snipped of code used to display all the records in the employees table; the delete checkbox is the last form item:


htp.p('<FORM name="delete" method="POST" ACTION="dyn_hr_paula_sample_app.get_delete_employee">');
        htp.tableOpen (cattributes =>'border=1 bordercolor="black" width=100%');
        htp.tableRowOpen(cattributes=>'bgcolor="gray"');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>&nbsp;</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>First Name</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Last Name</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Email</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Phone Number</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Hire Date</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Job ID</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Salary</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Comm Pct</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Manager ID</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Dept ID</font>');
        htp.tableHeader('<Font size=2 face=Arial, Helvetica>Delete</font>');        
    htp.tableRowClose;

-- Display information about each row in the employees table 

    for c1 in (select employee_id, first_name, last_name, email, phone_number, hire_date,
 job_id, salary, commission_pct, manager_id, department_id
               from employees
               order by last_name) 
      LOOP
        htp.tableRowOpen (cattributes => 'width=100%');    
               
        htp.p('<td><a href="http://**.**.***.6:7777/pls/hr/dyn_hr_paula_sample_app.get_pop_form_current_data?p_employee_id='
||c1.employee_id||'"><img STYLE="border: none;" src="s:/paula/images/edit_icon.jpg"/></a></td>'); 
                        htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.first_name||'</font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.last_name||'</font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.email||'</font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.phone_number||'<font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.hire_date||'<font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.job_id||'<font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.salary||'</font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.commission_pct||'<font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.manager_id||'<font>');
            htp.tableData('<Font size=2 face=Arial, Helvetica>'||c1.department_id||'<font>');
            htp.tableData('<center><input type="checkbox" name="p_checkboxes" value="'||c1.employee_id||'"></center>');
                        
        htp.tableRowClose;    
        l_count := l_count + 1;  
      END LOOP;
     
     htp.tableRowOpen;
        htp.tableData(calign  =>  'CENTER', 
              cattributes =>  'colspan="2"',
              cvalue      =>   htf.formSubmit(cvalue=>'Delete'));

        htp.tableData(calign  =>  'CENTER', 
              cattributes =>  'colspan="2"',
              cvalue      =>   htf.formReset(cvalue=>'Reset'));           
    htp.tableRowClose;  
     htp.p('</form>');


I attached a screenshot of the web page (I removed most of the rows for brevity). TIA.
[EDITED by DJM: split long lines]

[Updated on: Wed, 28 January 2009 23:59] by Moderator

Report message to a moderator

Pass Checkbox Parameters from HTML Form to a stored procedure [message #383401 is a reply to message #383192] Wed, 28 January 2009 15:02 Go to previous messageGo to next message
jonesp99
Messages: 3
Registered: January 2009
Junior Member
I'm still looking for a solution to my forms problem. FYI, I'm not using Applications Express to build my application--I'm using straight PL/SQL. I need to know how to pass checkbox parameters from my Web form. I'm allowing folks to select one or more checkboxes on a form that will call a delete function to delete the selected records. What I read in Oracle's "Database Application Developer's Guide - Fundamentals" isn't helpful to me. If someone would point me to some examples, maybe I could see what I'm doing wrong. Here's what was written in "Database Application Developer's Guide - Fundamentals":

All the checkboxes with the same NAME attribute make up a checkbox group. If none of the checkboxes in a group is checked, the stored procedure receives a null value for the corresponding parameter.

If one checkbox in a group is checked, the stored procedure receives a single VARCHAR2 parameter.

If more than one checkbox in a group is checked, the stored procedure receives a parameter with the PL/SQL type TABLE OF VARCHAR2. You must declare a type like this, or use a predefined one like OWA_UTIL.IDENT_ARR. To retrieve the values, use a loop:

CREATE OR REPLACE PROCEDURE handle_checkboxes ( checkboxes owa_util.ident_arr )
AS
BEGIN
...
FOR i IN 1..checkboxes.count
LOOP
htp.print('<p>Checkbox value: ' || checkboxes(i));
END LOOP;
...
END;
/ 
SHOW ERRORS;
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383403 is a reply to message #383401] Wed, 28 January 2009 15:12 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
try this demo package - it includes checkboxes...
  • Attachment: DEMO_PKG.SQL
    (Size: 9.21KB, Downloaded 628 times)
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383409 is a reply to message #383401] Wed, 28 January 2009 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm still looking for a solution to my forms problem.
I'd like to suggest that perhaps you are (ab)using the wrong tool for this job.

While I am sure that you can build a web form application using PL/SQL, but IMO better languages exist specifically for this purpose.

Good Luck!
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383460 is a reply to message #383409] Wed, 28 January 2009 23:54 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I believe that PL/SQL is brilliant for writing web applications. Use the 'htf.formcheckbox' procedure. Research the 'htf' and 'htp' procedures

David
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383465 is a reply to message #383401] Thu, 29 January 2009 00:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
"brilliant" is not the adjective I'd use.
A web form runs normally on a web server.
PL/SQL only runs within Oracle RDBMS which is not a webserver.
While it does work, I question its scability.
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383681 is a reply to message #383460] Thu, 29 January 2009 12:13 Go to previous messageGo to next message
jonesp99
Messages: 3
Registered: January 2009
Junior Member
Thanks everyone! I don't doubt that there is a better way to create the web form; I'm just new to PL/SQL and am trying to learn to create various procedures, functions and packages. I found a solution to my problem....however, I used owa_util.ident_arr instead of a Table Type to handle the array.
Re: Pass Checkbox Parameters from HTML Form to a stored procedure [message #383703 is a reply to message #383681] Thu, 29 January 2009 16:20 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I don't know how PL/SQL web toolkit (OWA* packages etc) scales for large apps - I've never benchmarked it, but have no reason to be concerned. To serve up content - all you're doing is writing to the HTTP buffer just like DBMS_OUTPUT writes to it's own buffer.

Comparing the PL/SQL web toolkit to Apex - they both use the same PL/SQL gateway. Both support connection pooling (upstream of the gateway) and both run in the database - not on a separate app server.

The fact that Metalink (classic) and AskTom are both Apex based means that hosting a web app inside Oracle scales just fine for most of us. Recall that Metalink used to have some super-slow Java based n-tier architecture before they changed to Apex a few years back...

Building a web app with PL/SQL web is a bit like building a radio with individual transistors rather than chips. For most folks, Apex makes more sense these days...

[Updated on: Thu, 29 January 2009 16:21]

Report message to a moderator

Previous Topic: Multiple Flat File Generation in one spool
Next Topic: Dynamic while loop
Goto Forum:
  


Current Time: Sat Dec 10 10:52:49 CST 2016

Total time taken to generate the page: 0.08105 seconds