Oracle Raises an Error when Calling Stored Proc


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Oracle Raises an Error when Calling Stored Proc

  1. #1
    Narayan Guest

    Oracle Raises an Error when Calling Stored Proc



    Hi All,

    I am facing a strange problem with ORacle VB connectivity. The code that
    I wrote is as follows :-

    Private Sub Form_Load()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    conn.ConnectionString = "Provider=MSDAORA.1;User ID= NV1040/temp123"
    & ";Data Source=cpcidev" & ";Persist Security Info=False"

    conn.Open

    conn.Execute "admin_package.create_user'X','Y','x','ads','16','NV1040')"

    End Sub

    The create_user procedure takes "username, firstname, lastname, comments,
    roles, created by" as its 6 paramters which are given in the code.

    I wanted to call a stored proc in Oracle that creates a user for the application
    (Not a database user). The stored proc inserts the new user details in a
    table. The problem I face is, the above code works fine if user name I insert
    doesnt exisit already.. If the user I insert already exist it thorws me an
    error message "ORA-00900: invalid SQL statement" insetead of returning "Uniqure
    Constraint Violation". If it is an invalid SQL statement it should return
    an error message even if I insert a user that doesnt exist in the table.
    This error comes only when I try to insert a duplicate value. I also tried
    executing the stored procedure by creating Paramaters through ADO. The same
    error is returned. Can anyone help me in this ?? Your help will be very much
    appreciated.

    Narayan




  2. #2
    Andy O'Neill Guest

    Re: Oracle Raises an Error when Calling Stored Proc


    Two thoughts.
    1) Change the stored procedure so's it checks if the user is already there
    before inserting.
    Add a parameter so's you can tell the user.
    Maybe not an option...
    2) I think you'll find there's more than one error in the error collection
    returned by your ADO call.
    Not got the code to hand, take a look at MSDN for an example... but
    I think you can probably trap the specific error in your error handler
    for the procedure the call's from.

    If neither approach works for you, post again.
    If I have the time, I'll look for similar example.


    "Narayan" <narayankv@yahoo.com> wrote:
    >
    >
    >Hi All,
    >
    >I am facing a strange problem with ORacle VB connectivity. The code that
    >I wrote is as follows :-
    >
    >Private Sub Form_Load()
    > Dim conn As New ADODB.Connection
    > Dim rs As New ADODB.Recordset
    >
    > conn.ConnectionString = "Provider=MSDAORA.1;User ID= NV1040/temp123"
    >& ";Data Source=cpcidev" & ";Persist Security Info=False"
    >
    > conn.Open
    >
    > conn.Execute "admin_package.create_user'X','Y','x','ads','16','NV1040')"
    >
    >End Sub
    >
    >The create_user procedure takes "username, firstname, lastname, comments,
    >roles, created by" as its 6 paramters which are given in the code.
    >
    >I wanted to call a stored proc in Oracle that creates a user for the application
    >(Not a database user). The stored proc inserts the new user details in a
    >table. The problem I face is, the above code works fine if user name I insert
    >doesnt exisit already.. If the user I insert already exist it thorws me

    an
    >error message "ORA-00900: invalid SQL statement" insetead of returning "Uniqure
    >Constraint Violation". If it is an invalid SQL statement it should return
    >an error message even if I insert a user that doesnt exist in the table.
    >This error comes only when I try to insert a duplicate value. I also tried
    >executing the stored procedure by creating Paramaters through ADO. The same
    >error is returned. Can anyone help me in this ?? Your help will be very

    much
    >appreciated.
    >
    >Narayan
    >
    >
    >



  3. #3
    Alex Kennedy Guest

    Re: Oracle Raises an Error when Calling Stored Proc



    I don't know if this is a typo or if you pasted the code straight out of
    VB but it looks like you've missed out a "(" symbol.

    Alex

    > conn.Execute "admin_package.create_user'X','Y','x','ads','16','NV1040')"




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center