Called Stored proc on ORACLE SERVEUR VIA ADO

[Marc Rondeau]

Hi every expert. I very need Help!

I don't know what is wrong in my code. When i try to execute this code on my
access , it give me give this weard error

The stored proc have no parameters, i only need to call it. Thats it

error -2147467259 Error not specified

What is wrong!!?????

I very need help!!!

Thanck!

Public Sub CallStoredProc()
On Error GoTo ERRORHANDLER

Dim connOracle As New ADODB.Connection
Dim cmdOracle As New ADODB.Command

'Je crée la connexion
With connOracle
.Provider = "ORAOLEDB.Oracle"
.Properties("Data Source") = "ORACLESVR"
.Properties("User Id") = "Admin"
.Properties("Password") = "Admin1234"
.Properties("Persist Security Info") = False 'Permet de ne pas
afficher le password dans l'objet connOracle
.CursorLocation = adUseServer
.Open
End With

With cmdOracle
.ActiveConnection = connOracle
.CommandType = adCmdStoredProc
.CommandTimeout = 30

.Properties("PLSQLRSet") = False 'Item(80)
.CommandText = "begin IGOR_CREATE_USER; end;"

.Execute

End With

GoTo OVERERROR

ERRORHANDLER:

MsgBox err.Number & ":" & err.Description

OVERERROR:

Set connOracle = Nothing
Set cmdOracle = Nothing
'Set rsOracle = Nothing

End Sub
¨
ON ORACLE SERVER

IGOR_CREATE_USER:

AS
i_NomUsager VARCHAR2(30) :='Test123';
i_PassWord VARCHAR2(30) := '1234';

BEGIN
EXECUTE IMMEDIATE 'CREATE USER "' || i_NomUsager || '" PROFILE "DEFAULT" '
||
'IDENTIFIED BY ' || i_PassWord || ' DEFAULT TABLESPACE "CISQ_DAT"' ||
'TEMPORARY TABLESPACE "TEMP2" ' || 'ACCOUNT UNLOCK';
EXECUTE IMMEDIATE 'GRANT "CONNECT" TO ' || i_NomUsager;
END;

 

[Pieter Wijnen]

I don't think you need BEGIN .... END;
and also not ; (which is a SQL*Plus command not to be confused with a SQL
Statement)
ie
..CommandText = "IGOR_CREATE_USER"

..CommandText = "BEGIN IGOR_CREATE_USER; END"
might work though

HTH

Pieter