Oracle 11g: Dynamic query exception
-
Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks, 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,
-
Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks, 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,
I could be wrong, but I don't think you can use bind variables in that manner. Typically they should contain a value that can be operated on. In your case the bind variable contains all the operators and values. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
I could be wrong, but I don't think you can use bind variables in that manner. Typically they should contain a value that can be operated on. In your case the bind variable contains all the operators and values. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
I'm relatively new to oracle... so I'll assume you're right. I modified the proc a bit so it doesn't use the bind variable and instead just concats the string together. Now I get a different exception... ORA-00909: invalid number of arguments ORA-06512: at SINC.EQUIPMENT_MGMT_PKG, line 116 ORA-06512: at line 1 where line 116 is again the whitespace line just before opening the cursor. I'm basing this off of this - see section "Sample Multiple-Row Query Using Native Dynamic SQL". Maybe i'm just going about this wrong.. ? 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:= ' itd.niin like ''%' || p_Niin || '%'''; end if; if p_Fsc is not null then v_where:= v_where || ' and itd.fsc like ''%' || p_Fsc || '%'''; end if; if p_Description is not null then v_where:= v_where || ' and lower(itd.description) like lower(''%' || p_Description || '%'')'; end if; if p_Idn is not null then v_where:= v_where || ' and lower(itd.idn) like lower(''%' || p_Idn || '%'')'; end if; if p_Tam is not null then v_where:= v_where || ' and lower(itd.tam) like lower(''%' || p_Tam || '%'')'; end if; if p_Cos is not null then v_where:= v_where || ' and lower(itd.cos) like lower(' || p_Cos || ')'; end if; if p_Scos is not null then v_where:= v_where || ' and lower(itd.scos) like lower(' || p_Scos || ')'; end if; if p_Ec is not null then v_where:= v_where || ' and lower(itd.ec) like lower(''%' || p_Ec || '%'')'; end if; if p_LocalTam is not null then v_where:= v_where || ' and lower(itd.local_tam) like lower(''%' || p_LocalTam || '%'')'; end if; if p_PageNum > -1 then v_where:= v_where || ' and rownum > ' || (p_PageNum * 100); v_where:= v_where || ' and rownum < ' || ((p_PageNum+1) * 100); end if; v_where:= ltrim(v_where, ' and'); v_where:= 'where ' || v_where; 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_i
-
I'm relatively new to oracle... so I'll assume you're right. I modified the proc a bit so it doesn't use the bind variable and instead just concats the string together. Now I get a different exception... ORA-00909: invalid number of arguments ORA-06512: at SINC.EQUIPMENT_MGMT_PKG, line 116 ORA-06512: at line 1 where line 116 is again the whitespace line just before opening the cursor. I'm basing this off of this - see section "Sample Multiple-Row Query Using Native Dynamic SQL". Maybe i'm just going about this wrong.. ? 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:= ' itd.niin like ''%' || p_Niin || '%'''; end if; if p_Fsc is not null then v_where:= v_where || ' and itd.fsc like ''%' || p_Fsc || '%'''; end if; if p_Description is not null then v_where:= v_where || ' and lower(itd.description) like lower(''%' || p_Description || '%'')'; end if; if p_Idn is not null then v_where:= v_where || ' and lower(itd.idn) like lower(''%' || p_Idn || '%'')'; end if; if p_Tam is not null then v_where:= v_where || ' and lower(itd.tam) like lower(''%' || p_Tam || '%'')'; end if; if p_Cos is not null then v_where:= v_where || ' and lower(itd.cos) like lower(' || p_Cos || ')'; end if; if p_Scos is not null then v_where:= v_where || ' and lower(itd.scos) like lower(' || p_Scos || ')'; end if; if p_Ec is not null then v_where:= v_where || ' and lower(itd.ec) like lower(''%' || p_Ec || '%'')'; end if; if p_LocalTam is not null then v_where:= v_where || ' and lower(itd.local_tam) like lower(''%' || p_LocalTam || '%'')'; end if; if p_PageNum > -1 then v_where:= v_where || ' and rownum > ' || (p_PageNum * 100); v_where:= v_where || ' and rownum < ' || ((p_PageNum+1) * 100); end if; v_where:= ltrim(v_where, ' and'); v_where:= 'where ' || v_where; 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_i
The only thing I can think of is making sure that the 'out' parameter you supply when you call the procedure has the correct number and type of fields to match the select list. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
The only thing I can think of is making sure that the 'out' parameter you supply when you call the procedure has the correct number and type of fields to match the select list. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Yeah... it's just a reference cursor, so it works like it is. It was working before I added all of the if's. lol. But i need to be able to filter this stuff depending on which fields are populated.
-
Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks, 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,
Post the code using code blocks. You can debug by removing the dynamic blocks until only the first one is left. If that works then add one dynamic block at a time. Also rather than trying to remove the last term add a first fixed term. Thus your constructed where would look something like... where 1=1 and p_Niin like '%x%' ...
-
Post the code using code blocks. You can debug by removing the dynamic blocks until only the first one is left. If that works then add one dynamic block at a time. Also rather than trying to remove the last term add a first fixed term. Thus your constructed where would look something like... where 1=1 and p_Niin like '%x%' ...
Thanks... I didn't even think about fixing the first base where clause string like that. Much easier. and yeah, I've just been moving through the if statements. I'm sure there's just a quote or something missing.