Solved

Select multi data from tlist and store them in a variable

Posted on 2004-04-19
10
1,048 Views
Last Modified: 2007-12-19
hi,

I'd like to know how to ago about for the multi selection option for the TList component. (i.e) I want to select more than one data from the TList1 and add them to TList2 then store all elements which added in TList2 in variable to create a select statement.

many thanks
0
Comment
Question by:khaledsoliman2001
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10858543
You cannot select multiple elements of T-list item. You have to use some work around e.g. create a block with check boxes=TList1 and checked values pass to the TList2...
0
 

Author Comment

by:khaledsoliman2001
ID: 10858807
Dear Henka
my proplem is:
I have some tables in my DB, when I choose a table from a list the T-list1 will fill with the table coulmns, so I need to choose some of this coulmns which add in the T-list1 and create a select statement from the choosen T-list elements.
what is the Ideal method to do that?
sorry for poor English
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10858873
I think that you can achieve the same functionality:
block 1 - tables        master block
block 2 - columns    detail block. In this block you can add a check box (it is not based on a table column).
User can check some rows in the block 2 (= table columns) and you can LOOP through this block and create a desired select statement (e.g. in a When-Button-Pressed trigger of button 'Create statement').
0
 

Author Comment

by:khaledsoliman2001
ID: 10859115
Sorry , could u explain step by step
how can I add tables names in block and the coulmns in other block?
and is it possible to change the number of checkboxes debended on the number of table's coulmns because the coulmns in each tabel in not the same number?
0
 
LVL 6

Expert Comment

by:musdu
ID: 10859437
Or you can use a hierarchical tree to allow multi selection.

regards.
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 47

Expert Comment

by:schwertner
ID: 10860403
2 years ago Mr. M-Ali and I have answered how to construct hierarchical tree.
This is a very nice feature of Forms introduced beginning with Forms 6i.

Look in
http://oldlook.experts-exchange.com:8080/Databases/Oracle/Q_20312351.html

The record group for hierarchical tree should have 5 columns:
1) Initial State - specifies which nodes are expanded/collapsed. Values are 0, 1, -1.
2) Level of node tree depth
3) Displayed value on node
4) Icon name
5) Data -- value associated with the node.


Here are the steps you need to follow using emp table in Scott schema:

1) Create a block B1. Create a hierarchical tree item TREE1.

2) Create another block B2. Create a push-button PB1.

3) Create Record Group RG1 with the following query:
SELECT 1, LEVEL, ename, '', TO_CHAR(empno)
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT'

4) Create WHEN-BUTTON-PRESSED trigger on PB1:
DECLARE
 my_tree ITEM;
 my_num  NUMBER;

BEGIN
 my_tree := Find_Item('B1.TREE1');
 my_num  := POPULATE_GROUP('RG1');
 FTREE.SET_TREE_PROPERTY(my_tree, FTREE.RECORD_GROUP, 'RG1');
END;

5) Create layout and run the program. Click PB1 to populate the tree.


I use hierarchical trees in my Human Resources Info System. Before using them you have to know some "underground" facts. First at all the tree item ha to be the only item in the block. The second fact is very important - due a bug in Forms a tree CANNOT have more then 1800 elements (leaves, subtrees, etc.) So if you try to use huge trees (what is not a good solution) you will run into this bug.

I provide you with the body of the package I use to deal with the hierarchical tree. My hope is that you will fid it useful.

Good luck!

PROCEDURE POPULATE_TREE (p_tree_id ITEM) IS
     TYPE pl_table_group_names IS
        TABLE OF VARCHAR(120)
        INDEX BY BINARY_INTEGER;
  TYPE pl_table_group_id IS
        TABLE OF NUMBER(6)
        INDEX BY BINARY_INTEGER;
     htree             ITEM;
     work_gr_names     pl_table_group_names;
     work_gr_id        pl_table_group_id;
     vn_group          INTEGER;
     vn_group_id       INTEGER;
     vn_member_begin   INTEGER;
     vn_member_count   INTEGER;
     v_new_node_root   FTREE.NODE;
     v_new_node_group  FTREE.NODE;
     v_new_node_member FTREE.NODE;
     vc_root_label     VARCHAR2(80);
     
     CURSOR rila_groups IS
        SELECT id,group_name,group_director_name  FROM rila_group;
        vc_rila_group rila_groups%ROWTYPE;
       
      CURSOR rila_employees IS
        SELECT id, rila_id|| '        '|| given_name||' ' || second_name ||' '|| surname AS name
        FROM employee
         WHERE     service_status = :service_status
               AND employee.rila_group = vn_group_id
         ORDER BY rila_id;
     vc_rila_employee rila_employees%ROWTYPE;
     
BEGIN

   -- Register ALL Rila group - Group_Names and Id
  OPEN rila_groups;
  vn_group := 0;
  LOOP
         FETCH rila_groups INTO vc_rila_group;
         EXIT WHEN  rila_groups%NOTFOUND;
        vn_group := vn_group + 1;
        work_gr_names(vn_group) := vc_rila_group.group_name;
        work_gr_id(vn_group) := vc_rila_group.id;
  END LOOP;
  CLOSE rila_groups;
 
   
   -- Delete the existing tree (if any)
   -- Ftree.ROOT_NODE is an undocumented constant pointing the Root node of the tree
   Ftree.Delete_Tree_Node(p_tree_id,  Ftree.ROOT_NODE);
   
   -- Make the Root Node "Rila Solutions AD"
     
   vc_root_label :=  'RILA SOLUTIONS AD';
   IF    :service_status = '1' THEN  vc_root_label := vc_root_label || ' - list of candidates';
   ELSIF :service_status = '2' THEN  vc_root_label := vc_root_label || ' - list of employees';
   ELSIF :service_status = '3' THEN  vc_root_label := vc_root_label || ' - list of released';
   END IF;

                 v_new_node_root := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       Ftree.ROOT_NODE,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       vc_root_label,
                                       NULL,
                                       NULL);
             
   -- Add nodes to the Hierarchical Tree - Subgroup Nodes  
  FOR i IN  1..vn_group LOOP
             -- Add parent node - the name of the Rila Group
             /*
             v_new_node_group := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       Ftree.ROOT_NODE,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       work_gr_names(i),
                                       NULL,
                                       work_gr_names(i));                                  
                                     
         */
                       v_new_node_group := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       v_new_node_root,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       work_gr_names(i),
                                       NULL,
                                       work_gr_names(i));
      -- Add all sibling nodes - Employees Member of the Subgroup                                
       vn_group_id := work_gr_id(i);
      FOR k IN rila_employees LOOP
          v_new_node_member := Ftree.Add_Tree_Node(
                                       p_tree_id,
                                       v_new_node_group,
                                       Ftree.PARENT_OFFSET,
                                       Ftree.LAST_CHILD,
                                       Ftree.EXPANDED_NODE,
                                       k.name,
                                       NULL,
                                       TO_CHAR(k.id));
     
       END LOOP;


  END LOOP;
 
   -- Expand the ROOT node of the hierarchical tree  
   FTREE.SET_TREE_NODE_PROPERTY (p_tree_id, v_new_node_root, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
 
   --Ftree.Set_Tree_Node_Property(htree, node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
  vn_member_begin := vn_group + 1;
       
 EXCEPTION
    WHEN OTHERS THEN
       CLOSE rila_groups;
      CLOSE rila_employees;
END POPULATE_TREE;  -- End Procedure POPULATE_TREE
 


PROCEDURE Open_Employee_Form (p_tree_id ITEM) IS  

  node_label    VARCHAR2(100);
  node_value    VARCHAR2(10);
  node_depth    VARCHAR2(1);  
      list_id   ParamList;
      list_name VARCHAR2(10) := 'param_list';
BEGIN
   
   -- Find the DEPTH of the node clicked on.
  node_depth := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_DEPTH);
 
   --MESSAGE(node_depth || ' ' || node_value ||' '|| node_label);
  --PAUSE;

     
    -- only nodes with depth 3 correspond to employees
    -- depth 3 corresponds to groups
    -- depth 1 corresponds to RILA SOLUTION
    IF  node_depth  = '3' THEN
         
           -- Find the VALUE of the node clicked on.
       node_value := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_VALUE);
       -- Find the LABEL of the node clicked on.
       node_label := Ftree.Get_Tree_Node_Property(p_tree_id, :SYSTEM.TRIGGER_NODE, Ftree.NODE_LABEL);
       /*
           ** Create a parameter list named "param_list"
            */
       list_id := Get_Parameter_List(list_name);
        IF NOT Id_Null(list_id) THEN
                  Destroy_Parameter_List(list_id);
           END IF;
       
           list_id := Create_Parameter_List(list_name);
         
           /*
            ** Add parameter to the list to pass values for each
            ** user-defined parameters defined in the target form; for each
            ** parameter, specify its key, type (text or data), and value
            */
              Add_Parameter(list_id, 'PROCESS_TYPE',TEXT_PARAMETER,'EDIT');
             Add_Parameter(list_id, 'EMPLOYEE#',TEXT_PARAMETER,TO_NUMBER(node_value));
             Add_Parameter(list_id, 'EMPLOYEE_SERVICE_STATUS',TEXT_PARAMETER,:SERVICE_STATUS);
             Add_Parameter(list_id, 'WEB_or_LAN',TEXT_PARAMETER,:DATA.WEB_or_LAN);
 

              -- now open the form using the created parameter list
             OPEN_FORM('staff_new',ACTIVATE,SESSION,list_id);
    END IF;

END Open_Employee_Form; -- End Procedure Open_Employee_Form


 
PROCEDURE Colapse_All_Nodes (p_tree_id ITEM) IS
   node ftree.node;
  state varchar2(30);
  node_label varchar2(80);

BEGIN

  -- Get the root node
  node := Ftree.Find_Tree_Node(p_tree_id,'Management',Ftree.FIND_NEXT,Ftree.NODE_LABEL, Ftree.ROOT_NODE);

  --node_label := Ftree.Get_Tree_Node_Property(p_tree, node, Ftree.NODE_LABEL);
  --MESSAGE(node_label);
  --PAUSE;      

   -- collapse the node if it is expanded
  WHILE NOT Ftree.ID_NULL(node) LOOP
     state := Ftree.Get_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE);
     IF state = Ftree.EXPANDED_NODE THEN
         Ftree.Set_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE, Ftree.COLLAPSED_NODE);
      END IF;

     node := Ftree.Find_Tree_Node(p_tree_id, '', ftree.find_NEXT,Ftree.NODE_LABEL,'', node);

  END LOOP;
END colapse_all_nodes;
 
   

PROCEDURE Expand_All_Nodes (p_tree_id ITEM) IS
  node ftree.node;
  state varchar2(30);
  node_label varchar2(80);

BEGIN

-- Get the root node
  node := Ftree.Find_Tree_Node(p_tree_id,'RILA SOLUTIONS',Ftree.FIND_NEXT,Ftree.NODE_LABEL, Ftree.ROOT_NODE);

  --node_label := Ftree.Get_Tree_Node_Property(htree, node, Ftree.NODE_LABEL);
  --MESSAGE(node_label);
  --PAUSE;      

   -- expand the node if it is collapsed
  WHILE NOT Ftree.ID_NULL(node) LOOP
     state := Ftree.Get_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE);
     IF state = Ftree.COLLAPSED_NODE THEN
        Ftree.Set_Tree_Node_Property(p_tree_id, node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);
     END IF;
     node := Ftree.Find_Tree_Node(p_tree_id, '', ftree.find_NEXT,Ftree.NODE_LABEL,'', node);
  END LOOP;
END Expand_All_Nodes;
0
 
LVL 6

Expert Comment

by:musdu
ID: 10860619
Hi,

above document is very good, but you may need a simple one to start and understand how it works;

-Create a new form
-Create a hierarchical tree on your block (name it as TABLETREE)
-Create a when-new-form-instance trigger
-Copy/Paste below code and run your form. This code will populate all tables and their columns to tree item.
-Set "MULTI SELECTION" property to "YES" to allow users to select multiple items

regards

DECLARE

   htree   ITEM;
   new_node FTREE.NODE;
   
BEGIN


  -- Find the tree itself.
  htree := Find_Item('tabletree');

  FOR vTable IN (SELECT Table_Name FROM USER_TABLES) LOOP

     new_node := Ftree.Add_Tree_Node(
        htree,
        Ftree.ROOT_NODE,
        Ftree.PARENT_OFFSET,
        Ftree.LAST_CHILD,
        Ftree.EXPANDED_NODE,
        vTable.Table_Name,
        NULL,
        'root');

     -- Add the new node at the top level and children.
     Ftree.Add_Tree_Data(
        htree,
        new_node,
        Ftree.PARENT_OFFSET,
        Ftree.LAST_CHILD,
        Ftree.QUERY_TEXT,
        'SELECT 0, 1, Column_Name, NULL, Column_Name FROM User_Tab_Columns WHERE Table_Name = '''||vTable.Table_Name||'''');

  END LOOP;
 
END;
0
 

Author Comment

by:khaledsoliman2001
ID: 10860740
Thanks All;
But how can I create a select statement from the multiselected coulmns ans tables in the tree?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10865986
You can LOOP through selected tree items - see example "GET_TREE_SELECTION" topic in the on-line help.
0
 
LVL 6

Accepted Solution

by:
musdu earned 500 total points
ID: 10866319
Goodmorning,

if you created the form I mentioned above;

1) Create a new block (because tree items must be single item on a block)
2) Create a button on this new block
3) Copy below code to when-button-pressed-trigger.
4) Run form. Select several columns of a table in tree
5) Press the button
6) It'll create a select statement using selected column names and display it as message

regards

----------------

DECLARE
   htree         ITEM;
   num_selected  NUMBER;
   current_node  FTREE.NODE;
   parent_node  FTREE.NODE;
   vQuery VARCHAR2(500) := 'SELECT ';
   vColumnName            VARCHAR2(50);
   vTableName            VARCHAR2(50);
BEGIN
   -- Find the tree itself.
   htree := Find_Item('tabletree');

   -- Find the number of nodes marked as selected.
   num_selected := Ftree.Get_Tree_Property(htree, Ftree.SELECTION_COUNT);

   FOR j IN 1..num_selected LOOP
      current_node := Ftree.Get_Tree_Selection(htree, j);
      vColumnName := Ftree.Get_Tree_Node_Property('tabletree', current_node, Ftree.NODE_VALUE);
      vQuery := vQuery || vColumnName;
      EXIT WHEN j=num_selected;
      vQuery := vQuery || ',';
   END LOOP;
   
   parent_node := ftree.get_tree_node_parent('tabletree', current_node);
   vTableName := Ftree.Get_Tree_Node_Property('tabletree', parent_node, Ftree.NODE_LABEL);
   vQuery := vQuery ||' FROM '||vTableName;
   message(vquery);
END;
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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 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

23 Experts available now in Live!

Get 1:1 Help Now