Solved

frm-10902: Invalid sql query

Posted on 2015-01-15
18
493 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now