ORA-01006 [message #653868] |
Wed, 20 July 2016 23:38 |
|
pcouas
Messages: 112 Registered: February 2016
|
Senior Member |
|
|
Hi
I have error ORA-01006: la variable attachée (bind variable) n'existe pas.
I think problem is with pMonth1 variable in "OPEN cValuationReportMonth FOR v_SQLYEARMONTH USING pYear1,pMonth1,pVessel;"
But i use this variable
MONTHS_BETWEEN (LAST_DAY(TO_DATE ('':pYear1''||'':pMonth1'', ''YYMM'')), LAST_DAY(TO_DATE(va.month, ''YYMM'')))+1 as MYEAR ?
Where is my mistake ?
Regards
FUNCTION F_EXPORT_CONSO_VESSEL(pYear1 IN VARCHAR2,pMonth1 IN VARCHAR2,pVessel LO_VESSEL.VESSEL_uid%TYPE)
RETURN CURSOR_EXPORT
IS
cValuationReportMonth CURSOR_EXPORT;
v_SQLYEARMONTH varchar2(4000);
BEGIN
---- par Annnee
v_SQLYEARMONTH :='select
MONTHS_BETWEEN (LAST_DAY(TO_DATE ('':pYear1''||'':pMonth1'', ''YYMM'')), LAST_DAY(TO_DATE(va.month, ''YYMM'')))+1 as MYEAR
,sum(NVL(va.AMOUNT_USED,0)) AS AMOUNT_z01
,sum(NVL(va.QUANTITY_USED,0)) AS QUANTITY_z01
FROM
lo_vessel ve
,LO_VALUATION va
WHERE
(to_char(TO_DATE(va.month, ''YYMM''), ''YY'') = :pYear1 )
and va.VESSEL_UID=ve.VESSEL_UID
and ve.vessel_uid=:pVessel
GROUP BY TO_DATE(va.month, ''YYMM'')
ORDER BY TO_DATE(va.month, ''YYMM'')';
-- la boucle par mois
OPEN cValuationReportMonth FOR v_SQLYEARMONTH USING pYear1,pMonth1,pVessel;
RETURN cValuationReportMonth;
END F_EXPORT_CONSO_VESSEL
|
|
|
|
|
|
Re: ORA-01006 [message #653880 is a reply to message #653877] |
Thu, 21 July 2016 03:05 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Bind variables are applied positionally, not by name. The pyear1 parameter is not going to be automatically used for both binds of that name, you need to supply it twice.
That said, as Michel pointed out - nothing here is dynamic so you should just change it to static SQL and then the bind variable problem goes away automatically.
|
|
|
|