Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

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

2 Answers

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 (
        $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.

share|improve this answer
up vote 3 down vote

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”);
share|improve this answer
1  
Replace(Environment.NewLine, “\n”) is a good idea – bernd_k Feb 16 '11 at 16:43

Your Answer

 
discard

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.