Dynamic Oracle Stored Procedure

Question

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.

Answer 1

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: 

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4473634500618

---

Mitja

Answer 2

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#.

Anybody?

Answer 3

I 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]

geek-goddess-bonnie.blogspot.com

Answer 4

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 

Best wishes,

---

Mike Zhang[MSFT]
MSDN Community Support | Feedback to us