I have a need to create a TClientDataSet on a client from an Oracle 11g cursor contained in a package. I am using Delphi XE2 and DBExpress to connect to the DB and DataSnap to send the data back to the client.

When I configure the TSQLStoredProc to the TClientDataset at design time I can return the cursor as a TClientDataset with no problem and get expected results.

When I try to execute the Stored Procedure at runtime it returns an empty TClientDataset.

Is it possible to configure and execute an Oracle 11g Stored Procedure using TSQLStoredProc at runtime?

function to execute Runtime configuration This is the code that returns an empty ClientDataSet. The objective is to connect the pieces, set the value of the parameter, open the CDS and return the CDS.Data

Once agian I must confess I am new to the Delphi language. I have searched google, code.google, the Embarcadero Developer Network and DBExpress documentation all to no avail.
I just don't understand why there would be a difference between design time and runtime.

Please don't include tag information in the subject of your questions; the tagging system here is designed very well, and works without help. Also, it's not necessary to SHOUT any information in your question; even if we might have hearing difficulty, reading comprehension here tends to be very high, and text in ALL CAPITALS is more difficult to read (and quite annoying). We'll see your question just as well without it. Thanks.
–
Ken WhiteNov 1 '12 at 22:33

Your question is quite unclear. You say that design-time works but runtime doesn't, but the code in your client side Button1Click handler shows two totally separate function calls (getCDS_Data2('TESTTH1) and getCDS_Data3 (no parameter)) and says one of them works, but you only show the code for getCDS_Data2. It's hard to compare them and say why one works and the other doesn't when you don't give us the code to compare.
–
Ken WhiteNov 1 '12 at 22:42

I have updated the title to remove the tags and the words beginning with capital letters. The code for getCDS_Data3 is included perhaps you have just overlooked it. Do you have any idea why the function would work in design time and not runtime? Thanks
–
THarris76Nov 2 '12 at 2:30

You're correct. I overlooked it. The code for getCDS_Data2 is about forty lines of code, while getCDS_Data3 is three; I guess I expected them to be more similar because you wanted us to explain the different behavior. Can you explain how those are comparable? (The first is about 13x more code, which means apparently that every third line should be equivalent.)
–
Ken WhiteNov 2 '12 at 2:40

The reason the functions are so vastly different is because function getCDS_Data3 is getting its component settings from the design time code provided while function getCDS_Data2 is creating and setting its components during runtime. I need to execute stored procedures at runtime because it won't be efficient to drop a component on the data module for each stored procedure I need executed. I would greatly appreciate any input in regards to why a component would behave differently at runtime vs. Design time. Thanks
–
THarris76Nov 2 '12 at 14:06

As you can see if FProcParams are assigned then FreeProcParams is call which frees the params. Because I was setting the StroredProcName after I was assigning the param values the code was executing with cleared params and returning an empty cursor.

the order that produces correct results at runtime [from getCDS_Data2] is as follows: