chalie001
asked on
error in my pre-query
hi am having this error in my pre-query trigger ORA-00918: column ambiguously defined
SELECT CODE,DESCRIPTION,DESCRIPTI
the trigger code is
am in 11g
i think the error is beacause the table column has change its no longer code description am in 11g
SELECT CODE,DESCRIPTION,DESCRIPTI
the trigger code is
declare
cursor c is
select column_name, column_id from all_tab_columns where table_name=:control.reftable and column_id<=3 order by column_id;
r c%rowtype;
q varchar2(2000) := '(select ';
type arr is varray(3) of varchar2(50);
arrv arr := arr('CODE', 'DESCRIPTION', 'DESCRIPTION2');
begin
:control.column1:=null;
:control.column2:=null;
:control.column3:=null;
open c ;
loop
fetch c into r;
exit when c%notfound;
q := q || r.column_name||' '|| arrv(r.column_id)||',';
if r.column_id = 1 then
:control.column1:=r.column_name;
end if;
if r.column_id = 2 then
:control.column2:=r.column_name;
end if;
if r.column_id = 3 then
:control.column3:=r.column_name;
end if;
end loop;
if r.column_id=2 then
q := q || 'null' ||' '|| arrv(3)||',';
end if;
q := substr(q,1,length(q)-1) || ' from '||:control.reftable||' order by 1)';
set_block_property ('LIST',query_data_source_name,q);
if :control.column3 is null then
set_item_property ('LIST.DESCRIPTION2',VISIBLE,property_false);
set_item_property ('LIST.DESCRIPTION',width,385);
else
set_item_property ('LIST.DESCRIPTION2',visible,property_true);
set_item_property ('LIST.DESCRIPTION2',enabled,property_true);
set_item_property ('LIST.DESCRIPTION2',insert_allowed,property_true);
set_item_property ('LIST.DESCRIPTION2',update_allowed,property_true);
set_item_property ('LIST.DESCRIPTION2',navigable,property_true);
set_item_property ('LIST.DESCRIPTION',width,192);
end if;
close c;
end; am in 11g
i think the error is beacause the table column has change its no longer code description am in 11g
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so i must change this part
cursor c is
select column_name, column_id from all_tab_columns where table_name=:control.reftable and column_id<=3 order by column_id;
r c%rowtype; ASKER
now am geting SELECT CODE,DESCRIPTION,DESCRIPTI
ORA-00936: missing expression
i got this
ORA-00936: missing expression
i got this
select column_name, column_id from all_tab_columns where table_name= '<tabname>' and column_id<=3 order by column_id;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
am geting this
results.png
results.png
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this what i get
query2.jpg
query2.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NO POINTS please.
In case you don't see a problem in that result image, note that there is more than one value in the OWNER column, hence you get repeated values for the column names.
So your dynamic SQL returns multiple columns of the same name, and this then leads to the "ambiguous column" problem that started the question.
So: Make sure the query you use for the dynamic sql returns only a single row for each column heading
In case you don't see a problem in that result image, note that there is more than one value in the OWNER column, hence you get repeated values for the column names.
So your dynamic SQL returns multiple columns of the same name, and this then leads to the "ambiguous column" problem that started the question.
So: Make sure the query you use for the dynamic sql returns only a single row for each column heading
ASKER
ok i use USER_TAB_COLUMNS
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
https://drive.google.com/open?id=0B3dKl5pXBYMqSHgwU1M1YTJUNmc how can i still do this dynamically even if the table columns has change