Hello All,
I am trying to write an application for Student Transfer Articulation.
I have 3 fields(1st one is a drop down menu for "Subject", 2nd one is a
text field for "Course Number" and 3rd one is a drop down menu for
"Year") to make up a row on my form. I am allowing a maximum of 10 rows
on the form.
I am planning to use PL/SQL tables to store the values that are entered
on the form.
Can someone let me know how I can do this ? I mean, how do I actually
get the values from the form into the PL/SQL table and then retrieve
those ? I have the front end ready and want to know how things would
happen once I click on the "Submit Query" button.
I am new to PL/SQL etc and hence the question.
- Ardy

<happyardy (AT) gmail (DOT) com> wrote

: Hello All,
: I am trying to write an application for Student Transfer Articulation.
: I have 3 fields(1st one is a drop down menu for "Subject", 2nd one is a
: text field for "Course Number" and 3rd one is a drop down menu for
: "Year") to make up a row on my form. I am allowing a maximum of 10 rows
: on the form.
: I am planning to use PL/SQL tables to store the values that are entered
: on the form.
: Can someone let me know how I can do this ? I mean, how do I actually
: get the values from the form into the PL/SQL table and then retrieve
: those ? I have the front end ready and want to know how things would
: happen once I click on the "Submit Query" button.
: I am new to PL/SQL etc and hence the question.
: - Ardy
:

typically PL/SQL tables are temporary in-memory storage used in database
packages or in Oracle's Forms and Reports developer tools...

what development tools are you using?

why do you want to use PL/SQL tables? where would the tables reside?

++ mcs

Mark C. Stock wrote:
Thanks for your reply.
I am using Oracle SQL Developer to code(if that is what you meant by
development tools).
I have been told to use PL/SQL tables since it would also be a good
exercise for future development here. At this point I am really not
that knowledgable to make an educated choice about whether PL/SQL
tables are the best way to go for my application.
thanks
- Ardy
I have been told to use PL/SQL tables

On Sat, 05 Aug 2006 12:48:40 -0700, happyardy wrote:


One way to interpret this - you have effectively been told to use a
variant on 'array'.

(Not entirely accurate, but a convenient way to start thinking about
PL/SQL tables.)

--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

<happyardy (AT) gmail (DOT) com> wrote

:
: Mark C. Stock wrote:
: > <happyardy (AT) gmail (DOT) com> wrote in message
: > news:1154797266.923101.275840 (AT) i42g2000cwa (DOT) googlegroups.com...
: > : Hello All,
: > : I am trying to write an application for Student Transfer Articulation.
: > : I have 3 fields(1st one is a drop down menu for "Subject", 2nd one is
a
: > : text field for "Course Number" and 3rd one is a drop down menu for
: > : "Year") to make up a row on my form. I am allowing a maximum of 10
rows
: > : on the form.
: > : I am planning to use PL/SQL tables to store the values that are
entered
: > : on the form.
: > : Can someone let me know how I can do this ? I mean, how do I actually
: > : get the values from the form into the PL/SQL table and then retrieve
: > : those ? I have the front end ready and want to know how things would
: > : happen once I click on the "Submit Query" button.
: > : I am new to PL/SQL etc and hence the question.
: > : - Ardy
: > :
: >
: > typically PL/SQL tables are temporary in-memory storage used in database
: > packages or in Oracle's Forms and Reports developer tools...
: >
: > what development tools are you using?
:
: > why do you want to use PL/SQL tables? where would the tables reside?
: >
: > ++ mcs
:
: Thanks for your reply.
: I am using Oracle SQL Developer to code(if that is what you meant by
: development tools).
: I have been told to use PL/SQL tables since it would also be a good
: exercise for future development here. At this point I am really not
: that knowledgable to make an educated choice about whether PL/SQL
: tables are the best way to go for my application.
: thanks
: - Ardy
: I have been told to use PL/SQL tables
:

actually, i meant, what is your user interface? what kind of 'form' are you
referring to?

and once you store the data in the PL/SQL tables (quite ananlogous to
arrays, as Hans pointed out) how will the data in the arrays be used? will
it be ready by another process or does it need to be stored in the database?

++ mcs

I am using Oracle PL/SQL for Web. So basically creating web pages using
htp procedures. I mentioned the word forms because I am using a lot of
htp.form<aaaa) etc.

After the user inputs values for Subject, Course number and year.
E.g(for say 3 subjects at a time)

English 101 1999
French 235 1997
Math 450 2000

(these are the course that he/she has taken at their university and
want to know if these can be transferred to my univ. here..that is the
application i am working on)

and clicks on "Submit" button, I need to get these values from the
webpage and then run queries again the database tables that I have
using these values.

After the processing is done, the user(prospective student) would see
on the screen the equivalent courses at my university for his/her
entered courses and would know what courses of his/her can be
transferred here.

thanks & regards
- Ardy

<happyardy (AT) gmail (DOT) com> wrote

Have you looked at using App Express, as apposed to htp. form and such? It
is a great tool for what you are looking to do and its FREE!!

<happyardy (AT) gmail (DOT) com> wrote

:
: > :
: > : Thanks for your reply.
: > : I am using Oracle SQL Developer to code(if that is what you meant by
: > : development tools).
: > : I have been told to use PL/SQL tables since it would also be a good
: > : exercise for future development here. At this point I am really not
: > : that knowledgable to make an educated choice about whether PL/SQL
: > : tables are the best way to go for my application.
: > : thanks
: > : - Ardy
: > : I have been told to use PL/SQL tables
: > :
: >
: > actually, i meant, what is your user interface? what kind of 'form' are
you
: > referring to?
: >
: > and once you store the data in the PL/SQL tables (quite ananlogous to
: > arrays, as Hans pointed out) how will the data in the arrays be used?
will
: > it be ready by another process or does it need to be stored in the
database?
: >
: > ++ mcs
:
: I am using Oracle PL/SQL for Web. So basically creating web pages using
: htp procedures. I mentioned the word forms because I am using a lot of
: htp.form<aaaa) etc.
:
: After the user inputs values for Subject, Course number and year.
: E.g(for say 3 subjects at a time)
:
: English 101 1999
: French 235 1997
: Math 450 2000
:
: (these are the course that he/she has taken at their university and
: want to know if these can be transferred to my univ. here..that is the
: application i am working on)
:
: and clicks on "Submit" button, I need to get these values from the
: webpage and then run queries again the database tables that I have
: using these values.
:
: After the processing is done, the user(prospective student) would see
: on the screen the equivalent courses at my university for his/her
: entered courses and would know what courses of his/her can be
: transferred here.
:
: thanks & regards
: - Ardy
:

ok, that helps

have you used PL/SQL arrays before?
how are you currently passing your fields to your form's action procedure?
have you read the mod_plsql Users's Guide? the section 'Parameter Passing'
describes your options, which include each of your multi-valued fields being
passed as a single PL/SQL array parameter of your procedure.

++ mcs

"Mark C. Stock" <mcstockX@Xenquery .com> wrote

:
: <happyardy (AT) gmail (DOT) com> wrote in message
: news:1154820165.589749.30340 (AT) m79g2000cwm (DOT) googlegroups.com...
::
:: > :
:: > : Thanks for your reply.
:: > : I am using Oracle SQL Developer to code(if that is what you meant by
:: > : development tools).
:: > : I have been told to use PL/SQL tables since it would also be a good
:: > : exercise for future development here. At this point I am really not
:: > : that knowledgable to make an educated choice about whether PL/SQL
:: > : tables are the best way to go for my application.
:: > : thanks
:: > : - Ardy
:: > : I have been told to use PL/SQL tables
:: > :
:: >
:: > actually, i meant, what is your user interface? what kind of 'form' are
: you
:: > referring to?
:: >
:: > and once you store the data in the PL/SQL tables (quite ananlogous to
:: > arrays, as Hans pointed out) how will the data in the arrays be used?
: will
:: > it be ready by another process or does it need to be stored in the
: database?
:: >
:: > ++ mcs
::
:: I am using Oracle PL/SQL for Web. So basically creating web pages using
:: htp procedures. I mentioned the word forms because I am using a lot of
:: htp.form<aaaa) etc.
::
:: After the user inputs values for Subject, Course number and year.
:: E.g(for say 3 subjects at a time)
::
:: English 101 1999
:: French 235 1997
:: Math 450 2000
::
:: (these are the course that he/she has taken at their university and
:: want to know if these can be transferred to my univ. here..that is the
:: application i am working on)
::
:: and clicks on "Submit" button, I need to get these values from the
:: webpage and then run queries again the database tables that I have
:: using these values.
::
:: After the processing is done, the user(prospective student) would see
:: on the screen the equivalent courses at my university for his/her
:: entered courses and would know what courses of his/her can be
:: transferred here.
::
:: thanks & regards
:: - Ardy
::
:
: ok, that helps
:
: have you used PL/SQL arrays before?
: how are you currently passing your fields to your form's action procedure?
: have you read the mod_plsql Users's Guide? the section 'Parameter Passing'
: describes your options, which include each of your multi-valued fields
being
: passed as a single PL/SQL array parameter of your procedure.
:
: ++ mcs
:
:

here's an example. you should be able to adapt it to your logic. if any of
the syntax does not look familiar, make sure you take time to understand
what it's doing.

CREATE OR REPLACE PROCEDURE SAMPLE_FORM

AS

BEGIN

htp.p(user||' connected to SAMPLE_FORM @ ' || to_char(sysdate, 'mm/dd/yyyy
hh24:mi:ss'));



htp.p('<FORM ACTION=params1>');

htp.p('<TABLE>');



for x in 1..3

loop

htp.p('<TR><TH>One:</TH><TD><INPUT NAME=p1 VALUE=p1.'||x||' /></TD>');

htp.p('<TH>Two:</TH><TD><INPUT NAME=p2 VALUE=p2.'||x||'
/></TD></TR>');

end loop;



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

htp.p('<INPUT TYPE=SUBMIT />');

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



END;





CREATE OR REPLACE PROCEDURE params1
(
p1 in owa_util.vc_arr
, p2 in owa_util.vc_arr
)
AS
BEGIN
htp.p('<TABLE>');
htp.p('<TR><TH>Parameter</TH><TH>Value</TH></TR>');

for x in 1..p1.count
loop
htp.p('<TR><TD>p1('||x||')</TD><TD>'||p1(x)||'</TD></TH>');
end loop;

for x in 1..p2.count
loop
htp.p('<TR><TD>p2('||x||')</TD><TD>'||p2(x)||'</TD></TH>');
end loop;

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

END;

++ mcs