This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Here is my problem: I need to have a stored procedure that accepts two parameters, one of which is a list of values ( '10', 02', '05', '11' etc... ) my 'WHERE' statement needs to look like the one below : WHERE I tried using EXECUTE IMMEDIATE but then the O/R mapper does not recognize ref cursor results and doesn't even map the procedure.... I tried a lot of things but just can't get it to work....I don't know it this can even be done....if so please help me cause I am running out of ideas... Thanks.
|
|
|
You don't say whether this is a runtime or design-time issue. Also, I'm not sure which database you are using though I am guessing Oracle since you mention "ref cursor results." I'll talk briefly about the two cases as this may give you some pointers, but we probably need to know more specifics in order to give specific advice. At design time, if you drag a sproc onto the designer, it tries to figure out the schema of the sproc result set so as to create or match an entity type. Because databases don't provide metadata about sproc result sets, the only way it can do this is to call the sproc with some dummy parameters and see what schema comes back (even if the query doesn't match anything, the DB will usually provide a schema). Therefore if the sproc is very sensitive to parameters, e.g. using them to construct dynamic SQL, this will probably fail, and either no sproc will appear on the design surface or it won't have a result type or the result type will be garbage. At run time, things are a bit simpler. You create a ProcedureQuery object with ProcedureParameters, and we just punt those over to the database. We neither know nor care how the sproc is implemented. All we care about is that we get a data reader back, either as the result of the sproc (SQL Server etc.) or via a ref cursor out parameter (Oracle). I don't know if this helps to understand your specific problem but I don't think we have enough info to comment on that just yet. Is the issue with passing the list to the ProcedureQuery? Is it with getting the output of the EXECUTE IMMEDIATE into the results ref cursor? |
|
|
Hi Ivan, and thanks for your reply... I am using an Oracle database. The problem is that I don't know how to pass the list to the proc ( as a string delimited with commas or how ?? ) and also how to get the output of EXECUTE IMMEDIATE into the results ref cursor and still be able to drag the proc onto the designer. I would like to avoid creating a "ProcedureQuery" but rather use a stored procedure which I can drag onto the designer. Again, I am not sure how to pass the second parameter ( the list of values ) so that the select statement works as it should. My other option is to create a procedure which takes "normal" integers as parameters and then stack the results one over another in Visual Studio...this, of course, is a waste of resources...getting all the data in one run would be much better... again, any help is appreciated...thank you so much !!
|
|
|
I'm afraid you've stretched my PL/SQL knowledge beyond its limits here! From googling around, it sounds like the correct way to pass the list to the stored procedure is via an array parameter. These appear to be supported only using the ODP provider, and LightSpeed doesn't currently provide a way to access them. (We would be happy to work with you to add this functionality if it turns out to be what you need.) If you're not sure how to create and use an array parameter in your sproc definition, I'm going to have to direct you to the Oracle forums or a more general site such as www.stackoverflow.com -- sorry, but I just don't have the PL/SQL knowledge to help you. (At design time, it's unlikely we'd be able to provide designer support for sproc array parameters, but we would be happy to help you encapsulate a ProcedureQuery in the same way that the designer generated code does, so that you don't need to muck around with PQs at your app code level.) Regarding the issue of doing EXECUTE IMMEDIATE'ing a SELECT into a ref cursor, again I don't have the PL/SQL skills to answer this question. Googling for "pl/sql execute immediate ref cursor" (without the quotes) shows up some links that look potentially promising but I can't vouch for their quality or relevance. Again, the Oracle forums or Stack Overflow may be your friend here. Sorry this is not terribly helpful -- it sounds like you have some issues to resolve on the PL/SQL side which we are not qualified to help with, but once you know what that side of things needs to look like we will be very glad to help you out on the LightSpeed end. |
|
|
Hi again ! Thanks again for your quick reply. It really helps to discuss this with someone even though it looks like our mutual knowledge of PL/SQL is not sufficient to resolve the matter...I will try the forums and come back to you if I need some LightSpeed help.... Thanks!! |
|