Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Is this even possible in PL/SQL
Is this even possible in PL/SQL [message #237056] Fri, 11 May 2007 14:09 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Is it possibly to write an election or survey application in PL/SQL that passes multiple items depending on what they select?

Let me explain. In my limited knowledge of PL/SQL, a web form that has 3 input fields and a submit button sends 4 items to the receiving procedure. Now, the receiving procedure has to be coded to accept 4 items only, not 3, not 2 but 4 items. So, back to the election or survey application that could send 1 or 50 items (depending on how you vote or fill out the survey) to the receiving procedure, how do you account for the varying number of receiving items?

procedure webForm is

begin

<form name="new" method="post" target="_top" action="/pls/test/webReceive">
<input type="text" name="first" size="20" maxlength="20">
<input type="text" name="middle" size="20" maxlength="20">
<input type="text" name="last" size="20" maxlength="20">
<input type="submit" value="Submit" name="submit">
</form>

end;

procedure webReceive (first  varchar2,
                      middle varchar2,
                      last   varchar2,
                      submit varchar2) is

begin

htp.print(first); htp.br;
htp.print(middle); htp.br;
htp.print(last); htp.br;

end;


If I only passed 2 fields to webReceive, I would receive the error, procedure can not be found or something like that.

How would you code webReceive for a survey that could pass any number of fields to the procedure based on different responses by the user?

Is this something that PL/SQL just can't do?

[Updated on: Fri, 11 May 2007 14:10]

Report message to a moderator

Re: Is this even possible in PL/SQL [message #237072 is a reply to message #237056] Fri, 11 May 2007 15:25 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Yes, it's possible. Your procedure should look like this...

procedure webReceive (first  varchar2 default NULL,
                      middle varchar2 default NULL,
                      last   varchar2 default NULL,
                      submit varchar2 default NULL) is

begin

htp.print(first); htp.br;
htp.print(middle); htp.br;
htp.print(last); htp.br;

end;



Please note a few things about your form though:
<form name="new" method="post" target="_top" action="/pls/test/webReceive">
<input type="text" name="first" size="20" maxlength="20">
<input type="text" name="middle" size="20" maxlength="20">
<input type="text" name="last" size="20" maxlength="20">
<input type="submit" value="Submit" name="submit">
</form>


1. This form will ALWAYS send four variables to your procedure. The variables may be null (no value entered), but it will always send four. If you change the method from POST to GET, you can see the values in the URL when you hit the submit button.

2. If you don't want to send a value for the "Submit" button, don't give it a name (name="submit"). The button will still submit the form, but you won't have a variable "submit" passed with a value "Submit".

3. Checkboxes and Radio buttons are about the only input fields I've had problems with when passing web form fields to a procedure. If you don't check anything, no value is passed so you need the "default NULL" for that particular field in your parameter list.

Let me know if this helps or if you have any further questions.

Ron
Re: Is this even possible in PL/SQL [message #237080 is a reply to message #237072] Fri, 11 May 2007 15:53 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Ronald Beck wrote on Fri, 11 May 2007 15:25
Yes, it's possible. Your procedure should look like this...

procedure webReceive (first  varchar2 default NULL,
                      middle varchar2 default NULL,
                      last   varchar2 default NULL,
                      submit varchar2 default NULL) is

begin

htp.print(first); htp.br;
htp.print(middle); htp.br;
htp.print(last); htp.br;

end;






Ah, I never knew that about "default NULL" after the variable name. I always did it with an <input type="hidden" name="first"> statement.

Ok, back to the original question, how would it be done with a survey that is asking multiple questions based on varying answers?

You see, it's possible to answer Question 1 with a particular answer that now takes you down to Question 10 bypassing everything else. What I'm getting at is, what happens when your survey only ends up being 13 questions with different input variables versus being 25 questions with totally different input variables? See what I'm trying to get at?

One part of the survey might be an input field, another might be a check boxes and another might be radio boxes. Depending on the answers in the survey, you could end up with all sorts of combinations (one part has check boxes and another has radio boxes or another part has check boxes and input boxes). All of this would depend on how they were answersing the questions.

How would you accommodate all the possibilities?
Re: Is this even possible in PL/SQL [message #237085 is a reply to message #237080] Fri, 11 May 2007 16:23 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
A lot depends on how you do the survey form (or forms). Is it going to be one big survey form from Question 1 to Question 25 and somewhere in there it says "If you answered YES here, go to Question 8", or are you going to make it automatically skip from 1 to 8 based on the given answer.

From what I've found, a form will ALWAYS pass a value to your <form action=...> procedure if you use

<input type="text" name="xxx">,
<select name="xxx"><option value="yyy"></select>,
<textarea name="xxx"></textarea>.

A form will NOT send a value if nothing is checked if you use...

<input type="checkbox" name="xxx">
<input type="radio" name="xxx">

So, on your example form, it will always send the info:

http://<your server>:<port>/pls/text/webReceive?first=&middle=&last=&submit=Submit

to your webReceive procedure, whether or not you actually put data into the field for each of those variables. You can see this when you change from POST to GET. I recommend doing that during debug of your form to assure it's passing the proper information. Don't make the mistake of assuming that "...&middle=&last=..." is not passing a value to your procedure. It is, and the value is NULL. The URL above passes four values, three NULL values and one "Submit" string.

If you have a form with 65 <input type="text"...> fields, you'll need 65 variables in your procedure, even if only two of the fields are popluated (I know, I have some!). I'll usually make up the form and then click "submit", cut the URL from the browser and paste it to a file so that I instantly get a complete list of form variables that are passed to my procedure.

I learned about the "default NULL" piece because several of the portal APIs can take multiple inputs and you don't have to specify all of them. I got curious and looked at one and found the "default NULL" for all non-required input parameters.

So, if you have one form with 65 fields, you'll need an input parameter for each of the 65. If you're going to use several forms, you'll have to coordinate the information entry with the form navigation.

Good luck with it.

Ron
Re: Is this even possible in PL/SQL [message #237088 is a reply to message #237085] Fri, 11 May 2007 17:17 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Since I needed something to fill my afternoon before I head out, here's some code for you to play with. I'm assuming you know how to make a procedure run in a web browser. And, forgive my HTP.xxx coding. It's easier for me to make everything a print line and enter the HTML code myself than it is to remember all the HTP.xxx stuff. Hope this helps.

First, the form:

create or replace procedure play_form is
begin

  -- The following is a script that produces a quick form to show the example
  -- of many of the type of input fields available in a form and how to pass
  -- information to a pl/sql procedure that will read the information and 
  -- process it.
  
  htp.p('<form action="itcreator.play_form_action">');
  htp.p('<table>');
  htp.p('<tr>');
  -- pretty standard <input type=text...
  htp.p('  <td>What''s your name?</td>');
  htp.p('  <td><input type="text" name="f_name"></td>');
  htp.p('</tr>');
  htp.p('<tr>');
  htp.p('  <td>What''s your favorite color?</td>');
  htp.p('  <td><input type="text" name="f_color"></td>');
  htp.p('</tr>');
  htp.p('<tr>');
  -- Here's a radio button.  If nothing is checked, no value for f_watch_tv is passed
  -- which is why you need to define the parameter as "f_watch_tv varchar2 default NULL"
  htp.p('  <td>Do you like to watch TV?</td>');
  htp.p('  <td><input type="radio" name="f_watch_tv" value="Yes" clicked>Yes &nbsp;&nbsp;');
  htp.p('      <input type="radio" name="f_watch_tv" value="No">No</td>');
  htp.p('</tr>');
  htp.p('<tr>');
  htp.p('  <td>Which sports do you like?</td>');
  htp.p('  <td>');
  -- Here's a series of checkboxes that provide values to the parameter f_sport.  Because
  -- there are multiple selections for the same variable, you need to define the parameter
  -- as f_sport owa_text.vc_arr.  Unfortunately, I haven't found a way to provide a default
  -- value for this parameter TYPE, which is why there is a "<input type="hidden" name="f_sport>
  -- as the first line.  This assures at least one value for f_sport which is NULL.  Your
  -- procedure will have to deal with the null value.
  htp.p('      <input type="hidden" name="f_sport">');
  htp.p('      <input type="checkbox" name="f_sport" value="Baseball">Baseball<br>');
  htp.p('      <input type="checkbox" name="f_sport" value="Basketball">Basketball<br>');
  htp.p('      <input type="checkbox" name="f_sport" value="Football">Football<br>');
  htp.p('      <input type="checkbox" name="f_sport" value="Hockey">Hockey<br>');
  htp.p('      <input type="checkbox" name="f_sport" value="Swimming">Swimming<br>');
  htp.p('      <input type="checkbox" name="f_sport" value="Wimpy Figure Skaters">Wimpy Figure Skaters<br>');
  htp.p('   </td>');
  htp.p('</tr>');
  htp.p('<tr>');
  htp.p('  <td>What''s your favorite team name?</td>');
  htp.p('  <td><input type="text" name="f_team"></td>');
  htp.p('</tr>');
  -- Here's an example of a "SELECT" field.  Because of the way select works, it will
  -- always pass the first entry in the option list if none are selected.  If you need
  -- to allow multiple selections in the dropdown, define the parameter as owa_text.vc_arr
  -- so that it will comprehend multiple values for a single variable name.
  htp.p('<tr>');
  htp.p('  <td>Select one of the following: </td>');
  htp.p('  <td><select name="f_animal">');
  htp.p('      <option value="horse">horse');
  htp.p('      <option value="donkey">donkey');
  htp.p('      <option value="mule">mule');
  htp.p('      </select>');
  htp.p('  </td>');
  htp.p('</tr>');
  htp.p('<tr>');
  -- Here's the submit button.  Note that the value shows up in the button but
  -- is not passed to the procedure because there's no "name=" defined.
  htp.p('  <td colspan=2><input type="submit" value="Record my answers"></td>');
  htp.p('</tr>');
  
  htp.p('</table>');
  htp.p('</form>');
end play_form;


Next, the action procedure for the form:

create or replace procedure play_form_action( f_name in varchar2, 
                                              f_color in varchar2, 
                                              f_watch_tv in varchar2 default NULL, 
                                              f_sport in owa_text.vc_arr, 
                                              f_team in varchar2, 
                                              f_animal in varchar2
                                             ) is
begin

   -- This is the ACTION procedure for the form in the previous example
   -- it takes the data that was input and displays it.
   
   htp.p(' Value for F_NAME is: '||NVL(f_name,'No Name was entered')||'<br>');
   htp.p(' Value for F_COLOR is: '||NVL(f_color,'No Color was entered')||'<br>');
   htp.p(' Value for F_WATCH_TV is: '||NVL(f_watch_tv,'No TV selection was made')||'<br>');
   
   for x in 1..f_sport.last loop
     -- Note that if the only value passed was the hidden NULL, nothing is
     -- displayed for this.  Otherwise the values checked will be displayed.
     if f_sport(x) is not null then
       htp.p(' Value for F_SPORT('||x||') is: '||f_sport(x)||'<br>');
     end if;
   end loop;
   
   htp.p(' Value for F_TEAM is: '||NVL(f_team,'No Team was entered')||'<br>');
   htp.p(' Value for F_ANIMAL is: '||NVL(f_animal,'No Animal was selected')||'<br>');
   
end play_form_action;
Re: Is this even possible in PL/SQL [message #237803 is a reply to message #237088] Tue, 15 May 2007 14:02 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Here's a test form...
  • Attachment: demo_pkg.zip
    (Size: 2.51KB, Downloaded 1990 times)
Re: Is this even possible in PL/SQL [message #237809 is a reply to message #237056] Tue, 15 May 2007 14:31 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Thanks everyone.

It sounds like my main problem has been the omission of the 'DEFAULT NULL' with my variables in my receiving procedure.

That's why I couldn't figure out how you could account for a survey that ended after 5 questions (without having additional hidden input statements) versus a survey that ended after 100 questions.

Here's what I have gathered:

  • If a survey can have 100 answers then the receiving procedure must have 100 variables
  • Add DEFAULT NULL to all 100 variables so that the receiving procedure sees all 100 variables even if the survey ends at the 5th question and nothing else is sent
  • The receiving variables must be in the exact same order as the form
  • Don't "name" your submit button if you don't want to include it in the receiving procedure

Re: Is this even possible in PL/SQL [message #237819 is a reply to message #237809] Tue, 15 May 2007 15:34 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Duane

No, if you have 100 answers - you can also have a single (or typically 2 varrays). One for the value and one for tag corresponding to the question number. See my attachement above for an example.

"flexible parameter passing" is similar - Google it or see this link. It's not generally advised - it makes debugging difficult and it's not trivial to work with. It's probably not the fastest approach either, and may have other limitations... Apex uses a similar approach sometimes ("|" delimited item names and values)

http://technology.amis.nl/blog/?p=172

From what I recall, order of parameters in form doesn't need to match procedure parameter order in the submit.


Another tip is only to use character datatypes in receiving procedure parameters. Numbers and dates are all represented as strings by the browser. If something needs to be a number or date, convert it inside the proc.

[Updated on: Wed, 16 May 2007 17:11]

Report message to a moderator

Re: Is this even possible in PL/SQL [message #237820 is a reply to message #237056] Tue, 15 May 2007 15:49 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Wow, I never knew that. Boy, I can see that coming in handy at times.
Re: Is this even possible in PL/SQL [message #238135 is a reply to message #237819] Wed, 16 May 2007 16:35 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
That is very cool! I'll have to try it and see what happens. It could help on a LOT of the web forms I've been working on.

Ron
Re: Is this even possible in PL/SQL [message #451818 is a reply to message #237056] Sat, 17 April 2010 00:32 Go to previous messageGo to next message
pianoman
Messages: 3
Registered: April 2010
Junior Member
Can you please tell me why that code in the attached example is using arrays when there can only be one value selected for P_RD1 and one value for P_RD2, and one value for loc?

thanks,
Re: Is this even possible in PL/SQL [message #451856 is a reply to message #451818] Sat, 17 April 2010 10:12 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I can't run the attachment code right now, but Select1 is a multi-select list, so loc pltab_typ is used to pass all the selected locations. From what I recall, P_RD1 and P_RD2 are for the multiple radio buttons, and you need 2 of them because radio buttons don't get submitted by the form unless selected. I don't think the code really shows it well, but the idea is one pltab_typ for the radio button name and the other to track whether it has a value or not. You'll have to run the form to see the behavior.
Re: Is this even possible in PL/SQL [message #451906 is a reply to message #237056] Sun, 18 April 2010 17:44 Go to previous messageGo to next message
pianoman
Messages: 3
Registered: April 2010
Junior Member
Thanks Andrew,

I'm failing to see how a Radio Button can be put into an array.

In my example below, I have 3 Questions with with Answers A, B or C. I'd like to be able to have a dynamic number of Questions. But as far as I can tell, p_rg1 is the part which needs to be variable and stored in an array....and I can't see how that can be done.

I can only see it working if I add 3 variables to my procedure p_rg1, p_rg2 and p_rg3....but then I've hardcoded how many questions there are.

htp.formradio('p_rg1','Option A', 'CHECKED'); htp.print('Option A');htp.br;
htp.formradio('p_rg1','Option B'); htp.print('Option B');htp.br;
htp.formradio('p_rg1','Option C'); htp.print('Option C');htp.br;

htp.formradio('p_rg2','Option A', 'CHECKED'); htp.print('Option A');htp.br;
htp.formradio('p_rg2','Option B'); htp.print('Option B');htp.br;
htp.formradio('p_rg2','Option C'); htp.print('Option C');htp.br;

htp.formradio('p_rg3','Option A', 'CHECKED'); htp.print('Option A');htp.br;
htp.formradio('p_rg3','Option B'); htp.print('Option B');htp.br;
htp.formradio('p_rg3','Option C'); htp.print('Option C');htp.br;


Any ideas would be appreciated !

thanks
Re: Is this even possible in PL/SQL [message #452058 is a reply to message #451906] Mon, 19 April 2010 14:55 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Rather than me misleading you, see ==> http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_we.htm#1006251

I see my demo pkg isn't very clear to read. I'd recommend testing it and then try tweaking to add a 3rd radio button. I had a spec/body mismatch (default 2 vs default 3) that I needed to fix before my copy compiled in 10g.

[Updated on: Mon, 19 April 2010 15:00]

Report message to a moderator

Re: Is this even possible in PL/SQL [message #452235 is a reply to message #237056] Tue, 20 April 2010 17:33 Go to previous messageGo to next message
pianoman
Messages: 3
Registered: April 2010
Junior Member
For those that may be interested in the solution for passing multiple Radio Buttons.....

create or replace PACKAGE testing AS 

PROCEDURE show_questions_p;

PROCEDURE show_answers_p( 
  name_array IN  owa.vc_arr,
  value_array IN  owa.vc_arr);

END testing; 
/

create or replace PACKAGE BODY testing AS 

PROCEDURE show_questions_p IS 

  TYPE array_tab IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
     
  my_array array_tab;
  my_label array_tab;
  
  lvn_count integer;

  lvn_number_of_questions NUMBER(10) := 10; -- # of Questions  
  lvn_number_of_options   NUMBER(10) := 3;  -- # of Options per question

BEGIN 

  HTP.htmlopen; 
  HTP.headopen; 
  HTP.title('Questions'); 
  HTP.headclose; 
  HTP.bodyopen; 
  
  HTP.print('Questions');
  HTP.br;
  
  -- Note the use of the ! to signify flexible parameter passing
  HTP.formopen('!testing.show_answers_p', 'post');  
  
  lvn_count := 0;
  
  for i in 1..lvn_number_of_questions loop
  HTP.print('Question '||i);
  HTP.br;
  
    for j in 1..lvn_number_of_options loop
      lvn_count := lvn_count + 1;
      
      -- Construct 2 arrays of the Radio Button, one for the Name, one for Value (label)
      my_array(lvn_count) := 'Radio'||i;
      my_label(lvn_count) := 'Option'||j;
      
      -- Display the content of the array as a Radio Button
      HTP.formradio(my_array(lvn_count), my_label(lvn_count)); 
      
      HTP.print('&nbsp;'||my_label(lvn_count));
      HTP.br;
    end loop;
    HTP.br;
      
  end loop;

  HTP.formsubmit('p_submit', 'Go'); 
  HTP.formclose; 
  HTP.bodyclose; 
  HTP.htmlclose; 
  
END; 


PROCEDURE show_answers_p( 
  name_array IN  owa.vc_arr,    -- Note the parameters must be named name_array and value_array
  value_array IN owa.vc_arr)
IS 
BEGIN 
  HTP.htmlopen; 
  HTP.headopen; 
  HTP.title('Responses'); 
  HTP.headclose; 
  HTP.bodyopen; 

  HTP.print('Responses'); 
  HTP.br;
  HTP.br;
  
  -- Loop through all the Form elements and display their values
  for i in 1..name_array.count loop
    HTP.print(name_array(i)||' : '||value_array(i));
    HTP.br;
  end loop;

  HTP.bodyclose; 
  HTP.htmlclose; 
  
EXCEPTION 
WHEN OTHERS THEN 
  HTP.print(SQLERRM); 
END; 

END testing; 
/
Re: Is this even possible in PL/SQL [message #452236 is a reply to message #452235] Tue, 20 April 2010 18:00 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Thanks for the update. It's worth mentioning for others that although your show_questions_p form uses arrays (pl/sql tables) to construct it, the receiving proc show_answers_p is only interested in the final HTML structure of the form, not how it was created.

[Updated on: Tue, 20 April 2010 18:01]

Report message to a moderator

Previous Topic: Cannot connect to home page in Apex
Next Topic: Code for shuttle box in Action items ->select columns
Goto Forum:
  


Current Time: Thu Mar 28 15:11:46 CDT 2024