patternMinor
Execute PL/SQL script block in C#
Viewed 0 times
sqlblockscriptexecute
Problem
I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language I am using is c#, the DB is oracle10g
What I am actually doing is the following :
The PL/SQL block executes properly when I run it through ORACLE client but over here it throws an error ORA-00922: missing or invalid option
I want to ask :
Suggestions/Answers
Thanks.
What I am actually doing is the following :
//ConnectionSting is the connection String
OracleConnection connection = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
// Open the connection
connection.Open();
//queryFile contains the PL/SQL Script I am trying to execute;
String queryFile = ConfigurationManager.AppSettings["MasterDbScript"];
String dataInFile = new StreamReader(queryFile).ReadToEnd();
cmd.CommandText = dataInFile;
connection.Open();
cmd.ExecuteNonQuery();
//close the connection
connection.close();The PL/SQL block executes properly when I run it through ORACLE client but over here it throws an error ORA-00922: missing or invalid option
I want to ask :
- Are the scripts executed in a different way from the normal query?
- What am I doing wrong ?
Suggestions/Answers
Thanks.
Solution
From my limited understanding, blocks should be embedded in the C# or put in a package and called. I've seen it like this:
@"
BEGIN
SchemaName.PackageName.ProcedureName(p_LoginID => :loginID,
p_Password => :password, p_IPAddr => :ipAddr)";
END;”.Replace(Environment.NewLine, “\n”);Code Snippets
@"
BEGIN
SchemaName.PackageName.ProcedureName(p_LoginID => :loginID,
p_Password => :password, p_IPAddr => :ipAddr)";
END;”.Replace(Environment.NewLine, “\n”);Context
StackExchange Database Administrators Q#1258, answer score: 3
Revisions (0)
No revisions yet.