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
but am geting the error above am in form 11gR2 AND MY DATABASE is 11gR2 my query run well in my sql developerASKER
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;
What are column types of the view ?
ASKER
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_syste m from llv v
as the source of record group, isn't it ?
select v.obj_name , v.obj_childs, v.obj_parents,v.Obj_type ,v.description,d.sub_syste
as the source of record group, isn't it ?
ASKER
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
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
ASKER
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_pa rents,obj_ type,descr iption,sub _system
from llv;
frm-12007:cannot create the record group (unsupported datatype)
the lov sql is select obj_name,obj_childs,obj_pa
from llv;
Yes, CLOB are not supported. You cannot use LOV in this case.
ASKER
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
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 ?
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 ?
ASKER
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_syste m
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
ora-01489: result of string concatenation is too long
select
c.obj_name , obj_childs, obj_parents,d.Obj_type ,d.description,d.sub_syste
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.
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.
ASKER
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.
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.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
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.