Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

frm-10902: Invalid sql query

Posted on 2015-01-15
18
Medium Priority
?
688 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

926 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