?
Solved

Query on creating Hierarchical Tree in Oracle Form

Posted on 2003-03-07
9
Medium Priority
?
4,714 Views
Last Modified: 2007-12-19
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
Comment
Question by:njain78
[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
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:DrJekyll
ID: 8089266
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
 

Author Comment

by:njain78
ID: 8093250
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8101654
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 22

Expert Comment

by:Helena Marková
ID: 8101847
I have forgotten to mention that if there are more than 1800 nodes in a tree you must upgrade your Forms builder.
0
 

Author Comment

by:njain78
ID: 8102030
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
 
LVL 22

Accepted Solution

by:
Helena Marková earned 60 total points
ID: 8102312
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
 

Author Comment

by:njain78
ID: 8157005
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8165363
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
 

Author Comment

by:njain78
ID: 8171584
Thans henka, It's now working perfectly fine.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 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