Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4917
  • Last Modified:

Query on creating Hierarchical Tree in Oracle Form

Hi,

I am trying to create a H-Tree using Record Group in Oracle Form 6.But I am getting FRM-41072 (Cannot Create Group) and FRM-41076 (error populating group). I also saw the reasons for the form failures, but everything is fine according to me.Here is my Code:


PROCEDURE ADDHTREENEW IS

htree FTREE.NODE;
htree_id ITEM;
rg_id RECORDGROUP;
rg_name VARCHAR2(40);
poprg number;
 
BEGIN
   
    htree_id := FIND_ITEM('TREEDATABLK.HTREE');
   
    IF NOT ID_NULL (htree_id) THEN
           FTREE.DELETE_TREE_NODE('HTREE',htree);
    ELSE
       MESSAGE('ERROR CREATING ID OF TREE');    
       RAISE FORM_TRIGGER_FAILURE;
    END IF;        
   
   rg_id := FIND_GROUP('TREEGROUP');
 
  rg_id := CREATE_GROUP_FROM_QUERY(rg_name,'SELECT 1,LEVEL,lname,NULL,to_char(SSN) from employee connect by prior ssn = superssn start with ssn = 987654321',FORM_SCOPE,20);
                 
   poprg := POPULATE_GROUP(rg_id);
   FTREE.Set_Tree_Property(htree_id,FTREE.RECORD_GROUP,rg_id);
 
   htree := FTREE.ADD_TREE_NODE('TREEDATABLK.HTREE',htree,FTREE.PARENT_OFFSET,FTREE.LAST_CHILD,FTREE.COLLAPSED_NODE,'ORACLE','','ora');
   
 END IF;        
END;

 
In a WNF Trigger I am calling this procedure ADDHTREENEW.

Also one strange thing that I noticed- The Query passed to CREATE_GROUP_FROM_QUERY
(SELECT 1,LEVEL,lname,NULL,to_char(SSN) from employee connect by prior ssn = superssn start with ssn = 987654321) works absolutely fine in SQL PLUS.So, why it is not creating the group????

Also. I am adding one node at the last named ORACLE just to verify that tree is being actually created.
The node 'ORACLE' does appear.

I am using Forms [32 Bit] Version 6.0.8.11.3 (Production).

Thanks in advance.

0
njain78
Asked:
njain78
  • 4
  • 4
1 Solution
 
DrJekyllCommented:
Make sure the record group does not already exist.

IF ID_NULL( rg_id ) THEN  
  rg_id    := CREATE_GROUP_FROM_QUERY(rg_name,'SELECT 1,LEVEL,lname,NULL,to_char(SSN) from employee connect by prior ssn = superssn start with ssn = 987654321',FORM_SCOPE,20);
END IF;  
0
 
njain78Author Commented:
Hi,

 Actually i did check it but somehow missed in pasting it here.Truly speaking, this code was running sometime ago, and after that i went one step ahead and created WHEN-TREE-NODE-ACTIVATED trigger on a node to query employee record based on a node value.(in new EMPTEST form). I never got the result and after few tries, even the creation of tree didn't happen.I have faced this situation before also with other applications in forms.

I wish to know why does this happen???Also, kindly help me with the question assked. Thanks.
 The code for this trigger is as follows:

DECLARE

htree_id ITEM;
htree_blk BLOCK;
node_value VARCHAR2(2000);

BEGIN
     htree_id := FIND_ITEM('TREE');
 
IF NOT ID_NULL(htree_id) THEN
 
  node_value :=  
FTREE.GET_TREE_NODE_PROPERTYhtree_id,:SYSTEM.TRIGGER_NODE,FTREE.NODE_VALUE);

       SET_BLOCK_PROPERTY('F:\test\emptest.fmx',DEFAULT_WHERE,'EMPLOYEE.LNAME = ' || node_value);

       open_form('F:\test\emptest.fmx');
       GO_BLOCK('EMPLOYEE');
       EXECUTE_QUERY();
   
  ELSE
       
       RAISE FORM_TRIGGER_FAILURE;
     END IF;                  
             
END;








0
 
Helena Markováprogrammer-analystCommented:
I think that you would upgrade your Forms to higher version, we use Forms [32 Bit] Verzia 6.0.8.21.3 (Produkcia). I use tree with no problems there. It seems that it is a bug since you have written that "I have faced this situation before also with other applications in forms.".

Henka
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Helena Markováprogrammer-analystCommented:
I have forgotten to mention that if there are more than 1800 nodes in a tree you must upgrade your Forms builder.
0
 
njain78Author Commented:
Hi Henka,

My SQL query just returns me 3 rows.Here it is:

select 1, level, lname, NULL, to_char(ssn)  from employee
connect by prior ssn = superssn start with ssn = 987654321;

   1     LEVEL LNAME           N TO_CHAR(SSN)
---- --------- --------------- - ---------------------
   1         1 WALLACE           987654321
   1         2 ZELAYA            999887777
   1         2 JABBAR            987987987

As i said earlier,in my second comment, query was running fine.so I think form upgrade is not necessary.

I also tried to build application afresh but in vain.

0
 
Helena Markováprogrammer-analystCommented:
You can try this:

PROCEDURE ADDHTREENEW IS

htree FTREE.NODE;
htree_id ITEM;
rg_id RECORDGROUP;
poprg number;

BEGIN
   
   htree_id := FIND_ITEM('TREEDATABLK.HTREE');
   
   IF NOT ID_NULL (htree_id) THEN
          FTREE.DELETE_TREE_NODE('HTREE',htree);
   ELSE
      MESSAGE('ERROR CREATING ID OF TREE');    
      pause;
   END IF;        
   
  rg_id := FIND_GROUP('TREEGROUP');
  IF NOT Id_Null(rg_id) THEN
   DELETE_GROUP(rg_id);
  END IF;

 rg_id := CREATE_GROUP_FROM_QUERY('TREEGROUP','SELECT 1,LEVEL,lname,NULL,SSN from employee connect by prior ssn = superssn start with ssn = 987654321');
                 
  poprg := POPULATE_GROUP(rg_id);
  IF poprg=0 THEN
   FTREE.Set_Tree_Property(htree_id,FTREE.RECORD_GROUP,rg_id);

   htree := FTREE.ADD_TREE_NODE('TREEDATABLK.HTREE',htree,FTREE.PARENT_OFFSET,FTREE.LAST_CHILD,FTREE.COLLAPSED_NODE,'ORACLE','','ora');
ELSE
 message('error');
 pause;
END IF;
END;
0
 
njain78Author Commented:
Hi Henka,

      Sorry for the delay.This code worked but when i went ahead to use node selected trigger as mentioned above, NODE_VALUE is returning me 'NULL'. Hence no employee record is shown. Kindly help me.





 
0
 
Helena Markováprogrammer-analystCommented:
I don't understand: If you doubleclick on a node in a tree then the ftree.NODE_VALUE for SYSTEM.TRIGGER_NODE is NULL ?
0
 
njain78Author Commented:
Thans henka, It's now working perfectly fine.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now