Solved

Oracle APEX, PLSQL, pre-loading values.

Posted on 2014-01-31
11
936 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-
  • 7
  • 4
11 Comments
 
LVL 2

Accepted Solution

by:
bcarlis earned 500 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now