Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

  //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;

  //close the connection

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 ?



share|improve this question
What's the usefulness of the StreamReader injection? That shouldn't matter, you're trying to convert a string to a string :\ – jcolebrand Feb 16 '11 at 14:53
up vote 1 down vote accepted

As you are not showing your script to us, lets work the other way around. create a table in your database

Create table Test_call_count (
count number(10)
insert into Test_call_count values (0);

and put the following into your script file

update  Test_call_count set count = count + 1;

After executing your code, you can check if it worked by selecting from the database.

The next step will be to use some begin end; block not returning any result sets.

Generally Ado can't process whole scripts. Using sql-server it can process batches, that are parts of a script separated by go statements. As far as I know for Oracle it can only process a single sql-statements or a begin end block.

Edit: Here is a litte PowerShell you can use to try what is possible:

if ($ora_loaded -eq $null)
    $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 

$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password" 

function Execute-Query
    param (

        $conn = new-object Oracle.DataAccess.Client.OracleConnection 
        $conn.ConnectionString = $ConnectionString 
        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
    #   $cmd.CommandTimeout=$timeout
        $ds = New-Object system.Data.DataSet
        $da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
        $nuLL = $da.fill($ds)
        Write-Host ''

function Execute-NonQuery
    param (

        $conn = new-object Oracle.DataAccess.Client.OracleConnection 
        $conn.ConnectionString = $ConnectionString 
        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
    #   $cmd.CommandTimeout=$timeout

Execute-Query "select * from Test_call_count;"
Execute-NonQuery "update  Test_call_count set count = count + 1"
Execute-NonQuery "begin update  Test_call_count set count = count + 1; end;"

For example you see, that the following fails ( I added a semicolon):

Execute-NonQuery "update  Test_call_count set count = count + 1;"

And this fails too:

Execute-NonQuery "begin update  Test_call_count set count = count + 1; end;

In a first approximation you can think of the '/' as similar to the sql-server batch delimiter go.

share|improve this answer

From my limited understanding, blocks should be embedded in the C# or put in a package and called. I've seen it like this:

   SchemaName.PackageName.ProcedureName(p_LoginID => :loginID, 
      p_Password => :password, p_IPAddr => :ipAddr)";
END;”.Replace(Environment.NewLine, “\n”);
share|improve this answer
Replace(Environment.NewLine, “\n”) is a good idea – bernd_k Feb 16 '11 at 16:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.