Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

frm-10902: Invalid sql query

Posted on 2015-01-15
18
Medium Priority
?
637 Views
Last Modified: 2015-02-06
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
this is how am dreating lov
0
Comment
Question by:chalie001
[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
  • 9
  • 9
18 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40552931
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.
0
 

Author Comment

by:chalie001
ID: 40555951
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

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40557106
What are column types of the view ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:chalie001
ID: 40557259
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
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40557378
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 ?
0
 

Author Comment

by:chalie001
ID: 40557410
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
0
 

Author Comment

by:chalie001
ID: 40557434
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;
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40557439
Yes, CLOB are not supported. You cannot use LOV in this case.
0
 

Author Comment

by:chalie001
ID: 40557451
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
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40557478
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 ?
0
 

Author Comment

by:chalie001
ID: 40557599
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
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40557628
Sorry, I cannot help. I don't understand why you need those columns obj_parents and obj_childs.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40561519
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.
0
 

Author Comment

by:chalie001
ID: 40561807
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
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40561859
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.
0
 

Author Comment

by:chalie001
ID: 40563777
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
adding anothe block
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 2000 total points
ID: 40563848
A search LLV1.SEARCH_ITEM  item ought to be on CANVAS123.
LLV1.BT_SELECT - here you also HIDE_VIEW('CANVAS123') and :FOCUS.xxx := :CAL_OBJ.xxx ... and at the end GO_BLOCK('FOCUS')

LLV1.BT_FIND - here you ought to modify where clause e.g. something like this
DECLARE
s_where VARCHAR2(4000);
BEGIN
s_where := LLV1.SEARCH_ITEM;
   SET_BLOCK_PROPERTY('CAL_OBJ',DEFAULT_WHERE,s_where);
EXECUTE_QUERY;
END;

LLV1.BT_CANCEL - HIDE_VIEW('CANVAS123') and GO_BLOCK('FOCUS');

This is an inspiration. As I see what you have done so far, then you can also do it without LLV1.SEARCH_ITEM and simply set query_allowed property to YES on items on which you want to perform query on block 'CAL_OBJ'.
Then in LLV1.BT_FIND you will use enter_query and execute_query buil-ins - or remove this button and use toolbar button for entering and executing query.
0
 

Author Closing Comment

by:chalie001
ID: 40594345
thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

671 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