Comment utiliser un Oracle Ref Curseur à partir de C# ODP.NET comme un ReturnValue Paramètre, sans l'aide d'une Fonction ou de la Procédure Stockée?

J'ai besoin d'aide pour comprendre si la façon dont je suis en train d'utiliser un Curseur de référence comme un ReturnValue Paramètre pour plusieurs enregistrements/valeurs, avec le PL/SQL juste d'être CommandText d'un objet OracleCommand et pas dans une Procédure Stockée ou une Fonction, il est encore possible.

Si cela n'est pas possible, ce que je suis en train de faire est de trouver un moyen de délivrer un PL/SQL qui va mettre à Jour un nombre inconnu d'enregistrements (dépend du nombre de match de la clause where), et de renvoyer les Identifiants de tous les dossiers mis à Jour dans un OracleDataReader, à l'aide d'un seul voyage aller-retour à la base de données, sans l'utilisation d'une Procédure Stockée ou une Fonction.

Je travaille avec Oracle 11g à l'aide de ODP.NET pour la communication avec un C# .NET 4.0 base de code qui utilise la connexion SQL pour récupérer/modifier des données. La simplification de la table de test définition que je suis en utilisant ressemble à ceci:

CREATE TABLE WorkerStatus
(
    Id                  NUMERIC(38)         NOT NULL
    ,StateId            NUMERIC(38)         NOT NULL
    ,StateReasonId      NUMERIC(38)         NOT NULL
    ,CONSTRAINT PK_WorkerStatus PRIMARY KEY ( Id )
)

Je l'ai pré-remplir la table avec trois valeurs de test comme suit:

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;

L'instruction SQL existante, chargé à partir d'un fichier de script nommé Oracle_UpdateWorkerStatus2, et contenues dans le OracleCommand.CommandText ressemble à ceci:

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;

J'ai créé un petit C# programme de test pour tenter d'isoler où j'obtiens une erreur ORA-01036 "illégales variable nom/numéro d'erreur" qui a un corps principal qui ressemble à ceci:

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);
}
}
}

J'ai essayé de changer les noms de paramètres, la dénomination et la non-nommer les UpdatedRecords paramètre, modification de l'ordre de sorte que le UpdatedRecords est le premier ou le dernier. La chose la plus proche que j'ai trouvé jusqu'à présent est la suivante StackOverflow question (Comment appeler un Oracle de la fonction avec un Curseur de référence comme paramètre de Sortie à partir de C#?), mais qui utilise encore une Fonction Stockée pour autant que je peux dire.

De l'exécution de l'Oracle_UpdateWorkerStatus2 PL/SQL script à partir de SQL Developer, il ouvre le "Entrez Lie boîte de dialogue" où je entrer les valeurs pour CurentStateId, StateId et StateReasonId comme dans le code ci-dessus, mais il donne l'erreur suivante: rapport de

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:

Je ne comprends vraiment pas pourquoi il me dit que la table n'existe pas, quand je l'ai défini le WorkerStatus table, et a déclaré le t_Ids variable, de type id_array, à une table. Toute l'aide ici est grandement apprécié.

OriginalL'auteur Paul | 2012-06-29