I am migrating an Sql Server Application to Oracle and i have a lot of store proc.

in sql server, most of them return values or record. here is an example :


CREATE PROCEDURE sp_CheckInfoUser
(@CodeUsager AS Varchar(100) = '',
@MotPasse AS Varchar(100) = '')

AS


DECLARE @Total AS Int

IF @MotPasse = ''
SELECT @Total = COUNT(*) FROM AAUsager
Where CodeUsager = @CodeUsager
ELSE
SELECT @Total = COUNT(*) FROM AAUsager
Where CodeUsager = @CodeUsager AND MotPasse = @MotPasse


SELECT @Total




----------------------------
in oracle I created the following store proc :

CREATE OR REPLACE PROCEDURE "GV"."SP_CHECKINFOUSER" (
pCodeUsager In Varchar,
pMotPasse In Varchar,
pTot Out number)

Is
pTotal Number(12,0);
Begin

IF pMotPasse <> '' then
SELECT COUNT(*) into pTotal FROM AAUsager
Where CodeUsager = pCodeUsager AND MotPasse = pMotPasse;
ELSE
SELECT COUNT(*) into pTotal FROM AAUsager
Where CodeUsager = pCodeUsager;
end if;


pTot := pTotal;

End;

----------------------

it is functionning perfectly..... in Sql plus... but the code in Visual Studio .net never return anything..... in Sql Server it is Ok, in oracle (when there is no returning values) it ok too, but when i try to return values with a param Out in oracle : nothing.... ... here is a sample of the code i tryed in VB.Net, it is an ADODB connection. in oracle, rs.state is always closed.:

'================================================= =========
Public Function ExecScalarAlain(ByVal pxTypeConnection As xTypeConnection, _
ByVal sSql As String, _
ByVal poCon As ADODB.Connection, _
Optional ByRef ParmArray(,) As String = Nothing) As String
'================================================= =========
'================================================= =========
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim adt As OleDb.OleDbDataAdapter
Dim rs As New ADODB.Recordset()
Dim sReturnValue As String

Try

If IsNothing(poCon) = False Then
cn = poCon
Else
cn = fctOpenConnection(pxTypeConnection)
End If

cmd = New ADODB.Command()
cmd.ActiveConnection = cn

If Not (ParmArray Is Nothing) Then
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = sSql
Call PopulateCommandParms(ParmArray, cmd)
Else
cmd.CommandType = CommandType.Text
cmd.CommandText = sSql
End If

rs = cmd.Execute

If IsNothing(rs) = False Then
If rs.State <> 0 Then

If rs.EOF = True And rs.BOF = True Then
sReturnValue = 0
Else
sReturnValue = rs(0).Value
End If
rs.Close()
End If
End If


cmd = Nothing
rs = Nothing

Return sReturnValue

Catch e As Exception
MsgBox(e.Message & " / " & e.Source)

Finally
cmd = Nothing
End Try

End Function


Private Sub PopulateCommandParms(ByVal arParms(,) As String, ByRef cmd As ADODB.Command)

'Adds each parameter in the array to the command object
Dim i As Integer
Try
cmd.Parameters.Refresh()
For i = 0 To UBound(arParms)
cmd.Parameters(arParms(i, 0)).Value = arParms(i, 1)
Next

Catch e As Exception
MsgBox(e.Message)
End Try

End Sub