Skip to Main Content
  • Questions
  • Dependent drop down lists in Pl/SQL cartridge


Question and Answer

Connor McDonald

Thanks for the question, Karl.

Asked: May 22, 2000 - 5:11 am UTC

Last updated: November 04, 2006 - 12:24 pm UTC

Version: 8.05

Viewed 10K+ times! This question is

You Asked

I am developing a web front end for a client and they require a search screen that has drop down lists. These drop down list choices are dependent, i.e. the choices of the second list are dependent on what the user chooses from the first list.

Can't find info on how to do this, do you have any idea where I may be able to find any examples or decent documentation.

and we said...

I've put a quick demo up. It work on EMP and DEPT. I have a list of deptno's, as you change their value - the list of enames in that DEPT updates itself. To test it see:

</code> <code>

What we've done is to use frames. One hidden frame actually rewrites itself every time we change the DEPTNO. We send the new deptno up to the server and get the list of enames for that DEPTNO. This hidden frame then truncates the old list and fills it with the new values. The code for doing this is as follows (sample for the emp and dept tables, you would recode for your environment of course)

set define off

create or replace
package test_onchange as

procedure main;

procedure frame_main;

procedure frame_hidden(p_deptno varchar2 default null);

end test_onchange;

create or replace
package body test_onchange as

g_addToList constant varchar2(512) default '
<script language="javascript">
function addToList( n, v )
with ( document.forms[0].p_ename )
options[length] = new Option( n, v );

g_lookUp constant varchar2(512) default '
<script language="javascript">
function lookUp( deptNo )
document.forms[0].p_deptno.value = deptNo;

procedure main is

htp.framesetOpen( crows=> '100%,0' );
htp.frame( cname => 'mainFrame',
csrc => 'test_onchange.frame_main' );
htp.frame( cname => 'hiddenFrame',
csrc => 'test_onchange.frame_hidden',
cattributes => ' MARGINWIDTH=0 ' ||

end main;

procedure frame_main

htp.p( g_addToList );
htp.formOpen( null );

htp.p( 'Deptno:' );

htp.formSelectOpen( 'p_deptno',
cattributes => 'onChange="parent.hiddenFrame.'||
'lookUp( this.options[' ||
'this.selectedIndex].value );"' );

for c in ( select deptno, decode(deptno,10,'Y',null) sel
from dept )
htp.formSelectOption( c.deptno, c.sel,
cattributes => 'value="' || c.deptno || '"' );
end loop;


htp.p( 'Ename:' );
htp.formSelectOpen( 'p_ename' );
for c in ( select ename from emp where deptno = 10 )
htp.formSelectOption( c.ename );
end loop;
end frame_main;

procedure frame_hidden( p_deptno varchar2 default null )
l_not_found boolean := true;

htp.p( g_lookUp );

htp.formOpen( 'test_onchange.frame_hidden' );
htp.formHidden( 'p_deptno', null );

htp.p( '<script language="javascript">' );
htp.p( 'with( parent.mainFrame )' );
htp.p( '{' );
if p_deptno is not null
htp.p( ' document.forms[0].p_ename.length = 0;' );
for c in ( select ename
from emp
where deptno = p_deptno
order by ename )
htp.p( ' addToList( "' || c.ename ||
'", "' || c.ename || '" );' );
l_not_found := false;
end loop;
end if;

if l_not_found
htp.p( ' addToList( "NULL", "" );' );
end if;

htp.p( ' document.forms[0].p_ename.' ||
'selectedIndex = 0;' );
htp.p( ' document.forms[0].p_ename.focus();' );
htp.p( '}' );
htp.p( '</script>' );

end frame_hidden;

end test_onchange;

set define on


  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Excellent Answer

Munzer, December 09, 2001 - 11:38 am UTC

Tom is the best there was, is and will be

Easier Way

Munzer, February 21, 2002 - 11:51 pm UTC


There is much easier way to do dependent lists in Javascript and without using frames.

You bascially define an array for all lookup values and then create a function that assigns the array values to lookup column based on the values in other column.

If you are interested I can show you the code for a simple example.

Also, I managed to build the array dynamically using PL/SQL so that when you create the page the array will be defined from values in oracle table.

Thank you,

Tom Kyte
February 22, 2002 - 10:49 am UTC

Easier to you maybe BUT -- it would demand you pull ALL of the data to the client.

Say you have 100 departments.
Each department has 100 employees.

You would pull 10,000 employee records to the client when the page first comes down.

I would pull 100 employee records to the client ON DEMAND.

This supplied implementation scales very well as the data grows (it can be used for non-trivial sets of data). As well, it gets the current data from the database as you click on the "master" record -- were as you would show the data when the page was generated.

Me -- I like to keep web pages as SMALL as possible and look for ways to accomplish that goal over all else. Nothing worse then a 500k html page -- I prefer a 20/40k page at most (given that I work on dialup/isdn frequently).

I would like to see Munzer 's solution

Asha, June 22, 2002 - 4:03 pm UTC

Hi Munzer ,
I am interested in your solution . Could you please email it to me at



Munz, July 19, 2002 - 9:46 pm UTC


1. I did create a description lookup for an item number. If user select a stock number, description will pop up. If user selects a '99999' then I ask user to enter a description as this part does not have an assigned number. I just need to enter a number in table so I store a 99999.

Problem now is that user wants the description also to be dropdown, so he can select description and item will pop up which is not a problem. However how can you handle the '9999' part number problem. If the description is a drop down then user can not enter a free text in the description. Do you know of a solution?


munz, July 21, 2002 - 4:54 pm UTC


1. I did create a description lookup for an item number. If user select a
stock number, description will pop up. If user selects a '99999' then I ask
user to enter a description as this part does not have an assigned number. I
just need to enter a number in table so I store a 99999.

Problem now is that user wants the description also to be dropdown, so he can
select description and item will pop up which is not a problem. However how can
you handle the '99999' part number problem. If the description is a drop down
then user can not enter a free text in the description. Do you know of a

Tom Kyte
July 21, 2002 - 11:54 pm UTC

not off the top of my head but now that you've seen what javascript can do -- you can buy a javascript book and really learn it!


mo, September 26, 2002 - 10:59 pm UTC


1. Can you explain what you mean by a hidden frame?

2. Is it different than a regular frame in that you can not tell it in a frame by itself?

3. If you can not use a regular frame since it does not support handicapped people I think because you can not tab into it would a hidden one be different. I see that you can tab into it like a regular form?

4. If I added a submit button, would it submit deptno and employeeid. or you have to so something different.

Thank you,

Tom Kyte
September 27, 2002 - 7:36 am UTC

1) a frame you cannot see. (obviously?)

2) it just has no dimensions, it is 0 by 0, you cannot see it, but yet -- its there.

3) we don't want anyone to tab into it. it is a scratch pad for programming purposes -- handicapped or not, there is no user interaction in this frame.

4) of course, it is just a form after all.

dependent dropdown lists

mo, September 28, 2002 - 1:28 pm UTC


1. Can you explain to me how you are doing this using a hidden frame.

I see that you have one procedure "main" where you are creating two frames and each frame is calling a pl/sql procedure. The main frame procedure creates a form with deptno and employee. The frame hidden procedure reads dept no parameter and then run a sql statement to find employees.

How are you setting the empname in the first frame to the results of the second frame?

2. Sine I have several web pages or sometime one web page with different sets of dependent dropdowns do i have to create this package for each two dependent dropdowns.

3. I use owa_util.listprint a lot if times except when I need to attach onCHange javascript event handler with the <select> tag because I guess you can not do it with the owa_util.listprint.

Can you implement this code for dropdwons using owa_util.listprint?

4. ALso, my dropdown usually show department name but I save department code. Would this create a problem here in this code? Would you only change the frame_main procedure?

Thank you,

Tom Kyte
September 28, 2002 - 1:35 pm UTC

1) suggest you read the resulting pages so you can see the javascript. the hidden frame just writes to the javascript variables in the visible frame.

2) don't know, guess it depends on how you code the hidden frame doesn't it. You can write a generic routine that could be reused, I'm sure.

Take the *concepts*, the *ideas* presented and mutate them to meet your needs.

3) suppose so, you have the code for listprint (it is shipped as source code). you can make it do whatever you want!

4) it would not cause a problem.

Before you use this -- you should understand javascript, frames and how this stuff all works. If you do -- simply looking at the generated pages should cause you to smack your forehead and go "of course, I see".


mo, September 28, 2002 - 2:24 pm UTC


Your example resultin pgage is:
<FRAMESET ROWS="100%,0">
<FRAME SRC="ask_tom.test_onchange.frame_main" NAME="mainFrame">
<FRAME SRC="ask_tom.test_onchange.frame_hidden" NAME="hiddenFrame" MARGINWIDTH=0 MARGINHEIGHT=0 NORESIZE>

As you can see there is nothing there from html or javascript.

2. I do not know how hard it would be to change the code for the listprint but it may take considerable time as you have to undertand the whole code someone else wrote. I rather use <SELECT> and insert an implicit cursor to generate the values. It is easier and faster unless you think otherwise?

3. I do understand javascript and frames reasonably well. A frame simply another html page within a page. Nothing complex about it. To display 10 pages in one windows ou create 10 frames.

4. To make a generic package I have to subsitute every reference to an object in either pl/sql or javascript as a parameter and name the object in the parameter?Correct.

Thank you,

Tom Kyte
September 28, 2002 - 2:34 pm UTC

(hint -- view the frame source, heck run it in SQLPLus and see what it spits out)

PLAY with it. Install it on your system.

2) i wrote listprint. It is pretty trivial. It is up to you.


sanjay, October 01, 2002 - 10:38 am UTC


how is this possible on oracle forms 6i.

i am new to forms


Tom Kyte
October 02, 2002 - 9:21 am UTC

this is even easier in forms -- but -- heres the catch -- you'll really need to read the documentation.

This is a master detail block basically.

Would like to see Munzer's solution

Neeta Ranchod, December 24, 2002 - 3:56 am UTC

Hi Munzer ,
I deperately need a solution to this. I am new to web PL/SQL and to Java Scripting. I am interested to see your solution . Could you please email it to me at Your response will be greatly appreciated.


Would like to see Munzer's solution

kiffu, December 16, 2004 - 2:54 pm UTC

Hi Munzer ,
I desperately need a solution to this. I am new to web PL/SQL and to Java Scripting. I am interested to see your solution . Could you please email it to me at Your response will be greatly appreciated.


Broken Link

Mike Elms, August 02, 2005 - 8:13 am UTC

</code> <code>
I can not get to the example.

Tom Kyte
August 02, 2005 - 10:13 am UTC

To Mike Elms

Justin, August 02, 2005 - 10:34 am UTC

You and I were browsing this article on the same day, how odd/cool.

Here is what I found from Tom elsewhere regarding broken links, and this fixed it.


osi = asktom, guess they removed osi from dns.

I.E. </code> <code>

Tom Kyte
August 02, 2005 - 1:46 pm UTC

well -- i had to fix the stored procedure too :) I didn't have emp and dept for some reason...

Very odd

Justin, August 02, 2005 - 11:59 am UTC


I am working with this example to try to build a web interface for a Business Analyst to update Data Dictionary Comments for tables and columns in our application schema.

I want two frames, one at the top with a drop-down list box that will be populated with schema tables.
Then, when you choose a table in the top frame, the bottom frame will be populated with two things: (maybe this means 2 more frames?) The first thing I want is the table name, and a text area for the user to type a comment, and the second area would be all the column names with text areas to the right of their name allowing the user to type comments for the columns.

That's the general gist of what I want to do, with a few more things to come later.

I am working with the PL/SQL web toolkit and am having some funky issues.

I have created a procedure as follows which generates the drop down list box I need in the top frame to show table names:

create or replace procedure tf

htp.p( 'Select a Table Name:' );

htp.formSelectOpen( 'p_table_name' );

for c in ( select table_name
from user_tables )
htp.formSelectOption( c.table_name, cattributes => 'value="' || c.table_name || '"' );
end loop;


end tf;

I can exec that procedure from SQL Plus just fine, and exec owa_util.showpage just fine as well. Likewise, I can also execute that program via a web browser through a DAD and it works fine.

Here's the weird part. If I simply rename that program to topframe, instead of tf. I can still execute it in SQL Plus just fine, and showpage it, but when I try to get to it in the browser, it bombs out.

Looking in the error log on the application server, I find this error message:

[Tue Aug 02 11:33:58 2005] [error] mod_plsql: /justin/topframe HTTP-404

Well that's not true, it does exist.

Any thoughts or questions I need to answer for your help?

Tom Kyte
August 02, 2005 - 2:02 pm UTC

is topframe granted to the credentials used by the DAD (tf might have been and create or replace would preserve it each time but when you renamed it to topframe, you might have missed a grant to the DAD account?)

A bit off the point...

andrew, August 02, 2005 - 1:35 pm UTC

Your analyst may find a simple script much easier to add and edit comments for tables and columns. Sorting the statements in excel allows you to easily add the same comments for all those pesky key and audit columns...

set linesize 200 trimspool on pagesize 0 echo off
spool t.sql

select 'comment on table '||table_name||
' is '''||replace(comments, '''', '''''')||''';'
from user_tab_comments;

select 'comment on column '||table_name||
'.'||column_name ||' is '''||replace(comments, '''', '''''')||''';'
from user_col_comments;

spool off

Data Dictionary Comment Application

Justin, August 02, 2005 - 3:23 pm UTC

I am going through the Oracle9i Application Server Administrator's Guide right
now to learn more about DAD's, and make sure I didn't miss anything.

I chose a "General DAD" and called it "justin" using the user "justin" with the
correct password, and correct database alias as the connect information. So the
user embedded in the DAD is the same user that owns the procedures.

Incidently, I can't get package references to work at all either. If I use "tf",
I get my page. If I use "topframe", and some other names, I get that error. I
created a package called "ddca" and put "tf" inside of it, thus calling
"", and I get the error. I've tried it with the username also, and it
doesn't work.

Some work, some don't, it's really strange.

Here's one for you...

If this works:


Should this work?: <code>

I'm beginning to think it's a bug, but I have no way to know I guess.

Here is my version information:
D:\oracle\9iAS_infr\Apache\Apache>apache -version
Server version: Oracle9iAS/ Oracle HTTP Server
Server built: Jul 2 2003 09:29:25

Anything else we can do to try to figure this out?

As to the other posting about using that script and excel, I agree, there are
lots of ways this could be done, but my user is non-technical, and does not have
and Oracle Client install. I prefer a nice simple web interface they can easily
understand and operate.

Thanks for the suggestion though!

I dropped the DAD

Justin, August 02, 2005 - 3:43 pm UTC

I dropped the DAD and recreated it and things are working as they should be now!


So when the user comes into the application, the first table in the list will be showing in the drop-down list box in the top frame. And in the bottom frame will be the table name, with a text field next to it for displaying/changing the comment. And below that will be all the columns with text fields next to them for diplaying/changing those comments.

When the user drops down the list box, and chooses a different table, I want the bottom frame to change as well.

Am I going to need to use the hidden frame technique to do that, or is it just straight execution of another procedure "on click" so to say?


Tom Kyte
August 02, 2005 - 4:36 pm UTC

That'll be a question for someone that knows java script :) Sorry -- outside of my realm of expertise.

(hey, have you considered HTMLDB to build the UI??)

</code> <code>

Another Option for Linked Drop Downs - AJAX

Michael Smith, August 02, 2005 - 5:54 pm UTC

Another option is to use XMLHTTTRequest to issue a background asynchronous request to update the select boxes.
Below is an example (designed to run in a package called Utility).

Procedure xmlSelectJS
var xmlHttpRequest;
/**Create and load the data using the XMLHttpRequest
function loadXmlData(url, obj)
// create the object, careful to the MSFT/Other method
if (window.XMLHttpRequest)
xmlHttpRequest = new XMLHttpRequest();
else if (window.ActiveXObject)
xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP"); //("Microsoft.XMLHTTP");
//alert("starting xml request");
// executing the request, passing the targetted object"GET", url, true);
xmlHttpRequest.onreadystatechange = function () {if (xmlHttpRequest.readyState==4&&xmlHttpRequest.status==200) processRequestChange(obj, xmlHttpRequest.responseText);};
function fillSecond(oItem,sTableName,oDest,pCol, cCol, vCol){ var nValue = oItem[oItem.selectedIndex].value;
var url="utility.sel_lookup?table_name="+sTableName+"&parentVal="+nValue+"&parentCol="+pCol+"&codeCol="+cCol+"&valCol="+vCol;

* Handle the events of the XMLHttpRequest Object
function processRequestChange(obj,resp)
if (xmlHttpRequest.readyState == 4)
if(xmlHttpRequest.status == 200)
while (obj.length > 0)
var opt_array = resp.split(";");

for (var i=0;i<opt_array.length-1;i++)
var option = document.createElement("option");
var elem_arr = opt_array[i].split("|");
option.value = elem_arr[0];
var labelNode= document.createTextNode(elem_arr[1]);
{alert("Error loading page\n"+ xmlHttpRequest.status +":"+ xmlHttpRequest.statusText);

// end of the request, change the status zone
document.getElementById("statusZone").innerHTML = ""
// Indicates that the client is *busy*
document.getElementById("statusZone").innerHTML = "<b style=''color:red'' >Loading..."


end xmlSelectJS;

Procedure sel_Lookup(table_name in varchar2, parentVal in number, parentCol in varchar2, codeCol in varchar2, valCol in varchar2)

stmt varchar2(1000);
code number;
value varchar2(100);
iCursor sys_refcursor;

stmt:='select '||codeCol||' code, '||valCol||' value from '||table_name||' t where '||parentCol||'=:pVal';

open iCursor for stmt using parentVal;

fetch iCursor into code, value;
exit when iCursor%notFound;
end loop;

close iCursor;


Now call this with something like this

htp.p('<tr><td>First Select: ');
htp.p('</td><td class=val><select size=1 name="first_select" onchange="fillSecond(this,''class_lk'',document.form_name.second_select,''category_code'',''class_code'',''class_value'');">');
for x in (select category_code, category_value from category_lk order by category_code)
if x.category_code=category_sel then
htp.p('<option value="'||x.category_code||'" selected>'||x.category_value||'</option>');
htp.p('<option value="'||x.category_code||'"> '||x.category_value||'</option>');
end if;
end loop;
htp.p('&nbsp;&nbsp; <select size=1 name="second_select">');
for x in (select class_code, class_value from class_lk c where c.category_code=nvl(category_sel,1) order by category_code)
if x.class_code=class_sel then
htp.p('<option value="'||x.class_code||'" selected>'||x.class_value||'</option>');
htp.p('<option value="'||x.class_code||'" >'||x.class_value||'</option>');
end if;
end loop;

Hope this helps someone,


putting the frames into a form

Denise, August 02, 2005 - 7:25 pm UTC

Hi Tom. I was able to get the dependent dropdown lists to work using the frames with PL/SQL. Thanks! But how do you add these frames to a form that already has multiple fields on it (other dropdowns, text fields, buttons)? I originally had these 2 dropdowns in their own Htp.tableRowOpen;.....Htp.tableRowClose; (when I wasn't using dependent data), but I can't figure out how to add the dependent dropdowns in using the frames. Your help is appreciated.

Tom Kyte
August 02, 2005 - 7:30 pm UTC

sorry, not entirely sure what the issue is?

putting the frames into a form

Denise, August 02, 2005 - 7:55 pm UTC

As soon as I put other code (for other dropdowns or fields) in the main procedure, the frames no longer appear. Not sure how to put the 2 together.
I put this in the main procedure right before htp.framesetOpen( crows=> '100%,0' ) and now the dependent dropdowns are not appearing.

htp.tablerowopen(cattributes => 'CLASS="fdlbl1"');


A reader, August 02, 2005 - 7:57 pm UTC

HTML DB has a very elegant way to do this quite easily as Sergio shows at

</code> <code>

Can you do this without using Frames?

Adele, August 04, 2005 - 11:16 am UTC

Is there a way of doing this without using frames? I have the 2 dependent dropdowns as well as other independent drop downs on the page and many other objects and can't use the frames because they do not work in the <body> tags. Any suggestions would be helpful?

Dependant dropdown lists/Putting the frames onto a form

Bob, November 23, 2005 - 3:23 pm UTC

Hi Tom. My inquiry deals with how to apply the dependant dropdown list onto an already established form. (much like Denise from Florida). The form I wish to add the dropdown list to is an already working form with many radio buttons, list items, and text fields - this form was created via PL/SQL without using frames. What is the best way to apply the javascript solution so that I don't have to totally re-create the form?

Tom Kyte
November 23, 2005 - 8:05 pm UTC

don't know, don't know what your form entails. I can only show this method of doing a drop down...

Re: URL once leaving frameset

Andrew Tindle, November 30, 2005 - 11:11 am UTC

Hi Tom:

I've created a query form using dependent drop down lists based on your code in this example. However, I find that when I then move onwards to subsequent pages; i.e. after pressing the query button to return a results page, that the URL always displays the test_onchange.main procedure as the URL; i.e. it seems that all subsequent pages are now being called in the frameset.

How can I get to leave the frameset and show the proper URL?

This even happens when I do a owa_util.redirect_login;e.g. on a button to send back to main menu page?


Tom Kyte
November 30, 2005 - 3:04 pm UTC

it's been a while, but wouldn't target=_top in a link you click on do that?


Carl Backstrom, December 23, 2005 - 5:27 pm UTC

HTML DB 2.0 has a built in AJAX library that make features like this a bit easier to implement.

Here's some examples with code.
Dynamic select lists.

Report with a detail row

A generic return content from an on demand process. <code>


dropdwown list

sam, November 03, 2006 - 6:39 pm UTC


is there a way to display a long item description in two lines in a HTML dropdown list. Some description are long and would not fit in the order form description field that user selects sometimes. Is there a way around it?

Tom Kyte
November 04, 2006 - 12:24 pm UTC

ask an html person?

reloading the whole page

JD, February 06, 2007 - 3:24 am UTC

Hi Tom,

I also got drop down lists which are dependent on the previous drop down lists. Because there are many (about 30) I want the page to reload every time I select something. The selected option of the list should be memorized in a variable and when reloading the page, it should act as a parameter of the procedure, so that I can use it for a new select statement for the other drop down list.
My problem is now: It don't get it to pass the selected option (the variable) to the same procedure when reloading.

Here is a fragmet of the whole code:


lv_jump_page_name_kap VARCHAR2(100) := 'in.eingabe'||'?kap='||kap;
-- html head
htp.tableData( cvalue => 'Kapitel' || ':', cattributes => 'width="30%" height="35" align="left" valign="middle" class="tableheader"' );
htp.formSelectOpen( cname => 'kap', cattributes => 'class="selectbox" onChange="MM_changeProp('''||'form1'||''','''',''action'','''||vpath||lv_jump_page_name_kap||''');
javascript:self.document.'||'form1'||'.submit();"' );
-- a lot of options with htp.formSelectOption

IF kap = kap THEN
htp.formSelectOpen( cname => 'tit', cattributes => 'class="selectbox"' );
-- the options depending on kap created with a selection

END eingabe;

END Liste;

Again: my Problem is only the variable kap.
When selecting an option from the kap drop down list, the page reloads with: there is no value for kap...
when manually editing the value to e.g.:
the second list appears in addiction to the first.

I got to admit, I am new at web applications.

Dependent Dropdown pl/sql

Suchitra Kumar, October 13, 2010 - 2:04 am UTC

Hi Munzer ,
I am interested in your solution . Could you please email it to me at


Bea, November 10, 2014 - 9:33 pm UTC

Do you happen to have the solution for these dynamic drop-downs written in PL/SQL?

I would be very interested to receive the solution. Could you please email me at

Thanks in advance,


StellaJames, July 14, 2021 - 10:28 am UTC

Static Values: Enables you to populate the drop-down list with a group of values that do not change. See "Populating a Drop-Down list with Static Values" for more information.

Entity Link: Enables you to populate the drop-down list with the names of all entities created from the selected specification. See "Populating Drop-Down Lists with Entity Links Using Existing Specifications" and "Populating Drop-Down Lists with Entity Links Using New Specifications" for more information.

Query: Enables you to populate the drop-down list with the results of a query that you define. See "Populating Drop-Down Lists with Query Results" and for more information

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library