为什么不使用ODP.NET执行此PL/SQL查询 [英] Why this PL/SQL query doesn't execute using ODP.NET

问题描述

在我的代码中的某个地方生成了一个查询，该查询将使用以下命令执行:

Somewhere in my code there's a query generated which is going to execute using:

command.ExecuteNonQuery();

但是对于以下查询，它将引发此异常:

but for the following query, it throws this exception:

{"ORA-01036: illegal variable name/number"}

奇怪的是，如果我将此查询复制到Toad并设置其参数值，则执行将没有任何问题.

The strange point is that if I copy this query to Toad and set its parameter values, It executes without any problem.

在command.CommandText中设置的我的查询是:

My query which is set in command.CommandText is:

declare vv2 number ; 
BEGIN 
:vv := '';

begin SKY.BUSINESS_OBJECT_PKG.REGISTER(B_TYPE_ID=>:B_a_TYPE_ID, B_ID=>:B_b_ID); end;

INSERT INTO GALAXY.E_CONTENTS(
    CONTENT,
    HASH_CODE,
    FILE_NAME,
    THUMBNAIL,
    ID,
    LOCKED_BY_ID,
    TYPE_ID
)
VALUES 
(
    :P_c_CONTENT,
    :P_d_HASH_CODE,
    :P_e_FILE_NAME,
    :P_f_THUMBNAIL,
    :P_g_ID,
    :P_h_LOCKED_BY_ID,
    :P_i_TYPE_ID
);

begin SKY.BUSINESS_OBJECT_PKG.REGISTER(B_TYPE_ID=>:B_j_TYPE_ID, B_ID=>:B_k_ID); end;

INSERT INTO EVENT_MANAGEMENT.UI_EVENT(
    NOTIFICATION_PORT,
    WORK_ITEM,
    WORK_ITEM_DESC,
    BO_DESC,
    BO_CHANGES_ID,
    SMART_SERVICE_VALUE,
    CONTEXT_ID,
    CONTEXT_TYPE_ID,
    DESCRIPTION,
    EVENT_DATE,
    CREATION_DATE,
    SENDER_ID,
    MACHINE,
    EVENT_CATEGORY,
    REFERENCE_ID,
    EVENT_MODE,
    HANDLED_DATE,
    HISTORY,
    IS_VIEWED,
    IS_ACTIVE,
    ID,
    LOCKED_BY_ID,
    TYPE_ID
)
VALUES 
(
    :P_l_NOTIFICATION_PORT,
    :P_m_WORK_ITEM,
    :P_m_WORK_ITEM_DESC,
    :P_o_BO_DESC,
    :P_p_BO_CHANGES_ID,
    :P_q_SMART_SERVICE_VALUE,
    :P_r_CONTEXT_ID,
    :P_s_CONTEXT_TYPE_ID,
    :P_t_DESCRIPTION,
    :P_u_EVENT_DATE,
    :P_v_CREATION_DATE,
    :P_w_SENDER_ID,
    :P_x_MACHINE,
    :P_y_EVENT_CATEGORY,
    :P_z_REFERENCE_ID,
    :P_aa_EVENT_MODE,
    :P_ab_HANDLED_DATE,
    :P_ac_HISTORY,
    :P_ad_IS_VIEWED,
    :P_ae_IS_ACTIVE,
    :P_af_ID,
    :P_ag_LOCKED_BY_ID,
    :P_ah_TYPE_ID
);

begin SKY.BUSINESS_OBJECT_PKG.REGISTER(B_TYPE_ID=>:B_ai_TYPE_ID, B_ID=>:B_aj_ID); end;

INSERT INTO ACCOUNTING.TRANSACTIONS(
    TITLE,
    LAST_CHANGE_ID,
    EFFECTIVE_DATE,
    CREATE_DATE,
    BO_REFRENCE_ID,
    CREATOR_ID,
    COMPANY_ID,
    VOUCHER_ID,
    TRANSACTION_TYPE_VALUE,
    FISCAL_YEAR_ID,
    TRANSACTION_STATE_VALUE,
    FYTB_ID,
    REVISORY_TRANSACTION_ID,
    BO_TYPE_VALUE,
    TRANSACTION_NUMBER,
    ID,
    LOCKED_BY_ID,
    TYPE_ID
)
VALUES 
(
    :P_ak_TITLE,
    :P_al_LAST_CHANGE_ID,
    :P_am_EFFECTIVE_DATE,
    :P_an_CREATE_DATE,
    :P_ao_BO_REFRENCE_ID,
    :P_ap_CREATOR_ID,
    :P_aq_COMPANY_ID,
    :P_ar_VOUCHER_ID,
    :P_as_TRANSACTION_TYPE_VALUE,
    :P_at_FISCAL_YEAR_ID,
    :P_au_TRANSACTION_STATE_VALUE,
    :P_av_FYTB_ID,
    :P_aw_REVISORY_TRANSACTION_ID,
    :P_ax_BO_TYPE_VALUE,
    :P_ay_TRANSACTION_NUMBER,
    :P_az_ID,
    :P_ba_LOCKED_BY_ID,
    :P_bb_TYPE_ID
);

END;

仅供参考，我正在使用ODP.NET.

FYI, I'm using ODP.NET.

为什么要为此类查询引发此异常?

Why this exception is being thrown for such query?

更新1:在此问题出现之前，我已经遇到过这样的问题，以下提示可以解决这些问题.但是在这种情况下，这些提示不起作用.

Update 1: Before this problem I've confronted such problem before and following hints resolved them. But in this case, these hint's doesn't work.

• 从查询中删除所有"\n"，"\r"，"\t"个字符.
• 重命名名称超过31个字符的所有变量.

• Removing all "\n", "\r", "\t" characters from the query.
• Renaming all variables with a name longer that 31 characters.

推荐答案

您应该发布其余.net代码，尤其是要添加到命令中的参数.无论如何，我都会看到一些潜在的问题:

You should post the rest of your .net code, specifically the parameters you're adding to the command. In any case I see a couple of potential problems:

一个，也许蟾蜍正在做一些特别的事情，但是以下内容在sql-plus窗口中无效:

One, maybe toad is doing something special, but the following is not valid in a sql-plus window:

declare vv2 number ; 
BEGIN 
:vv := '';
end;
/

带有冒号的绑定变量实际上是在sql-plus"var"语句中声明的，而不是在匿名pl-sql块的声明语句中声明的.无论如何，您似乎都没有使用该变量.仅此一个问题就可能成为问题，因为它消除了需要绑定的参数数量.

Bind variables with the colon are actually declared in the sql-plus "var" statement, not in the declare statement of the anonymous pl-sql block. It doesn't look like you're using that variable anyway. This alone could be the problem since it's throwing off the number of parameters that need to be bound.

第二，这可能是一个典型的参数不匹配问题(再次，需要查看您的Parameter.Add调用).参数数量，参数类型，参数顺序等可能不匹配.请参考以前的

Two, this might be a typical parameter missmatch issue (again, need to see your Parameter.Add calls). Could be mismatched number of params, parameter type, parameter order, etc. Please refer to a previous blog post of mine.

最后，我没有尝试在一个语句中执行两个匿名块.也许一次尝试一个就将其排除在外?我也有一个深入地编写了呼叫sql-plus脚本.

Last, I've not tried to execute two anonymous blocks in one statement. Maybe try one at a time just to rule them out? I've also have an in-depth write up of calling sql-plus scripts.

这篇关于为什么不使用ODP.NET执行此PL/SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！