C# вызов оракула хранимой функции

Question

C# вызов оракула хранимой функции

create or replace function ar_knyga_egzistuoja(
id number
)
return number
is
kiekis number;
begin
select count(*) into kiekis from knygos where kn_id  = id;
return kiekis;
end;

Код C#:

conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "ar_knyga_egzistuoja";

cmd.CommandType = CommandType.StoredProcedure;
OdbcParameter param = new OdbcParameter();

cmd.Parameters.Add("id", OracleType.Number).Value = id;

cmd.ExecuteNonQuery();
var kiekis = Convert.ToString(cmd.Parameters["kiekis"].Value);

MessageBox.Show(kiekis);

cmd.Parameters.RemoveAt(0);

conn.Close();

Я получаю ошибку:

PLS-00221: 'AR_KNYGA_EGZISTUOJA' is not a procedure or is undefined
ORA-06550: line 1, column 7:

Это не процедура, а функция, но я знаю, что функцию, которую я могу вызывать как процедуры, что не так?

7

c# oracle stored-functions

Источник

user1067795 21 апр '14 в 14:46

4 ответа

Решение

Вам нужно вызывать вашу функцию как обычный оператор выбора, использовать "текстовый тип команды", а затем выполнить оператор выбора, чтобы вызвать вашу функцию и, наконец, использовать executeScalar для получения возвращаемого значения (которое, я полагаю, является просто одним значением)

это может быть что-то вроде этого:

conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select yourSchema.ar_knyga_egzistuoja(@id)";
cmd.CommandType = CommandType.Text;
OdbcParameter param = new OdbcParameter();
cmd.Parameters.Add("id", OracleType.Number).Value = id;
var result = cmd.ExecuteScalar();
MessageBox.Show(result);
cmd.Parameters.RemoveAt(0);
conn.Close();

4

Источник

user690413 21 апр '14 в 15:11

Я думаю, что проблема в вашем призвании ExecuteNonQuery, Таким образом, он ожидает процедуру (не возвращающую значение). Если вы позвоните ExecuteScalar это будет ожидать ar_knyga_egzistuoja быть функцией, которая есть.

1

Источник

user993547 21 апр '14 в 15:04

Imports System.Configuration
Imports System.Data.OracleClient
Public Class DBConnection
    Dim v_connectionstring As String
    Public OracleConnect As New OracleClient.OracleConnection
    Public OracleCommand As New OracleClient.OracleCommand
    Public OraDataAdp As New OracleClient.OracleDataAdapter
    Public Oratrans As OracleClient.OracleTransaction
    Dim OraPara As New OracleParameterCollection
    Public title As String
    Dim v_clearParameter As Boolean = True
    Public response As MsgBoxResult

    ''' <summary>
    '''  Open Oracle Connection
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Connect()
        Try
            v_connectionstring = ReadSetting("DBString") 'for test default comment it
            'v_connectionstring = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=team-pc)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)));uid=genmfg1;pwd=genmfg1;"
            If OracleConnect.State = ConnectionState.Open Then
                OracleConnect.Close()
            End If
            OracleConnect.ConnectionString = v_connectionstring
            OracleConnect.Open()
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Sub

    ''' <summary>
    '''  Close Oracle Connection if Open
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Disconnect()
        Try
            If OracleConnect.State = ConnectionState.Open Then
                OracleConnect.Close()
            End If
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Sub

    ''' <summary>
    '''  Read Settings From Configuratiob File
    ''' </summary>
    ''' <param name="key"> Key name from Configuration File aap.config</param>
    ''' <returns> return keyvalue as string </returns>
    ''' <remarks></remarks>
    Private Function ReadSetting(ByVal key As String) As String
        Dim result As String
        Try
            Dim appSettings = ConfigurationSettings.AppSettings
            result = appSettings(key)
            If IsNothing(result) Then
                result = "Not found"
            End If
            Return result
        Catch ex As Configuration.ConfigurationException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function

    ''' <summary>
    '''  Clear oracle Paramter Collection of Coomand
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub ClearParameter()
        Try
            OracleCommand.Parameters.Clear()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' <summary>
    '''  Add Parameter For Procedure or Function any type of oracle command
    ''' </summary>
    ''' <param name="p_paraType">'I' for Input,'O' for Output and 'IO' for Input Output both,'R' For R</param>
    ''' <param name="p_paraName"> Name Of Parameter, same which is in declaration of Oracle </param>
    ''' <param name="p_paraDBType"> Name Of Parameter Typw, same which is in declaration of Oracle </param>
    ''' <param name="p_paraSize"> Size Of output Parameter/Field when p_paratype 'I' Size not consider </param>
    ''' <param name="p_paraval"> Parameter Value as Object but must be compitible with object </param>
    ''' <remarks> </remarks>
    Public Sub AddParameter(ByVal p_paraType As String, ByVal p_paraName As String, ByVal p_paraDBType As OracleType, ByVal p_paraSize As Integer, ByVal p_paraval As Object)
        ' OracleCommand.Parameters.Add
        Try
            If v_clearParameter Then
                ClearParameter()
                v_clearParameter = False
            End If
            'OracleCommand.Parameters.Add( p_paraName,p_paraDBType,p_paraSize,p_paraval)
            If p_paraType = "I" Then
                OracleCommand.Parameters.Add(p_paraName, p_paraDBType).Value = p_paraval
            ElseIf p_paraType = "O" Then
                OracleCommand.Parameters.Add(p_paraName, p_paraDBType, p_paraSize).Direction = ParameterDirection.Output
            ElseIf p_paraType = "IO" Or p_paraType = "OI" Then
                OracleCommand.Parameters.Add(p_paraName, p_paraDBType, p_paraSize).Direction = ParameterDirection.InputOutput
                OracleCommand.Parameters(p_paraName).Value = p_paraval
            ElseIf p_paraType = "R" Then
                OracleCommand.Parameters.Add(p_paraName, p_paraDBType, p_paraSize).Direction = ParameterDirection.ReturnValue
            Else
                Throw New Exception("Invalid Parameter Type")
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' <summary>
    '''  Begin Transaction For Commit and Rollback Transaction
    ''' </summary>
    ''' <param name="p_isolvl"></param>
    ''' <remarks></remarks>
    Public Sub BeginTransaction(ByVal p_isolvl As System.Data.IsolationLevel)
        Try
            Oratrans = OracleConnect.BeginTransaction(p_isolvl)
            OracleCommand.Transaction = Oratrans
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' <summary>
    ''' Commit DML command 
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Commit()
        Try
            Oratrans.Commit()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' <summary>
    ''' Rollback DML command
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Rollback()
        Try
            Oratrans.Rollback()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    ''' <summary>
    '''  Fill Dataset With command
    ''' </summary>
    ''' <param name="p_PrcName"> Oracle Procedure Name </param>
    ''' <param name="p_TableNames"> Name Of the Return Table </param>
    ''' <returns> Return Dataset From Store Procedure </returns>
    ''' <remarks></remarks>
    Public Function ExecuteSPReturnDatasetWithCommand(ByVal p_PrcName As String, ByVal p_TableNames As String()) As DataSet
        Dim _ds As DataSet = New DataSet
        Try
            Connect()
            OracleCommand.CommandText = p_PrcName
            OracleCommand.Connection = OracleConnect
            OracleCommand.CommandType = CommandType.StoredProcedure
            OraDataAdp = New OracleClient.OracleDataAdapter(OracleCommand)
            OracleCommand.ExecuteNonQuery()
            OraDataAdp.Fill(_ds)
            Try
                For Each _tbstr As String In p_TableNames
                    _ds.Tables(0).TableName = _tbstr
                Next
            Catch ex As Exception
                Throw New Exception("No Of Return Table Does not match with Procedure")
            End Try
            Return _ds
        Catch ex As Exception
            Throw ex
        Finally
            v_clearParameter = True
            Disconnect()
        End Try
    End Function

    ''' <summary>
    '''  Execute Stored Procedure With Transaction
    ''' </summary>
    ''' <param name="p_prcname"> Oracle Procedure Name </param>
    ''' <remarks></remarks>
    Public Sub ExecuteSPFunWithTransaction(ByVal p_prcname As String)
        Try
            OracleCommand.CommandText = p_prcname
            OracleCommand.Connection = OracleConnect
            OracleCommand.CommandType = CommandType.StoredProcedure
            OracleCommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            v_clearParameter = True
        End Try
    End Sub

    ''' <summary>
    '''  Execute Sql Query as string
    ''' </summary>
    ''' <param name="p_Sqlqry"> sql query  for execute </param>
    ''' <remarks></remarks>
    Public Sub ExecuteSqlWithcommand(ByVal p_Sqlqry As String)
        Try
            Connect()
            OracleCommand.CommandText = p_Sqlqry
            OracleCommand.Connection = OracleConnect
            OracleCommand.CommandType = CommandType.Text
            OracleCommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            v_clearParameter = True
            Disconnect()
        End Try
    End Sub

    ''' <summary>
    '''  Return Scalar value from sql stmt
    ''' </summary>
    ''' <param name="p_Sqlqry"></param>
    ''' <returns> Object for all datatype compatibalite </returns>
    ''' <remarks></remarks>
    Public Function ExecuteSqlReturnScalarWithcommand(ByVal p_Sqlqry As String) As Object
        Dim _obj As Object
        Try
            Connect()
            OracleCommand.CommandText = p_Sqlqry
            OracleCommand.Connection = OracleConnect
            OracleCommand.CommandType = CommandType.Text
            _obj = OracleCommand.ExecuteScalar()
        Catch ex As Exception
            Throw ex
        Finally
            v_clearParameter = True
            Disconnect()
        End Try
        Return _obj
    End Function

    ''' <summary>
    '''  Execute Sp/Function With Command
    ''' </summary>
    ''' <param name="p_prcname"> Procedure and Function name </param>
    ''' <remarks></remarks>
    Public Sub ExecutSPFunPWithcommand(ByVal p_prcname As String) 
        Try
            Connect()
            OracleCommand.CommandText = p_prcname
            OracleCommand.Connection = OracleConnect
            OracleCommand.CommandType = CommandType.StoredProcedure
            OracleCommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            v_clearParameter = True
            Disconnect()
        End Try
    End Sub

    ''' <summary>
    '''  Get Parameter Value From Parameter list as out parameter
    ''' </summary>
    ''' <param name="p_parametername"></param>
    ''' <returns> return value as object</returns>
    ''' <remarks></remarks>
    Public Function GetParameterValue(ByVal p_parametername As String) As Object
        Try
            Return OracleCommand.Parameters(p_parametername).Value
        Catch ex As Exception
            Return Nothing
        Finally
        End Try
    End Function
End Class

-2

Источник

user7440504 19 янв '17 в 10:43

Другие вопросы по тегам c# oracle stored-functions

user1446184 21 апр '14 в 15:23 2014-04-21 15:23 · Accepted Answer · 2014-04-21 15:23

ORA-06550 код, который вы получаете, означает, что функция была скомпилирована с недопустимыми операторами и должна быть переписана. Я не вижу ничего явно неправильного в коде, поэтому у вас могут возникнуть проблемы, такие как права доступа или неправильные имена таблиц, и вы должны проверить, можете ли вы в первую очередь запустить функцию в редакторе PL/SQL. Затем, после того, как вы запустите это, попробуйте...

var cmd = new OracleCommand();

cmd.Connection = conn;
cmd.CommandText = "ar_knyga_egzistuoja";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("id", id);
cmd.Parameters.Add("kiekis", OracleType.Number);
cmd.Parmeters["kiekis"].Direction = ParameterDirection.ReturnValue;

cmd.Connection.Open();
cmd.ExecuteNonQuery();

var kiekis = Convert.ToString(cmd.Parameters["kiekis"].Value);

MessageBox.Show(kiekis);
cmd.Connection.Close();

Это должно быть в состоянии выполнить функцию как хранимая процедура, ожидая возвращаемого значения с именем kiekis типа number быть доступным для работы.