Hi Jarrod,
You would want to set the Complex XML to false to use with the picker control. You mention an error you are seeing in the Tester tool, but never mentioned what the error was that you received. Can you let us know the error as the error you would have been seeing would be unrelated to the picker control as that point in your testing as the tester is independent from the form controls.
One error you might be seeing is that the dataset you are returning has a null value and your error may be something like "input string in the incorrect format." or something that effect. The Oracle broker has issues with null values. You may have also noticed that when you set Complex to False you see all the columns, but all of your columsn are not properly typed. They will be all set to text. The Oracle broker doens't properly type recordsets when you set complex to false.
If the input string in correct format error is the case, then you would need to edit your stored procedure and individually declare the columns you want to return (instead of using and wrap those in a COALESCE statement so it would look something like:
OPEN P_RECORDSET FOR
SELECT COALESCE(OMyColumn], "") AS MyColumn From Table WHERE X = Y.
You are essentially swapping a null value for an empty string which can be handled gracefully by the broker.
if I am totally off base about the error, if you can share your message I can help further.
S.
Hi Scott,
Thanks for the speedy reply, my actual error is attached.
A colleague has taken a look and provided a workable solution: after changing the service instance, delete the smart objects and recreate them.
It appears as though the switching between 'Complex output as XML' in the Service Instance wasn't being reflected in the SmartObjects (even after a refresh), so deleting it and recreating has worked a treat.
Regards,
Jarrod
Yes, that makes sense. Changes to Service Instance configuration has to be reconciled with your smart objects. Any changes to the Service Instance will necessitate the need to update your Smart Objects.
Glad you worked it you. Keep an eye on Nulls and the Data types just so you aren't suprised.
S.