I got the following Requirements for a Project:
on a Website; run SQL Commands from Scripts and from a "Shell"; These Commands can be Selects, Creates, Procedures, PLSQL Packages, Views, Partitions, etc, just about everything. They want all Messages from the Queries. The Scripts are "as is".
They will not change their Queries to fit into my program. In those Scripts can be hundreds of Table creations/alter.
I am now checking how I can do this but it´s not really clear if it´s possible.
I can see that it´s not possible to run multiple queries in a single OracleCommand, only one by one. Like: "Select * from Users; select * from Tasks;" Does not work. And I need different Methods depending on what the Query is doing (Select vs Create).
My only ideas so far : Make an simple Parser to split everything at ";" and use those Queries in their own Commands. But then I will have to extend it if there are ";" inside the Queries. Or: Open an Process to the SQl Shell and then read everything out it writes.
Is there anything else I can do? Any ideas what would make the least problems?
CODE:
using OracleConnection orclCon = new OracleConnection("");
orclCon.Open();
var query = @" create or replace view testview(ID) as select
ID from USER";
var query2 = @" begin
create or replace view test1(ID) as select
ID from USER;
create or replace view test2(ID) as select
ID from USER;
end";
// OracleCommand command2 = new OracleCommand("begin " + query
+ " end");
OracleCommand command2 = new OracleCommand( query2 );
orclCon.InfoMessage += new
OracleInfoMessageEventHandler(HandleInfo);
command2.CommandType = System.Data.CommandType.StoredProcedure;
// or .Text
command2.Connection = orclCon;
try
{
// command2.ExecuteReader();
command2.ExecuteNonQuery();
// command2.ExecuteScalar();
}
catch (OracleException e)
{
return Content(e.Message.ToString() + " \n" +
e.Errors[0].ToString());
}
I have also tried to use execute immediate with and without '' in the query. And that´s only a Test... I have here real queries with over 400 Lines.
Error with the current Co
Blockquote
de:
ORA-06550: Zeile 2, Spalte 28:
PLS-00103: Fand das Symbol "CREATE" als eines der folgenden erwartet wurde:
( begin case declare exit for goto if loop mod null pragma...
