?
Solved

Oracle APEX, PLSQL, pre-loading values.

Posted on 2014-01-31
11
Medium Priority
?
975 Views
Last Modified: 2014-02-04
Not sure how i can achieve this or if PL/SQL would be even be the appropriate method for doing this.

This is the scenario.

I have a table with 2 columns. Category, Product, .

In apex i have an application with 2 drop down list,

1 is for Category and the other is for product,

I would like to select a Category, and for the product drop down list to be populated with the products within that category, without refreshing the page.

I can achieve it now with a sql query on the drop downlist, if I submit the page on selection. But this is not efficient.

EG:

the table may have data like this
CATEGORY	PRODUCT
-----------------------
Soda		Pepsi
Soda		7up
Soda		Diet Coke
Cereal		Frosted Flakes
Cereal		Cheerios
Cereal		Apple Jacks
Cereal		lucky charms
Yogurt		Chobani
Yogurt		Dannons

Open in new window


I would like to select Soda on my first drop down list, and for the 2nd drop down list to only have pepsi, 7up, diet coke.


Can someone please set me on the right path.

Again, the key is to achieve this without refreshing the page.
0
Comment
Question by:FutureDBA-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 2

Accepted Solution

by:
bcarlis earned 2000 total points
ID: 39830846
See if the helps... has pictures and everything
http://www.inside-oracle-apex.com/oracle-apex-4-0-cascading-lovsselect-lists/

Bill
0
 

Author Comment

by:FutureDBA-
ID: 39831095
I can achieve that now using the built-in apex function.

My problem is that I need to do this on a detail of a MDF where this function is not available.

I will accept this solution and reword my question.

Thanks
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 39831099
What is MDF?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 2

Expert Comment

by:bcarlis
ID: 39831107
This is what I use to do in version 3.1

1.) HTML Region - to contain Javascript below.
	
 function f_getLOV(pSelectName,pLOVtype,parg1,parg2,parg3,parg4){
   var l_Return = null;
   var l_Select = $x(pSelectName);
   var ajaxReq = new htmldb_Get(null,&APP_ID.,
                      'APPLICATION_PROCESS=odpPopulateLOV',
                      $v('pFlowStepId')); 
   ajaxReq.addParam('x01',pLOVtype);
   ajaxReq.addParam('x02',parg1);
   ajaxReq.addParam('x03',parg2);
   ajaxReq.addParam('x04',parg3);
   ajaxReq.addParam('x05',parg4);
   //l_Return = ajaxReq.get('XML');
   l_Return = ajaxReq.get();
   alert('['+l_Return+']');

   if(l_Return && l_Select){
     var l_Count = l_Return.getElementsByTagName("option").length;
     l_Select.length = 0;
     for(var i=0;i<l_Count;i++){
       var l_Opt_Xml = l_Return.getElementsByTagName("option")[i];
       appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'), 
                                         l_Opt_Xml.firstChild.nodeValue);
     }
   }
 }
 function appendToSelect(pSelect, pValue, pContent){
   var l_Opt = document.createElement("option");
   l_Opt.value = pValue;
   if(document.all){
     pSelect.options.add(l_Opt);
     l_Opt.innerText = pContent;
   }else{
     l_Opt.appendChild(document.createTextNode(pContent));
     pSelect.appendChild(l_Opt);
   }
 }
2.) Create, unless already exist, three Hidden Text Items:
    ODP_ITEM_VALUE, ODP_SECOND_ITEM_NAME, ODP_CUR_ALL_IND

3.) Create a Application Process
 a. Go to Shared Components
 b. Application Processes
 c. Create a process
        On Demand: Run this application process when requested by a page process.
        Name - dependant on the select
        Ex: odp_campus_list
        Type:  PL/SQL Anonomous Block
        No conditions
 
declare
  l_counter   number := 0;
  ls_LOV_type varchar2(500):= wwv_flow.g_x01; 
  ls_arg1     varchar2(500):= wwv_flow.g_x02; 
  ls_arg2     varchar2(500):= wwv_flow.g_x03;
  ls_arg3     varchar2(500):= wwv_flow.g_x04; 
  ls_arg4     varchar2(500):= wwv_flow.g_x05; 

  encoded     varchar2(50);
begin
  owa_util.mime_header('text/xml', FALSE );
  htp.p('Cache-Control: no-cache');
  htp.p('Pragma: no-cache');
  owa_util.http_header_close;

  if(ls_LOV_type = 'ORG_USERS')then
    htp.prn('<select><option value="">- User(s) -</option>');
    for rec in ( select U.DISPLAY_NAME display, U.USER_ID rtn
                   from UM_USER U, UM_USER_ORG_XREF UO
                  where U.USER_ID = UO.USER_ID
                    and instr(':'||ls_arg1||':',
                              ':'||UO.ORG_ID||':') > 0
                  order by upper(U.DISPLAY_NAME)) loop
      htp.prn('<option value="' || rec.rtn || '">' ||rec.display|| '</option>');
      l_counter := l_counter + 1;
    end loop;
    htp.prn('</select>');
  elsif(ls_LOV_type = 'MODULE_APPS')then
    htp.prn('<select><option value="">- Application(s) -</option>');
    for rec in ( Select distinct A.APPLICATION_NAME||' - '||
                           A.APPLICATION_ID display, A.APPLICATION_ID rtn
                   from APEX_APPLICATIONS A
                  where CASE 
                          WHEN nvl(ls_arg1,0) = '0' THEN 1
                          WHEN EXISTS (select 1 from UM_MODULE_APP_PAGE_XREF AP
                                        where AP.APP_ID = A.APPLICATION_ID
                                          and AP.STATUS_ID =1 
                                          and AP.MODULE_ID = ls_arg1) THEN 1
                          ELSE 0
                        END = 1 
              order by upper(A.APPLICATION_NAME||' - '||A.APPLICATION_ID)) loop
      htp.prn('<option value="' || rec.rtn || '">' ||rec.display|| '</option>');
      l_counter := l_counter + 1;
    end loop;
    htp.prn('</select>');
  elsif(ls_LOV_type = 'APP_PAGES')then
    htp.prn('<select><option value="">- Page(s) -</option>');
    for rec in ( Select distinct display, rtn from(
                    Select 'App: '||P.APPLICATION_ID||
                        ' | Page: '||PAGE_NAME||' ('||P.PAGE_ID||')' display, 
                        P.PAGE_ID rtn
                     from WURS_APPLICATION_PAGES P
                    where CASE 
                            WHEN nvl(ls_arg1,0) = '0' 
                             AND nvl(ls_arg2,0) = '0' THEN 0
	                    WHEN nvl(ls_arg1,0) != '0' 
                             AND nvl(ls_arg2,0) != '0' 
                             AND EXISTS (select 1 from UM_MODULE_APP_PAGE_XREF AP
		                          where AP.APP_ID = P.APPLICATION_ID
                                            and AP.PAGE_ID = P.PAGE_ID
		                            and AP.STATUS_ID =1 
		                            and AP.APP_ID = ls_arg2
                                            and AP.MODULE_ID = ls_arg1) THEN 1
	                    WHEN nvl(ls_arg1,0) != '0' 
                             AND nvl(ls_arg2,0) = '0' 
                             AND EXISTS (select 1 from UM_MODULE_APP_PAGE_XREF AP
	                                  where AP.APP_ID = P.APPLICATION_ID
                                            and AP.PAGE_ID = P.PAGE_ID
	                                    and AP.STATUS_ID =1 
                                            and AP.MODULE_ID = ls_arg1) THEN 1
	                    WHEN nvl(ls_arg1,0)  = '0' 
                             AND nvl(ls_arg2,0) != '0' 
                             AND P.APPLICATION_ID = ls_arg2 THEN 1
	                    ELSE 0
                          END = 1 )
                       order by 1 ) loop
      htp.prn('<option value="' || rec.rtn || '">' ||rec.display|| '</option>');
      l_counter := l_counter + 1;
    end loop;
    htp.prn('</select>');
  end if;

end;

  4.) Add "onchange" statement to the first Select List
	onChange="javascript:{f_getLOV('P211_APP_ID','MODULE_APPS',this.value); }"
	onChange="javascript:{f_getLOV('P211_PAGE_ID','APP_PAGES',$v('P211_MODULE_ID').value,this.value); }"

Open in new window

0
 

Author Comment

by:FutureDBA-
ID: 39831155
you do this on the detail of a MDF?
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 39831158
What is an MDF?
0
 

Author Comment

by:FutureDBA-
ID: 39831175
Master Detail Form
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 39831178
Ahh
Yes I Used This in a form that had to cascading LOV's as you were talking about at the beginning. Doesn't matter if it's in a master detail or not
0
 

Author Comment

by:FutureDBA-
ID: 39831264
Do i need to add any items to capture values?
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 39832137
Actually, that link I sent you that has the pictures from Patrick will work in  MDF
Because the way I used to do it is the old way, now you need to do it with the automatic way that Patrick shows in his Example
0
 
LVL 2

Expert Comment

by:bcarlis
ID: 39832146
Is this a tabular form? I saw another post that looks just like yours thought you might've created another one.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question