"；pls-00201标识符必须声明为“；在ASP.NET/C#中

本文关键字：ASP NET 声明 pls-00201 quot 标识符 | 更新日期: 2023-09-27 18:30:14

我有以下存储过程，我想在C#/.NET中使用：

CREATE OR REPLACE
procedure contact_return(
    v_urn IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR )
AS
  sql_statement VARCHAR2(4000) := '        
  SELECT URN,
    FIRSTNAME,
    LASTNAME,
    TITLE,
    MOBILE,
    WORK,
    EMAIL
  FROM CONTACT
  WHERE urn = NVL(:1,v_contact_urn)';
BEGIN
  OPEN p_cursor FOR sql_statement USING v_urn;

使用此代码，我得到错误：PLS-00201:必须声明标识符"CONTACT_RETURN"

        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "contact_return";
        cmd.Parameters.Add("v_urn", OracleDbType.Int64).Value = null;
        cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

据我所见，我已经正确地声明了存储过程（"contact_return"），所以无法弄清楚为什么会出现这个错误。

如有任何帮助，我们将不胜感激，谢谢：）

"；pls-00201标识符必须声明为“；在ASP.NET/C#中

应该；这个

CREATE OR REPLACE
procedure contact_return(
    v_urn IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR )
AS
  sql_statement VARCHAR2(4000) := '        
  SELECT URN,
    FIRSTNAME,
    LASTNAME,
    TITLE,
    MOBILE,
    WORK,
    EMAIL
  FROM CONTACT
  WHERE urn = NVL(:1,v_contact_urn)';
BEGIN
  OPEN p_cursor FOR sql_statement USING v_urn;

是

CREATE OR REPLACE
procedure contact_return(
    v_urn IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR )
AS
BEGIN
  DECLARE sql_statement VARCHAR2(4000) := '';        
  SELECT URN,
    FIRSTNAME,
    LASTNAME,
    TITLE,
    MOBILE,
    WORK,
    EMAIL
  FROM CONTACT
  WHERE urn = NVL(:1,v_contact_urn)';
  OPEN p_cursor FOR sql_statement USING v_urn;
END;

要调用包含类型为SYS_REFCURSOR的OUT参数的Oracle存储过程，需要使用ODP.NET。
模板解决方案如下：

using Oracle.DataAccess;
using Oracle.DataAccess.Client;
public DataTable Call_contact_return(string v_urn_value)
            {
                using (OracleConnection cn = new OracleConnection(DatabaseHelper.GetConnectionString()))
                {
                    OracleDataAdapter da = new OracleDataAdapter();
                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = cn;
                    cmd.InitialLONGFetchSize = 1000;
                    cmd.CommandText = DatabaseHelper.GetDBOwner() + "contact_return";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("v_urn", OracleDbType.Char).Value = v_urn_value;                    
                    cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                    da.SelectCommand = cmd;
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
相关文章：