Forum OpenACS Q&A: CONNECT BY clause required in this query block

After running openacs on 10G i noticed that


fullquery name="relation_types_valid_to_group_multirow.select_sub_rel_types"
      
        select 
            pretty_name, object_type, level, indent,
            case when valid_types.rel_type = null then 0 else 1 end as valid_p
        from 
            (select
                t.pretty_name, t.object_type, level,
                replace(lpad(' ', (level - 1) * 4), 
                        ' ', ' ') as indent,
                rownum as tree_rownum
             from 
                acs_object_types t
             connect by 
                prior t.object_type = t.supertype
             start with 
                t.object_type = :start_with ) types,
            (select 
                rel_type 
             from 
                rc_valid_rel_types
             where 
                group_id = :group_id ) valid_types
        where 
            types.object_type = valid_types.rel_type(+)
        order by tree_rownum
    


Give me always the following error:


ORA-01788: CONNECT BY clause required in this query block

SQL: 
      
	select 
	    pretty_name, object_type, level, indent,
	    case when valid_types.rel_type = null then 0 else 1 end as valid_p
	from 
	    (select
	        t.pretty_name, t.object_type, level,
	        replace(lpad(' ', (level - 1) * 4), 
	                ' ', ' ') as indent,
	        rownum as tree_rownum
	     from  !>>>!	        acs_object_types t
	     connect by 
	        prior t.object_type = t.supertype
	     start with 
	        t.object_type = :start_with ) types,

I face this when i try to add a member to a group
Any Ideas?

Collapse
Posted by Don Baccus on
This query works in 8i and 9i so my first idea is that 10G is broken.  It's asking for a CONNECT BY clause but itt *has* a CONNECT BY clause in that subselect.

Can you run Oracle 9i rather than 10G?  If so, try that.

If not ... do you have Oracle support?  If so, submit a bug report to them and tell us what they tell you.

Collapse
Posted by Barry Books on
I'm planning on bringing up 10g this week. Is this the only problem you had?

My guess is the error message has nothing to do with the problem. I would rename the level column in the connect by query to tree_level and change the outer select to tree_level. If that fixes it I'd say it's a 10g bug

Collapse
Posted by Samer Abukhait on
That does magically resolve the problem and it is nothing but a 10G bug.

So far, I had no problem else

Collapse
Posted by Samer Abukhait on
I've just remembered that:

In packages/acs-bootstrap-installer/tcl/20-db-bootstrap-procs.tcl
And packages/acs-tcl/tcl/00-database-procs.tcl

Some "Oracle8" is found, I’ve had to change these occurrences to "Oracle" for the OpenACS to work over a 10G client.