Lexical Parameters

[ChrisHunt]

With heavy heart, I'm trying to get a report to work in Oracle 9i Reports Developer. According to the help file this should be straightforward. Ha bloody ha.

I'm sending a comma delimited list of IDs into a text parameter called "ID_LIST". An example would be "10001,10023,12345" (without the quotes). The parameter has an initial value of "10001".

My query is
[tt]
SELECT r.res_id,
r.name,
c.contact_name,
c.office,
address_pkg.line_address(c.address_id) contact_address,
c.phone,
m.meeting_times,
NVL(m.description,address_pkg.line_address(m.address_id)) meeting_address,
r.reference,
r.added,
u.group_code
FROM resources r,contacts c,meeting_places m,user_groups u
WHERE r.restype_id = 1
AND c.correspondence = 'Y'
AND c.res_id = r.res_id
AND m.meet_type (+) = '1'
AND m.res_id (+) = r.res_id
AND u.group_id = r.group_id
AND r.res_id IN ( &ID_LIST )
ORDER BY UPPER(r.name),r.res_id
[/tt]
This is accepted in the "SQL Query Statement" dialog, and is using the initial value of ID_LIST to validate the SQL.

However when I run the report, I get the error message

REP-1239: Invalid column 'ID_LIST'


Is there some trick to getting lexical parameters to work, or is this just another Oracle bug?




-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[sem]

Ther's no trick, it should work. Check again you report definition more thoroughly. Try to recreate it from scratch: create parameter first, then use report wizard. I'm about to be sure that using lexical parameter in query is not the source of your problem. Probably it's also used somewhere else without ampersand.

Regards, Dima

 

[ChrisHunt]

Fixed it.

Problem was that I wanted the parameter to be as long as it possibly could - 63335 characters - to allow for longest possible list. It accepts this value when you create the parameter, but not when you run the report. Changing the length to 65534 characters (to allow one for the &?) gets the report running OK. Another undocumented "feature".

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk