Home » SQL & PL/SQL » SQL & PL/SQL » Problem on a simple Where Clause !!! (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
Problem on a simple Where Clause !!! [message #325936] Mon, 09 June 2008 11:13 Go to next message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Hi everybody

I'm trying to build (dynamically) the where clause for a cursor on a Store Procedure.
Basically I need to build the list to use on a IN clause.

Inside the Store Procedure I already have build the list, so on a varchar2(2000) variable I have something like this i.e.

variable v_rubric:

'400402','400470','B102' (checked with a dbms_output msg)

and I like to use that variable as a input parameter of a cursor call, and on that cursor to used on the where clause, something like:

OPEN c3 (c1_rec.id_empregad, v_rubric, v_sociedad);

for this cursor

CURSOR c3 (vc_empregad VARCHAR2, vc_rubrica_nav VARCHAR2, vc_sociedad VARCHAR2) IS
SELECT id_empregad, rubrica_m4, tipo_operacao, abs(sum(valor)) total
FROM rhv_rdt
WHERE id_empregad = vc_empregad
AND dt_imp = '52008'
AND rubrica_m4 in (vc_rubrica_nav)
AND id_sociedad = vc_sociedad
GROUP BY id_empregad, rubrica_m4, tipo_operacao;

The problem is that this cursor always give me no data found, it's not suppose since there's records on table to match the criteria, and if I make the select outside the Procedure everything works great.

I'm pretty sure the problem it's on the variable (better in the way I'm using the variable and probably building the list)
any help will be appreciate.


many thanks for a quick reply,
RHorta
Re: Problem on a simple Where Clause !!! [message #325938 is a reply to message #325936] Mon, 09 June 2008 11:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for varying in list in google/ in this forum. You will find an answer to your question.

Regards

Raj
Re: Problem on a simple Where Clause !!! [message #325939 is a reply to message #325936] Mon, 09 June 2008 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varying elements in IN list

Regards
Michel
Re: Problem on a simple Where Clause !!! [message #325954 is a reply to message #325936] Mon, 09 June 2008 12:15 Go to previous message
hortar
Messages: 8
Registered: June 2008
Location: Portugal
Junior Member
Thank you very much for the quick reply
Previous Topic: Ideal methodology to use to import data from a reporting tool like web trends into an oracle DB
Next Topic: regular expression to evaluate day of week field
Goto Forum:
  


Current Time: Fri Dec 09 05:48:15 CST 2016

Total time taken to generate the page: 0.10466 seconds