Use of Bind Variables

[frasernm]

Hi,

Got a script for a report that uses three bind variables. (Excerpts from the script are below)

This first query to produce the headers for the csv file works ok, but the second query to display the data returns 'ORA-01008: not all variables bound', but not if I replace all :startDate variables with a hard-coded date. As this is not practical does anyone have a better solution?

Thanks,

Fraser

-- Declare Bind Variables
VARIABLE startDate CHAR(11)
VARIABLE endDate CHAR(11)
VARIABLE eventID CHAR(10)

BEGIN
:startDate := '1-SEP-2001';
:endDate := '1-OCT-2001';
:eventID := 'EDT184';
END;
/

-- display column headers for csv

SELECT '"CustomerID","' ||
to_char(to_datestartDate, 'DD-MON-YYYY')) || '","' ||
to_char(to_datestartDate, 'DD-MON-YYYY') + 1) || '","' ||
etc ...
to_char(to_datestartDate, 'DD-MON-YYYY') + 30) || '",'
FROM DUAL;

--display data

SELECT '"' || CustomerID || '",' ||
to_char(count(decode(trunc(Odate), to_datestartDate, 'DD-MON-YYYY'), 1))) || ',' ||
to_char(count(decode(trunc(Odate), to_datestartDate, 'DD-MON-YYYY') + 1, 1))) || ',' ||
to_char(count(decode(trunc(Odate), to_datestartDate, 'DD-MON-YYYY') + 2, 1))) || ',' etc...
to_char(count(decode(trunc(Opendate), to_datestartDate, 'DD-MON-YYYY') + 30, 1))) || ','
FROM event, event_type
WHERE trunc(opendate) >= to_datestartDate,'DD-MON-YYYY')
and trunc(opendate) <= to_dateendDate,'DD-MON-YYYY')
and event_code = :eventID
and lower(text) like lower (pattern)
GROUP BY CustomerID
ORDER BY CustomerID ASC;