asked on

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.

DrJekyll

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;

njain78

ASKER

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;

Helena Marková

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

Helena Marková

I have forgotten to mention that if there are more than 1800 nodes in a tree you must upgrade your Forms builder.

njain78

ASKER

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.

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

njain78

ASKER

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.

Helena Marková

I don't understand: If you doubleclick on a node in a tree then the ftree.NODE_VALUE for SYSTEM.TRIGGER_NODE is NULL ?

njain78

ASKER

Thans henka, It's now working perfectly fine.