Oracle 11g: Dynamic query problem

[sstainba]

Hey guys... I need to write a dynamic query for a table and I think I've got the right idea based on examples from the oracle docs. But I'm getting an exception when I try to execute this from my service. From the exception, it looks like one of the operators is incorrect, but I don't see a problem with them. Anyone care to take a look?

Exception:

ORA-00920: invalid relational operator
ORA-06512: at "SINC.EQUIPMENT_MGMT_PKG", line 89
ORA-06512: at line 1

The second error is pointing at the whitespace after the line "v_where:= ltrim(v_where, ' and');"

Procedure:

procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= concat(' p_Niin like ''%', concat(p_Niin, '%'''));
end if;
if p_Fsc is not null then
v_where:= concat(concat(v_where, ' and itd.fsc like ''%'), concat(p_Fsc, '%'''));
end if;
if p_Description is not null then
v_where:= concat(concat(v_where, ' and lower(itd.description) like lower(''%'), concat(p_Description, '%'')'));
end if;
if p_Idn is not null then
v_where:= concat(concat(v_where, ' and lower(itd.idn) like lower(''%'), concat(p_Idn, '%'')'));
end if;
if p_Tam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.tam) like lower(''%'), concat(p_Tam, '%'')'));
end if;
if p_Cos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.cos) like lower('), concat(p_Cos, ')'));
end if;
if p_Scos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.scos) like lower('), concat(p_Scos, ')'));
end if;
if p_Ec is not null then
v_where:= concat(concat(v_where, ' and lower(itd.ec) like lower(''%'), concat(p_Ec, '%'')'));
end if;
if p_LocalTam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.local_tam) like lower(''%'), concat(p_LocalTam, '%'')'));
end if;
if p_PageNum > -1 then
v_where:= concat(concat(v_where, ' and rownum > '), p_PageNum * 100);
v_where:= concat(concat(v_where, ' and rownum < '), (p_PageNum+1) * 100);
end if;

v_where:= ltrim(v_where, ' and');

open c_items for
'select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd
where :whereClause' using v_where;

END FetchItemTechData_PRC;

[jcaccia]

Shouldn't p_Niin in the quoted string inside the first IF be itd.niin instead?

if p_Niin is not null then
    v_where:= concat(' p_Niin like ''%', concat(p_Niin, '%'''));
end if;

if p_Niin is not null then
    v_where:= concat(' itd.niin like ''%', concat(p_Niin, '%'''));
end if;

[datakeyword]

hi,
It seemed some hard. But let make it easy to solve.....by esproc.
check this url:http://www.esproc.com/library/model_...rform-sql.html