oracle stored procedure

I

inter

Active member
Joined
Jul 22, 2005
Messages
36
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
 
Last edited by a moderator:
PlausiblyDamp said:
http://www.computerhelp.forum/showthread.php?t=87690
Click to expand...

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 dont know what causes this error. I know the existing stored procedure is being used by other programmes so the error cant be in the stored procedure...


this is the code I have

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

Code: 
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;
 
Oracle isnt my strong point but you might try creating and configuring the parameters and then adding them to the command. If that doesnt help post back here and Im sure somebody may be able to help.
 
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 dont know what causes this error. I know the existing stored procedure is being used by other programmes so the error cant be in the stored procedure...


this is the code I have

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

Code: 
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;
 
Code: 
   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.
 
PlausiblyDamp said:
Code: 
is that correct? The stored proc defines sortKey as VARCHAR2 rather than a cursor.[/QUOTE]


ow...
I changed it and get this exception now ...

Parameter SORTKEY: No size set for variable length data type: string
Click to expand...
 
inter said:
ow...
I changed it and get this exception now ...

Parameter SORTKEY: No size set for variable length data type: string
Click to expand...



OK...

I added

.size = "100"

to the with clausule of SORTKEY

now I dont 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
 
Back
Top