PDA

Click to See Complete Forum and Search --> : passing parameter in FOR .. IN Loop

Shurik12
03-07-2003, 06:06 AM
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
03-07-2003, 06:40 AM
I think you should use Dynamic SQL in this scenario.

Pl correct me if i m wrong.

SS
Shurik12
03-07-2003, 06:43 AM
Hi,
I think it's an option, i just wonder if it is possible this
way.
nandu
03-07-2003, 07:25 AM
Looks like your problem is with concatenating the string.

Can you post the SQL in trouble?

Cheers
Nandu
Shurik12
03-07-2003, 07:36 AM
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
03-07-2003, 07:42 AM
Can you pls post the code after the WHERE part from your source?

- Nandu
Shurik12
03-07-2003, 08:17 AM
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
03-07-2003, 08:56 AM
Couldn't make out much. Sorry... :(

- Nandu
Srinivas_Sharma
03-07-2003, 09:45 AM
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
03-07-2003, 09:46 AM
Thanks for your help, guys.