Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sql and dropdown window ASAP!! 2
- Thread starter kipp4Him
- Start date
-
1
- #2
SantaMufasa
Technical User
Kipp,
Unfortunately, PL/SQL does not offer any native support for Graphical User Interfaces (GUI). Therefore, there is no native support for things such as a "scroll window...allowing a person to select an item from the scroll window and assigning it to a variable."
If you have a (GUI) environment that accommodates input from external sources, then PL/SQL could be that external source, which "sends" commands to the GUI interface that do what you want. (For example, I regularly use PL/SQL to generate exotic HTML code that employs scroll bars and accepts input from web pages.)
Sorry to be the bearer of less-than-satisfactory news.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
@ 19:57 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:57 (11Nov04) Mountain Time
Unfortunately, PL/SQL does not offer any native support for Graphical User Interfaces (GUI). Therefore, there is no native support for things such as a "scroll window...allowing a person to select an item from the scroll window and assigning it to a variable."
If you have a (GUI) environment that accommodates input from external sources, then PL/SQL could be that external source, which "sends" commands to the GUI interface that do what you want. (For example, I regularly use PL/SQL to generate exotic HTML code that employs scroll bars and accepts input from web pages.)
Sorry to be the bearer of less-than-satisfactory news.
Mufasa(aka Dave of Sandy, Utah, USA)
@ 19:57 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 12:57 (11Nov04) Mountain Time
- Thread starter
- #3
SantaMufasa
Technical User
Kipp,
First, Oracle provides entire product lines that support Internet (html) interaction. (The "i" in "Oracle 9i" supposedly stands for "Internet".)
But rather than get into that entire topic (of Oracle products that support Internet/html applications), I'll suggest low-tech, no-cost experiment for you to see how PL/SQL can generate html-ready output. Once you see how to generate output from PL/SQL that your web-browser can accommodate, then you can add exotic complexity to your "simple" PL/SQL block to do whatever you know how to program with HTML.
Save the following code to a file on your hard disk, log into SQL*Plus, then at the SQL> prompt enter "@<filename you saved>":
If you do as it says, above, then you can see how SQL or PL/SQL can produce anything that is programmatically available to HTML, including the functionality you hoped for in your original post to this thread.
Let us know your findings and reactions.
Mufasa
(aka Dave of Sandy, Utah, USA)
@ 20:52 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:52 (11Nov04) Mountain Time
First, Oracle provides entire product lines that support Internet (html) interaction. (The "i" in "Oracle 9i" supposedly stands for "Internet".)
But rather than get into that entire topic (of Oracle products that support Internet/html applications), I'll suggest low-tech, no-cost experiment for you to see how PL/SQL can generate html-ready output. Once you see how to generate output from PL/SQL that your web-browser can accommodate, then you can add exotic complexity to your "simple" PL/SQL block to do whatever you know how to program with HTML.
Save the following code to a file on your hard disk, log into SQL*Plus, then at the SQL> prompt enter "@<filename you saved>":
Code:
set serveroutput on
set echo off
set pagesize 0
set trimspool on
set feedback off
spool c:\yada.html
select 'This is sample output from Oracle that I can view from my web browser.' from dual
/
spool off
prompt
prompt Now, enter "C:\yada.html" in your web browser's URL/address field and press [Enter]
promptIf you do as it says, above, then you can see how SQL or PL/SQL can produce anything that is programmatically available to HTML, including the functionality you hoped for in your original post to this thread.
Let us know your findings and reactions.
Mufasa(aka Dave of Sandy, Utah, USA)
@ 20:52 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 13:52 (11Nov04) Mountain Time
-
1
- #5
Kipp -
Are you looking for a way to have Oracle generate a static file for you or are you looking for dynamic page generation?
If the latter, do you already have a web server set up that can interact with your Oracle database (i.e., do you already have a DAD set up, etc)?
Are you looking for a way to have Oracle generate a static file for you or are you looking for dynamic page generation?
If the latter, do you already have a web server set up that can interact with your Oracle database (i.e., do you already have a DAD set up, etc)?
- Thread starter
- #6
Thanks Very Much.... I think this is the way I need to go.
I already have a table with 3 columns in it, I want to pull the data that is in this table and put it into a pulldown list so that a user can either choose a value from the list and save that value to a variable or give them the option from this window to add or delete items from the table. Any ideas if the above type code would be used?
Thanks
Kipp
I already have a table with 3 columns in it, I want to pull the data that is in this table and put it into a pulldown list so that a user can either choose a value from the list and save that value to a variable or give them the option from this window to add or delete items from the table. Any ideas if the above type code would be used?
Thanks
Kipp
Although there's nothing wrong with using the spooling approach used by SantaMufasa, I find that generating pages from Oracle is usually a bit too complex for simple SQL solutions. Instead, I use a PL/SQL approach and the utl_file package. Here is a snippet that will give you some idea of what I mean:
Code:
DECLARE
l_file utl_file.file_type := utl_file.fopen('your_directory_path','yada.html','W');
BEGIN
utl_file.put_line(l_file, '<HTML><HEAD><TITLE>This Is My Drop List</TITLE></HEAD>
<BODY><H2>Here Is My Test Drop List</H2><HR>
utl_file.put_line(l_file,'<FORM METHOD=POST ACTION="YOUR URL CALL HERE GOES HERE">');
utl_file.put_line(l_file,'<B>YOUR PROMPT GOES HERE: <SELECT NAME="your_variable_name">');
FOR i IN (SELECT the_value, the_meaning FROM my_table) LOOP
utl_file.put_line(l_file,'<option VALUE="'||i.l_value||'">'||i.the_meaning);
END LOOP;
utl_file.put_line(l_file,'</SELECT></B><P>
<INPUT TYPE="submit" VALUE="LABEL FOR YOUR SUBMIT BUTTON">
<INPUT TYPE="reset" VALUE="LABEL FOR YOUR CLEAR FORM BUTTON"></BODY></HTML>');
utl_file.fclose(l_file);
END;I should note that the preceding code assumes the query that populates the drop list will bring back distinct values from your lookup table. If this is not the case, then you would want to change that line to
Code:
FOR i IN (SELECT DISTINCT the_value, the_meaning FROM my_table) LOOP- Thread starter
- #9
utl_file is an Oracle-supplied package that allows your code to read from and write to files. There are certain constraints on it (most important of which is the subdirectory must be set up correctly for Oracle to access it), so I recommend you read about this package in the "Suppled Packages" manual.
- Thread starter
- #11
- Thread starter
- #12
- Thread starter
- #13
- Thread starter
- #14
That depends on quite a few things - which brings us back to my original question. How are you actually accessing the database? I use the Apache server setup that comes with Oracle and use an Oracle DAD to access our databases. Unfortunately, this is about the only technique I feel qualified to discuss with you.
Give us some idea of what your setup looks like and we can go from there.
Give us some idea of what your setup looks like and we can go from there.
- Status
