oracle stored procedure

[inter]

I have a query in my code. I use this query to fill a dataset..... and with another query I fill a datareader.

 

but instead of a query I want to use a stored procedure from oracle

 

how do I call this stored procedure and fill my dataset and/or datareader?

 

thx

Edited September 20, 2005 by inter

[PlausiblyDamp]

http://www.xtremedotnettalk.com/showthread.php?t=87690

[inter]

http://www.xtremedotnettalk.com/showthread.php?t=87690

 

hey plausible,

 

I did it but I get an error....

 

PLS-00306: wrong number or types of arguments in call to 'NAMECALL'

ORA-06550: line 1, column 7

PL/SQL: statement ignored

 

I don't know what causes this error. I know the existing stored procedure is being used by other programmes so the error can't be in the stored procedure...

 

 

this is the code I have

 

   Sub Main()
       Dim OracleConn As New OracleConnection
       Dim Oracmd As New OracleCommand
       Dim Oradr As OracleDataReader
       Dim name As New OracleParameter
       Dim firstname As New OracleParameter
       Dim sortKey As New OracleParameter

       OracleConn.ConnectionString = "Data Source=database;User ID=user;Password=password"
       With Oracmd
           .Connection = OracleConn
           .CommandType = CommandType.StoredProcedure
           .CommandText = "NAMECALL"
           .Parameters.Add(name)
           .Parameters.Add(firstname) 
           .Parameters.Add(sortKey)
       End With

       With name
           .ParameterName = "NAME"
           .OracleType = OracleType.VarChar
           .Direction = ParameterDirection.Input
           .Value = "lastname"
       End With

       With firstname
           .ParameterName = "FIRSTNAME"
           .OracleType = OracleType.VarChar
           .Direction = ParameterDirection.Input
           .Value = "firstname"
       End With

       With sortKey
           .ParameterName = "SORTKEY"
           .OracleType = OracleType.Cursor
           .Direction = ParameterDirection.Output
       End With

       Dim i As Integer
       Try
           OracleConn.Open()
           Oradr = Oracmd.ExecuteReader()
           While Oradr.Read
               For i = 0 To Oradr.FieldCount - 1
                   Console.WriteLine(Oradr(i).ToString & "  ")
               Next
           End While

           OracleConn.Close()
       Catch ex As Exception
           Console.WriteLine("***** : {0}", ex.Message)
       Finally
           OracleConn.Close()
       End Try
   End Sub

 

 

the stored procedure looks something like this

 

procedure NAMECALL(name in VARCHAR2, firstName in VARCHAR2, sortKey out VARCHAR2)

IS

input VARCHAR2(71);

output VARCHAR2(71);

finalOutput VARCHAR2(71);


BEGIN

///////rest of the code //////

sortKey := finalOutput;

END NAMECALL;

[PlausiblyDamp]

Oracle isn't my strong point but you might try creating and configuring the parameters and then adding them to the command. If that doesn't help post back here and I'm sure somebody may be able to help.

[inter]

I changed it but I get the same error

 

copied the same message from above but the parameters are first configured and then added to the command.

 

Help is welcome...

 

 

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

 

 

PLS-00306: wrong number or types of arguments in call to 'NAMECALL'

ORA-06550: line 1, column 7

PL/SQL: statement ignored

 

I don't know what causes this error. I know the existing stored procedure is being used by other programmes so the error can't be in the stored procedure...

 

 

this is the code I have

 

   Sub Main()
       Dim OracleConn As New OracleConnection
       Dim Oracmd As New OracleCommand
       Dim Oradr As OracleDataReader
       Dim name As New OracleParameter
       Dim firstname As New OracleParameter
       Dim sortKey As New OracleParameter

       With name
           .ParameterName = "NAME"
           .OracleType = OracleType.VarChar
           .Direction = ParameterDirection.Input
           .Value = "lastname"
       End With

       With firstname
           .ParameterName = "FIRSTNAME"
           .OracleType = OracleType.VarChar
           .Direction = ParameterDirection.Input
           .Value = "firstname"
       End With

       With sortKey
           .ParameterName = "SORTKEY"
           .OracleType = OracleType.Cursor
           .Direction = ParameterDirection.Output
       End With

       OracleConn.ConnectionString = "Data Source=database;User ID=user;Password=password"
       With Oracmd
           .Connection = OracleConn
           .CommandType = CommandType.StoredProcedure
           .CommandText = "NAMECALL"
           .Parameters.Add(name)
           .Parameters.Add(firstname) 
           .Parameters.Add(sortKey)
       End With

       Dim i As Integer
       Try
           OracleConn.Open()
           Oradr = Oracmd.ExecuteReader()
           While Oradr.Read
               For i = 0 To Oradr.FieldCount - 1
                   Console.WriteLine(Oradr(i).ToString & "  ")
               Next
           End While

           OracleConn.Close()
       Catch ex As Exception
           Console.WriteLine("***** : {0}", ex.Message)
       Finally
           OracleConn.Close()
       End Try
   End Sub

 

 

the stored procedure looks something like this

 

procedure NAMECALL(name in VARCHAR2, firstName in VARCHAR2, sortKey out VARCHAR2)

IS

input VARCHAR2(71);

output VARCHAR2(71);

finalOutput VARCHAR2(71);


BEGIN

///////rest of the code //////

sortKey := finalOutput;

END NAMECALL;

[PlausiblyDamp]

  With sortKey
           .ParameterName = "SORTKEY"
           .OracleType = OracleType.Cursor
           .Direction = ParameterDirection.Output
       End With

is that correct? The stored proc defines sortKey as VARCHAR2 rather than a cursor.

[inter]

is that correct? The stored proc defines sortKey as VARCHAR2 rather than a cursor.

 

 

ow...

I changed it and get this exception now ...

 

Parameter 'SORTKEY': No size set for variable length data type: string

[inter]

ow...

I changed it and get this exception now ...

 

Parameter 'SORTKEY': No size set for variable length data type: string

 

 

 

OK...

 

I added

 

.size = "100"

 

to the with clausule of SORTKEY

 

now I don't get an error... but not yet a result.....

 

just "press any key to continue"

 

(I should have atleast 50 records) edit: should have 2 results

[inter]

I've got it working