Call Oracle Stored Proceedure VB.NET

Discussion in 'ASP .Net' started by Problematic coder, Mar 8, 2007.

  1. I have a form based application that inputs values into various oracle
    tables, after it has done this I want to call a stored proceedure in
    Oracle which is already written to do more work on this data, it does
    not return any values to vb nor does it expect any criteria or input.

    I already have a connection to Oracle open using the following code:

    Dim objcnn As New Data.OracleClient.OracleConnection(ConnectStr)
    Dim objcom As Data.OracleClient.OracleCommand
    objcnn.Open()
    objcom = New Data.OracleClient.OracleCommand(strSQLinsert, objcnn)
    objcom.ExecuteNonQuery()

    Assuming the stored proceedure is named "my_proc" could I use a
    similar command like:

    objcom = New Data.OracleClient.OracleCommand(strCallMyProc, objcnn)

    If so what would strCallMyProc = ?

    If not how else could I or should I call it?

    Thanks
     
    Problematic coder, Mar 8, 2007
    #1
    1. Advertising

  2. "my_proc"

    You also have to ensure the Commands CommandType is set to stored procedure.
    And, since you are using OracleClient, you need to return a REF_CURSOR and
    set as an out parameter (which you will never use, go figure). With OleDb,
    you still have a REF_CURSOR return, but do not have to declare it as a
    param. Yes, a strange anomaly. Once you have the REF_CURSOR param declared,
    you simply use the DataSet or Reader as normal with other databases.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA

    *********************************************
    Think outside the box!
    *********************************************
    "Problematic coder" <> wrote in message
    news:...
    >I have a form based application that inputs values into various oracle
    > tables, after it has done this I want to call a stored proceedure in
    > Oracle which is already written to do more work on this data, it does
    > not return any values to vb nor does it expect any criteria or input.
    >
    > I already have a connection to Oracle open using the following code:
    >
    > Dim objcnn As New Data.OracleClient.OracleConnection(ConnectStr)
    > Dim objcom As Data.OracleClient.OracleCommand
    > objcnn.Open()
    > objcom = New Data.OracleClient.OracleCommand(strSQLinsert, objcnn)
    > objcom.ExecuteNonQuery()
    >
    > Assuming the stored proceedure is named "my_proc" could I use a
    > similar command like:
    >
    > objcom = New Data.OracleClient.OracleCommand(strCallMyProc, objcnn)
    >
    > If so what would strCallMyProc = ?
    >
    > If not how else could I or should I call it?
    >
    > Thanks
    >
     
    Cowboy \(Gregory A. Beamer\), Mar 9, 2007
    #2
    1. Advertising

  3. Thank you for answering my question, however I am having a hard time
    understanding your answer though undoubtably correct.

    I am not familiar with the term REF_CURSOR, I assume what you are
    saying is I have to return something whether I need it or not?

    I do not want anybody to do my work for me but it might be helpful if
    somebody could post some example code which would call the proceedure,
    make it run and return a value if that is necessary

    Again I appreciate your time

    On Mar 8, 8:45 pm, "Cowboy \(Gregory A. Beamer\)"
    <> wrote:
    > "my_proc"
    >
    > You also have to ensure the Commands CommandType is set to stored procedure.
    > And, since you are using OracleClient, you need to return a REF_CURSOR and
    > set as an out parameter (which you will never use, go figure). With OleDb,
    > you still have a REF_CURSOR return, but do not have to declare it as a
    > param. Yes, a strange anomaly. Once you have the REF_CURSOR param declared,
    > you simply use the DataSet or Reader as normal with other databases.
    >
    > --
    > Gregory A. Beamer
    > MVP; MCP: +I, SE, SD, DBA
    >
    > *********************************************
    > Think outside the box!
    > *********************************************"Problematic coder" <> wrote in message
    >
    > news:...
    >
    >
    >
    > >I have a form based application that inputs values into various oracle
    > > tables, after it has done this I want to call a stored proceedure in
    > > Oracle which is already written to do more work on this data, it does
    > > not return any values to vb nor does it expect any criteria or input.

    >
    > > I already have a connection to Oracle open using the following code:

    >
    > > Dim objcnn As New Data.OracleClient.OracleConnection(ConnectStr)
    > > Dim objcom As Data.OracleClient.OracleCommand
    > > objcnn.Open()
    > > objcom = New Data.OracleClient.OracleCommand(strSQLinsert, objcnn)
    > > objcom.ExecuteNonQuery()

    >
    > > Assuming the stored proceedure is named "my_proc" could I use a
    > > similar command like:

    >
    > > objcom = New Data.OracleClient.OracleCommand(strCallMyProc, objcnn)

    >
    > > If so what would strCallMyProc = ?

    >
    > > If not how else could I or should I call it?

    >
    > > Thanks- Hide quoted text -

    >
    > - Show quoted text -
     
    Problematic coder, Mar 9, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    311
  2. Problematic coder

    Call Oracle Stored Proceedure VB.NET

    Problematic coder, Mar 8, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    339
    Problematic coder
    Mar 8, 2007
  3. Problematic coder

    Erro calling stored proceedure

    Problematic coder, Mar 13, 2007, in forum: ASP .Net
    Replies:
    9
    Views:
    464
    Problematic coder
    Mar 13, 2007
  4. Mick Walker
    Replies:
    4
    Views:
    414
    Lloyd Sheen
    Jun 20, 2007
  5. Feyruz
    Replies:
    4
    Views:
    2,353
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page