Link to home
Start Free TrialLog in
Avatar of chalie001
chalie001

asked on

frm-10902: Invalid sql query

hi am having the following  sql when creating my lov in my form
select c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_system
from
(select  obj_name,RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_child || ',') order by obj_child), '/x/text()').getclobval(), ',')  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  
   and   ec.obj_child  = oc.obj_name(+)
 )
 group by obj_name
 ) c
,
(select obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_parent || ',') order by obj_parent), '/x/text()').getclobval(), ',')  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  
   and   ep.obj_parent = op.obj_name(+)
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name

Open in new window

but am geting the error above am in form 11gR2 AND MY DATABASE is 11gR2  my query run well in my sql developer
User generated image
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

CLOB is not supported in record group. This is from the on-line help:
A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE, provided that the total number of columns does not exceed 64K.

Maybe you can try to create a view and instead of CLOB use there LONG column and use this view in your record group.

You can also try to create a block (with CLOB column) and use it like a LOV.
Avatar of chalie001
chalie001

ASKER

hi i create this view but still geting the error when i  try to create lov using a view
create or replace view llv as
  select c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_system
from
(select  obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_child || ',') order by obj_child), '/x/text()').getclobval(), ',')  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  
   and   ec.obj_child  = oc.obj_name(+)
 )
 group by obj_name
 ) c
,
(select obj_name, RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x",obj_parent || ',') order by obj_parent), '/x/text()').getclobval(), ',')  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  
   and   ep.obj_parent = op.obj_name(+)
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name;

Open in new window

What are column types of the view ?
the column type is varchar i created the view as the query which other type you asking check the query above thats how i create the view how do i check the column types
Is it the same error (frm-10902) when using your view
select v.obj_name , v.obj_childs, v.obj_parents,v.Obj_type ,v.description,d.sub_system from llv v
as the source of record group, isn't it ?
ok will check my type is

SQL> desc llv
Name        Type          Nullable Default Comments
----------- ------------- -------- ------- --------
OBJ_NAME    VARCHAR2(100)                          
OBJ_CHILDS  CLOB          Y                        
OBJ_PARENTS CLOB          Y                        
OBJ_TYPE    VARCHAR2(50)  Y                        
DESCRIPTION VARCHAR2(500) Y                        
SUB_SYSTEM  VARCHAR2(100) Y                        
 
SQL> alter view llv modify obj_childs long;
 
alter view llv modify obj_childs long
 
ORA-00922: missing or invalid option
this is the error am geting
frm-12007:cannot create the record group (unsupported datatype)

the lov sql is select obj_name,obj_childs,obj_parents,obj_type,description,sub_system
from llv;
Yes, CLOB are not supported. You cannot use LOV in this case.
so what will be the solution for this

how can i do this
Maybe you can try to create a view and instead of CLOB use there LONG column and use this view in your record group.

You can also try to create a block (with CLOB column) and use it like a LOV
I would try to create a block based on your view llv and use it instead of LOV.
The block will be in its own window in a form. There will be 3 buttons - Select, Find and Cancel.
The block will be displayed by pressing a button.

But it is a lot of work.

Are those CLOB columns really needed in LOV ?
yes i re-wrote the query it was like this but giving this error
ora-01489: result of string concatenation is too long

select
c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_system
from
(select  obj_name, LISTAGG(obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  obj_childs
 from
 (select distinct c.obj_name,
          oc.obj_name obj_child
   from cal_obj c,
   cal_erd_link ec,
   cal_obj oc
   where ec.obj_parent(+) = c.obj_name  
   and   ec.obj_child  = oc.obj_name(+)
 )
 group by obj_name
 ) c
,
(select obj_name, LISTAGG(obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  obj_parents
 from
 (select distinct c.obj_name,
         op.obj_name obj_parent
   from cal_obj c,
   cal_erd_link ep ,
   cal_obj op
   where ep.obj_child(+)  = c.obj_name  
   and   ep.obj_parent = op.obj_name(+)
 )
 group by obj_name
 ) p
, cal_obj d
where c.obj_name = p.obj_name
and   d.obj_name = c.obj_name
Sorry, I cannot help. I don't understand why you need those columns obj_parents and obj_childs.
If you want to use CLOB column then LOV cannot be used.
You have to create a block (as it is mentioned in ID: 40557478). Block can be based also on stored procedure, where e.g. first 4000 characters of CLOB column will be return as VARCHAR.
hi i did create a block using the view  and the data type come as long how can i use that block as lov it is in its own window
In a brief:
1. create a button and use there GO_BLOCK('YOUR_BLOCK');, SHOW_VIEW ('CA_YOUR_BLOCK') if needed ...
2. create a block 'YOUR_BLOCK' - based on your view- on new canvas 'CA_YOUR_BLOCK'  and new window  'W_YOUR_BLOCK'  - in your form.
There will be 3 buttons - Select (BT_SELECT), Find (BT_FIND) and Cancel (BT_CANCEL)- on canvas 'CA_YOUR_BLOCK'. These buttons can be in  'YOUR_BLOCK'  or in a new control block  'BT_YOUR_BLOCK' (I prefer control block).
There will be an item for searching input - SEARCH_ITEM in 'BT_YOUR_BLOCK'.

If an user click on Find (BT_FIND) button -> execute_query on block 'YOUR_BLOCK' with WHERE clause depending on SEARCH_ITEM. So you ought to change here WHERE clause.
Select (BT_SELECT) - it closes the window 'W_YOUR_BLOCK' and item(s) from selected row can be stored to parameter(s) or directly to desired block.
Cancel (BT_CANCEL) -  it closes the window 'W_YOUR_BLOCK' without stored values.
hi this is what i have done so far where is the search_item how do i create one what must i write in the other two button when-button-pressed trigger
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks