SQL & PL/SQL

Dear All,

Please help on below query :

xml_emma(:P37_LIB1,'',:P37_C1,:P37_DATE_DEBUT,:P37_DATE_FIN,:P37_C1,:P37_ECHELLE, :P37_TRUNC)

create or replace function xml_emma (name in varchar2, y_axis in varchar2, v_clef_var number, date_debut varchar2, date_fin varchar2, station in varchar2, echelle number, trunc varchar2)
return clob
is stmt varchar2(32767);
f_station varchar2(40);
result CLOB;
BEGIN
stmt := 'SELECT xmlelement("series",
xmlattributes(:1 AS "name", :2 as "y_axis") ,
xmlagg( xmlelement("point" ,
xmlattributes(TO_CHAR(name) AS "name", TRANSLATE(data, '','' , ''.'') AS "y") ) ) ).getClobVal() FROM
(WITH S1 AS
(SELECT VALID_DATE,
ROUND(AVG(VALID_VALUE),2) DATA
FROM SAFEGE.MSR_'||station||'@EMMASAFE.NTSIVOA
WHERE VALID_DATE BETWEEN TO_DATE(:4||''000000'', ''DD/MM/YYYYHH24MISS'') AND TO_DATE(:5||''235959'', ''DD/MM/YYYYHH24MISS'')
GROUP BY VALID_DATE
)
SELECT
TO_CHAR(n, ''DD/MM/YY HH24:MI'') NAME,
NVL(ROUND(AVG(DATA),2), 0) DATA
FROM
(SELECT to_date(:4,''DD/MM/YYYY'') + (level-1)/ TO_NUMBER(:7) n
FROM dual
CONNECT BY level <=
(SELECT (to_date(:5,''DD/MM/YYYY'')+ 1 - to_date(:4,''DD/MM/YYYY'')) * TO_NUMBER(:7)
FROM dual
)
) days
LEFT JOIN s1
ON days.n = TRUNC(VALID_DATE, :8)
GROUP BY n
ORDER BY n)';
if v_clef_var is not null then
execute immediate stmt INTO result using name, y_axis, v_clef_var, date_debut, date_fin, date_debut, echelle, date_fin, date_debut, echelle, trunc ;
end if;
return result ;
END;

I am getting below error when running the above query

ORA-01006: bind variable does not exist

Edited by: Christian on Apr 26, 2012 12:13 AM