HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Execute PL/SQL script block in C#

Submitted by: @import:stackexchange-dba··
0
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 :

//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 :
  1. Are the scripts executed in a different way from the normal query?
  2. 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.