passing parameter in FOR .. IN Loop

[Shurik12]

Hi,

The situation is as follows.

CREATE OR REPLACE PACKAGE BODY
AS
PROCEDURE ChartGenericHistory
( p_SQL IN Varchar2
, ChartOutput OUT ReturnCursor)
)
IS

.....
.....

BEGIN


FOR x IN (SELECT DISTINCT coname

FROM

table1
,table2
... etc

WHERE

table1.field1=table2.field3
.... etc.

----> here I need to add p_SQL-parameter
----> which is just a variable part of
the 'where' clause
)


Failed to concantenate, get PLS-00103
error saying something about the symbols which are expected in place.


How could I go about it?

Someting in the lines with?:
(
...
WHERE
table1.field1=table2.field3
AND ...
and '||p_SQL||'
)


Thanks a lot for your help.


Shurik12.

[Srinivas_Sharma]

I think you should use Dynamic SQL in this scenario.

Pl correct me if i m wrong.

SS

[Shurik12]

Hi,
I think it's an option, i just wonder if it is possible this
way.

[nandu]

Looks like your problem is with concatenating the string.

Can you post the SQL in trouble?

Cheers
Nandu

[Shurik12]

Hi,

Basically it's about concantenating a varchar2 parameter in the WHERE
part of the statement

(
...
WHERE
table1.field1=table2.field3
AND
...
...
AND '||p_SQL||'
)


Is it enough for you to see the problem?
(The comlete code is on the develoment machine which
is separated from the outer world)

If you need more details, I'll retype the stuff?
Justr let me know.

Thakns in advance.


Shurik.

[nandu]

Can you pls post the code after the WHERE part from your source?

- Nandu

[Shurik12]

FOR x IN (SELECT DISTINCT coname
FROM
table1
,table2
... etc
WHERE
table1.field1=table2.field3
.... etc.
??? AND ||p_SQL|| ???
)

LOOP

l_query= l_query ||
',' || 'max(DECODE (coname,'''||x.coname||''',cds.midpx,null"'||x.coname|| '"';

END LOOP;

l_query:=rtrim(l_query, ',')|| l_query_dist||p_SQL|| ' GROUP BY cds.storedate';


OPEN ChartOutput FOR l_query;

END;
END;
/

Don't know if it makes much sense withoiut the whole code. But still.
The whole thing works OK if in FOR...IN I don't use any parameters (say select fom a table)


Thanks in advance,

Shurik12.

[nandu]

Couldn't make out much. Sorry... :(

- Nandu

[Srinivas_Sharma]

As far as i know you cant use it like that.

you have to go for dynamic sql only.

Even i had a problem like that and i went for dynamic sql.

SS

[Shurik12]

Thanks for your help, guys.