none
execute the statements against oracle database RRS feed

  • Question

  • Hi, All

     

        I have a script, which might consist of the following:

     

       update tablename set column= value where clause;  //comment: sql1

     

       update tablename set column= value where clause; // comment: sql2

      // comment: pl sql

       BEGIN

         some statements

       END;

    /

     

    I am using oracle .net data provider for oracle database, and I want to execute the statements block using ADO.net. How can I do this?

     

    Thanks for your help.

     

    Wednesday, September 24, 2008 4:41 PM

Answers

All replies

  • I would suspect that it would look something like the following: 

     

    Code Snippet

    Dim cnn As New OracleConnection(connectString)
    cnn.Open()


    Dim cmd As OracleCommand = cnn.CreateCommand()


    Dim sql As String = "UPDATE tablename set column= value where clause; " & _
                        "UPDATE tablename set column= value where clause; " & _
                        "BEGIN " & _
                           "some statements " & _
                        "END;"


    cmd.CommandText = sql

    cmd.ExecuteNonQuery()

     

     

     

    Thursday, September 25, 2008 7:09 PM
  •  

    Paul

     

       Really appreciate your reply. However, it doesn't work when we execute a script consists of several SQLs against oracle database as a command text using .net data provider. Any idea on this?

     

    Thanks a lot

    Haibing

    Saturday, September 27, 2008 4:31 PM
  • In that case you will probably need to put your script in a package/stored procedure and call that from your code instead.

     

    Monday, September 29, 2008 6:35 PM
  •  Paul P Clement IV wrote:
    In that case you will probably need to put your script in a package/stored procedure and call that from your code instead.

     

     

    Correct. This is the only way to do this?

     

    Thanks

     

    Friday, October 3, 2008 12:39 PM
  • OK, well I did a little more looking and found an example. I'm not sure if the syntax is different than what you tried (perhaps all statements must be between a BEGIN...END block) but the link is below and it's from Oracle's site:

     

    http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/anonyblock/index.html

     

     

    Friday, October 3, 2008 1:35 PM