Solved

dynamic lov in oracle form 11gR2

Posted on 2014-12-01
14
129 Views
Last Modified: 2015-12-03
DYNAMIC LOV


HI i like to create dynamic lov

i have two lov have to store information based on what the use has selected

parent Lov


child Lov


if the user select parent lov i what to save

the selected object name in column obj_name of cal_obj table and the id of selected objname in obj_parent column


 
if the user select parent lov i what to save
he selected object name in column obj_name of cal_obj table and the id of selected objname in obj_child column

this is the table structure
create table CAL_OBJ
(
  obj_child   NUMBER(4),
  obj_name    VARCHAR2(100),
  obj_type    VARCHAR2(50),
  obj_title   VARCHAR2(50),
  description VARCHAR2(50),
  obj_parent  NUMBER(5)
);


 insert into cal_obj(obj_child,                       
                        obj_name,
                        obj_type)
                    values(1192,                          
                           'MainObject',
                           'Form');
                           
                           
                           
                           insert into cal_obj(obj_parent,
                                               obj_child,                       
                        obj_name,
                        obj_type)
                    values(1192, 
                            1193,                         
                           'SecondObject',
                           'Form');
                           
                               insert into cal_obj(obj_parent,
                                               obj_child,                       
                        obj_name,
                        obj_type)
                    values(1193, 
                            1194,                         
                           'ThirdObject',
                           'Form');

Open in new window

Untitledparent.png
Untitled.png
dis.png
toad.png
0
Comment
Question by:chalie001
  • 8
  • 6
14 Comments
 
LVL 20

Expert Comment

by:flow01
Comment Utility
Don't  create dynamic  lov:   create 2 distinct lov's based on 2 recordgroups and assign those lov's to the different items in  your form.
Else try   to explain  the behaviour you want to see  (if the users does  xxx then  the form should yyy)
0
 

Author Comment

by:chalie001
Comment Utility
ok the behaviour is

if User select parentLov
 - copyid = obj_child
-  parentid = obj_parent
*disable child lov

if user select childlov
  -copyid=objparent
 -childlovid = obj_child
*disable parent lov
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
How does the user select parentLov  and to what item  should the lov  be attached.
How does the user select childLov  and to what item  should the lov  be attached.
0
 

Author Comment

by:chalie001
Comment Utility
hi this is how i store lov value

i select object from the first screen than i navigate to next screen with the object id
-in the second screen i display the selected objectname and id of selected object

if user what to make parent of the display object the user select the parent lov
than  - copyidfrom selected object from previous screen = obj_child
-  parentid = obj_parent

this is when-button-pressed tigger button lov do when pressed parent lov
BEGIN
  go_item('cal_erd.DISPLAYNAME');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
END;

i also attach how the table is in database
firstscreen.png
child.png
Untitledparentlov.png
Untitledtable.png
attachlov.png
childlov.png
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
Comment Utility
Close: I will make some assumptions
.If  the user hits  button1 and the parent lov is shown.  It works by navigating to cal_erd.DISPLAYNAME and showing its lov.
and you want that if user hits button2 the child lov is shown ?
Then
1 You could use another item DISPLAYNAME2 at the same position as DISPLAYNAME and attach the childlov to that new
item. Navigate in the when button-pressed to DISPLAYNAME2 .
or
2 navigate to the same item but change the attached lov before navigation

this is when-button-pressed tigger button lov do when pressed parent lov
BEGIN
  set_item_property('cal_erd.DISPLAYNAME',LOV_NAME,'PARENT_LOV');
  go_item('cal_erd.DISPLAYNAME');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
END;

this is when-button-pressed tigger button lov do when pressed CHILD lov
BEGIN
  set_item_property('cal_erd.DISPLAYNAME',LOV_NAME,'CHILD_LOV');
  go_item('cal_erd.DISPLAYNAME');
  do_key('List_values');
  ??;
END;
0
 

Author Comment

by:chalie001
Comment Utility
.If  the user hits  button1 and the parent lov is shown.  It works by navigating to cal_erd.DISPLAYNAME and showing its lov.
and you want that if user hits button2 the child lov is shown ?=No


If  the user hits  button1 and the parent lov is shown
--copyid=objchild
parentlovid is already set to obj_parent
and disable the child lov textitem



If  the user hits  button1 and the childl lov is shown.
--copyid=objparentid
childlovid is already set to objchild
and disable the parent lov text itemlov showing how to attach parent/child lov.If  the user hits  button1 and the parent lov is shown.  It works by navigating to cal_erd.DISPLAYNAME and showing its lov.
and you want that if user hits button2 the child lov is shown ?=No


If  the user hits  button1 and the parent lov is shown
--copyid=objchild
parentlovid is already set to obj_parent
and disable the child lov textitem



If  the user hits  button1 and the childl lov is shown.
--copyid=objparentid
childlovid is already set to objchild
and disable the parent lov text item

showing lov how it must attch id
0
 

Author Comment

by:chalie001
Comment Utility
am geting this error
FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502  
 
hi am trying to set-item -property when lov button pressed but am geting this error

 
my lov button trigger is

FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502

i try to do this
BEGIN
--set_item_property('CAL_ERD.OBJ_CHILD',:CAL_ERD.COPYOBJID);

  go_item('cal_erd.DISPLAYNAME');
    SET_ITEM_INSTANCE_PROPERTY('CAL_ERD.OBJ_CHILD',:CAL_ERD.COPYOBJID,'CAL_ERD.OBJ_CHILD');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
END;

11.CAL_ERD.OBJ_CHILD=:CAL_ERD.COPYOBJID  BEGIN
  go_item('cal_erd.DISPLAYNAME');
  --set_item_property('CAL_ERD.OBJ_CHILD',:CAL_ERD.COPYOBJID);
  SET_ITEM_INSTANCE_PROPERTY('CAL_ERD.OBJ_CHILD',:CAL_ERD.COPYOBJID,'CAL_ERD.OBJ_CHILD');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
END;

when i user  --set_item_property('CAL_ERD.OBJ_CHILD',:CAL_ERD.COPYOBJID); it does not set my iten to the value i what

CAL_ERD.OBJ_CHILD=:CAL_ERD.COPYOBJID
am in oracle 11gR2
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 20

Expert Comment

by:flow01
Comment Utility
You can't change the attached lov an item-instance item:
don't use SET_ITEM_INSTANCE_PROPERTY  but SET_ITEM_PROBERTY
if there are troubles setting it back to its default value we solve that afterwards.

Use the SET_ITEM_PROPERTY before the 'GO_ITEM'  because changing the properties of a current_item does not work (always?).

You want to change the LOV of the item that is the current-item if you execute the do_key('List_values') :
so the go_item  and the set_item_property should refer to the same item.

You want to change the LOV_NAME : the identification of that property  is an number and LOV_NAME  is an oracle-forms constant with a value of that number. So use LOV_NAME (without quotes) as the second argument of the set_item_property.
If you want to check : add MESSAGE( 'The value of LOV_NAME =' || LOV_NAME, acknowledge);

The 3e argument is the name of the LOV  you want to assign:  it should be a literal  containing a valid LOV_NAME :
'PARENT_LOV'  and 'CHILD_LOV' are,  'CAL_ERD.OBJ_CHILD' not.

I assume you did change my code because i did not do what you wanted :  what problem did occur?
0
 

Author Comment

by:chalie001
Comment Utility
hi i whated this
BEGIN
      copy(:cal_erd.copyobjid,'cal_erd.obj_child');
  go_item('cal_erd.DISPLAYNAME');
  do_key('List_values');
  copy ('0', 'GLOBAL.save_mouse_record');
END;
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
Yes, but that does  not do what you want ?  Or does it?
0
 

Author Comment

by:chalie001
Comment Utility
as far i can see it does it copy the id to another text field when i click the lov button
0
 
LVL 20

Expert Comment

by:flow01
Comment Utility
You  are not easy to help.
Your answer suggests that your trigger does what you want, but you did not ask this question if it where. Or are you ready now?
I think you want to display an lov in both triggers , but that you wanted to fill different items  with the choise of the user depending on choosing the child or parent button. Is that correct ?
I gave a solution to do it,  you changed the code so it gives errors and then you say you want to keep  the code you originally had.  What help do you expect?
0
 

Author Comment

by:chalie001
Comment Utility
sorry for not to be clear with my question
0
 

Author Closing Comment

by:chalie001
Comment Utility
correct
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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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

762 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

11 Experts available now in Live!

Get 1:1 Help Now