Мне нужна помощь, чтобы понять, является ли способ, которым я пытаюсь использовать Ref Cursor в качестве параметра ReturnValue для нескольких записей/значений, с PL/SQL просто CommandText объекта OracleCommand, а не в хранимой процедуре или функции, даже возможно.
Если это невозможно, то я пытаюсь найти способ выполнить оператор PL/SQL, который будет обновлять неизвестное количество записей (в зависимости от того, сколько из них соответствует предложению WHERE ), и возвращать Идентификаторы всех записей Обновляются в OracleDataReader с использованием одного кругового -обращения к базе данных без использования хранимой процедуры или функции.
Я работаю с Oracle 11g, используя ODP.NET для связи с существующей базой кода C #.NET 4.0 -, которая использует соединение SQL для извлечения/изменения данных. Упрощенное определение тестовой таблицы, которое я использую, выглядит так:
CREATE TABLE WorkerStatus
(
Id NUMERIC(38) NOT NULL
,StateId NUMERIC(38) NOT NULL
,StateReasonId NUMERIC(38) NOT NULL
,CONSTRAINT PK_WorkerStatus PRIMARY KEY ( Id )
)
Я предварительно -заполняю таблицу тремя тестовыми значениями, например так:
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (1, 0, 0)';
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (2, 0, 0)';
EXECUTE IMMEDIATE 'INSERT INTO WorkerStatus (Id, StateId, StateReasonId)
VALUES (3, 0, 0)';
END;
Существующий оператор SQL, загруженный из файла сценария с именем Oracle _UpdateWorkerStatus2 и содержащийся в OracleCommand.CommandText, выглядит следующим образом:
DECLARE
TYPE id_array IS TABLE OF WorkerStatus.Id%TYPE INDEX BY PLS_INTEGER;
t_ids id_array;
BEGIN
UPDATE WorkerStatus
SET
StateId = :StateId
,StateReasonId = :StateReasonId
WHERE
StateId = :CurrentStateId
RETURNING Id BULK COLLECT INTO t_Ids;
SELECT Id FROM t_Ids;
END;
Я создал небольшую тестовую программу на C #, чтобы попытаться изолировать, где я получаю ошибку ORA -01036 «недопустимое имя/номер переменной», основное тело которой выглядит так:
using System;
using System.Configuration;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace OracleDbTest
{
class Program
{
static void Main(string[] args)
{
// Load the SQL command from the script file.
StringBuilder sql = new StringBuilder();
sql.Append(Properties.Resources.Oracle_UpdateWorkerStatus2);
// Build and excute the command.
OracleConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleSystemConnection"].ConnectionString);
using (OracleCommand cmd = new OracleCommand(sql.ToString(), cn))
{
cmd.BindByName = true;
cn.Open();
OracleParameter UpdatedRecords = new OracleParameter();
UpdatedRecords.OracleDbType = OracleDbType.RefCursor;
UpdatedRecords.Direction = ParameterDirection.ReturnValue;
UpdatedRecords.ParameterName = "rcursor";
OracleParameter StateId = new OracleParameter();
StateId.OracleDbType = OracleDbType.Int32;
StateId.Value = 1;
StateId.ParameterName = "StateId";
OracleParameter StateReasonId = new OracleParameter();
StateReasonId.OracleDbType = OracleDbType.Int32;
StateReasonId.Value = 1;
StateReasonId.ParameterName = "StateReasonId";
OracleParameter CurrentStateId = new OracleParameter();
CurrentStateId.OracleDbType = OracleDbType.Int32;
CurrentStateId.Value = 0;
CurrentStateId.ParameterName = "CurrentStateId";
cmd.Parameters.Add(UpdatedRecords);
cmd.Parameters.Add(StateId);
cmd.Parameters.Add(StateReasonId);
cmd.Parameters.Add(CurrentStateId);
try
{
cmd.ExecuteNonQuery();
OracleDataReader dr = ((OracleRefCursor)UpdatedRecords.Value).GetDataReader();
while (dr.Read())
{
Console.WriteLine("{0} affected.", dr.GetValue(0));
}
dr.Close();
}
catch (OracleException e)
{
foreach (OracleError err in e.Errors)
{
Console.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
System.Diagnostics.Debug.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
}
}
cn.Close();
}
Console.WriteLine("Press Any Key To Exit.\n");
Console.ReadKey(false);
}
}
}
Я пытался изменить имена параметров, назвав, а не -назвав параметр UpdatedRecords, изменив порядок так, чтобы UpdatedRecords был первым или последним. Самое близкое, что я нашел до сих пор, это следующий вопрос StackOverflow(Как вызвать функцию Oracle с параметром Ref Cursor as Out -из C #?),но это все еще использует хранимую функцию, насколько я могу судить.
Запустив сценарий Oracle _UpdateWorkerStatus2 PL/SQL из SQL Developer, он открывает диалоговое окно «Ввод привязок», в котором я ввожу значения для CurentStateId, StateId и StateReasonId, как в приведенном выше коде, но выдает следующий отчет об ошибке:
Error report:
ORA-06550: line 13, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 13, column 2:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Я действительно не понимаю, почему он говорит мне, что таблица не существует, когда я определил таблицу WorkerStatus и объявил переменную t _Ids массива типа id _также таблицей. Любая помощь здесь очень ценится.