Saturday, June 09, 2012 6:24 PM
Currently we are using dynamic SQL-strings to pull adhoc data from an Oracle database. Because of SQL-Injection "threats", I am trying to convert this code to dynamically create a temporary Oracle stored procedure with parameters, then execute it.
Question is, HOW???
I have seen tons of articles creating dynamic SQL Server stored procedures, and tons for executing existing Oracle stored procedures. But nothing for creating dynamic Oracle stored procedures. Anybody have an example?
Also, for Oracle I'm using System.Data.OracleClient which, as I understand, has been deprecated. What should I be using?
Thanks very much.
Saturday, June 09, 2012 6:41 PM
Dynamic sql is very often kludge to cover for bad code. It is slow and hard to maintain.
You also leave open yourself open for sql injections in some cases.
Here is a detailed example how you can rewrite dynamic to pure code:
CREATE PROCEDURE sp_testdynamic ( @rows INT ) AS BEGIN DECLARE @sqltext VARCHAR(200) = 'SELECT top '+CAST(@rows AS VARCHAR)+' * FROM master..spt_values' EXEC(@sqltext) END go EXEC sp_testdynamic 10
Added for better performance:, this will retrieve maximum of 200 rows:
CREATE PROCEDURE sp_testimproved ( @rows INT ) AS BEGIN ;with firstrows AS (SELECT top 200 * FROM master..spt_values ), limitrows AS ( SELECT * FROM (SELECT row_number() OVER (ORDER BY (SELECT 1)) AS rownum, * FROM firstrows) AS A WHERE A.rownum <= @rows ) SELECT * FROM limitrows END go EXEC sp_testimproved 10
Here you have another website, where is shown some example of dynamic sql stored procedures:
Saturday, June 09, 2012 6:48 PM
Thanks for trying, but your example is for SQL Server, and I need to create an Oracle stored procedure. Also, your examples and the link you posted shows an stored procedures, but not created on the fly from C#.
Saturday, June 09, 2012 10:58 PMModeratorI can't help you at all with the dynamic Stored Procs, because I'm not really an Oracle developer. But, we have one customer who uses Oracle and we've needed to SELECT stuff from their database ... so I can answer your 2nd question: You should be using the Oracle.DataAccess.dll that comes with the Oracle Sql Developer app.
~~Bonnie Berent DeWitt [C# MVP]
Monday, June 11, 2012 6:38 AMModerator
First you will need the Oracle provider's support.
Then in the command text use the "execute immedate 'create your stored procedure'" as lgby Largeman replied: http://stackoverflow.com/questions/5999576/create-and-compile-on-the-fly-stored-procedure-in-oracle-from-c-net
For more research on .NET interacting with Oracle, I think you will need post questions to here: https://forums.oracle.com/forums/main.jspa;jsessionid=8d92100330d621e7fcc4245a47f8b4cea9076850ec22.e38NbN0LchiOci0LbhqSc3yQah4Te0?categoryID=84
MSDN Community Support | Feedback to us
- Marked As Answer by Mike Dos ZhangMicrosoft Contingent Staff, Moderator Thursday, June 14, 2012 3:36 AM