Execute PL/SQL script block in C#

oracle-10g

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.

Best Answer

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 (
        $sql
    )

        $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)
        $ds.tables[0]
        Write-Host ''
}

function Execute-NonQuery
{
    param (
        $sql
    )

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

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.

Related Question