Oracle Raises an Error when Calling Stored Proc

[Narayan]

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

[Andy O'Neill]

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
>
>
>

[Alex Kennedy]

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')"