Error 49 bad bind variable oracle forms

Error 49 bad bind variable oracle forms

提问by mysticfalls

I would like to ask regarding this error...

我想问一下这个错误...

   Error 49 at line 5, column 6
       bad bind variable 'S_ORD.payment_type'

Here is the code:

这是代码：

    DECLARE
     N NUMBER;
      v_credit S_CUSTOMER.credit_rating%type;
    BEGIN
      IF :S_ORD.payment_type = 'CREDIT' THEN
        SELECT credit_rating
        INTO v_credit
        FROM S_CUSTOMER
      WHERE :S_ORD.customer_id = id;
      IF v_credit NOT IN ('GOOD', 'EXCELLENT') THEN
            :S_ORD.payment_type:= 'CASH';
          n:=SHOW_ALERT('Payment_Type_Alert');
      END IF;
     END IF;
   END;

I'm new to oracle forms so I'm not sure if I have a missing setup or anything. S_ORD table exist and has a column payment_type, which consists of 'CREDIT' and 'CASH' value. Thank you.

我是 oracle 表单的新手，所以我不确定我是否缺少设置或任何东西。S_ORD 表存在并且有一个payment_type 列，它由'CREDIT' 和'CASH' 值组成。谢谢你。

回答by Rene

Oracle Forms doesn't allow the use of bind variables when the code is in a library or a menu.

当代码位于库或菜单中时，Oracle Forms 不允许使用绑定变量。

Here is a quote from the Oracle Forms (6i) help:

这是 Oracle Forms (6i) 帮助中的引述：

You can reference items indirectly with the NAME_IN and COPY built-in subprograms. The NAME_IN function returns the contents of an indicated variable or item. Use the NAME_IN function to get the value of an item without referring to the item directly. The following statements are equivalent:

IF :emp.ename = 'smith' -- direct reference
IF NAME_IN('emp.ename') = 'smith' -- indirect reference

The return value is always a character string. To use NAME_IN for a DATE or NUMBER item, convert the string to the desired data type with the appropriate conversion function:

date_var := TO_DATE(Name_In('order.date_item'));
num_var := TO_NUMBER(Name_In('order.number_item'));

Notes on NAME_IN:

· The NAME_IN function cannot return the contents of a global or local variable.

· In PL/SQL triggers that will be executed in enter-query mode, you must use NAME_IN rather than normal bind-variable notation to access values in the data-block. (This is because the end-user might type relational operators into the item, producing a value which is not in a form that can be processed by PL/SQL.)

The COPY Procedure The COPY procedure assigns an indicated value to an indicated variable or item. Unlike standard PL/SQL assignment, however, using the COPY procedure allows you to indirectly reference the item whose value is being set:

:emp.ename := 'smith'; -- direct reference Copy('smith','emp.ename'); -- indirect reference

COPY can be used with the NAME_IN function to assign a value to an item whose name is stored in a reference variable or item:

/* put value 'smith' in item whose name is stored in ref_item */ Copy('smith',Name_In('control.ref_item'));

Why Use Indirect Reference
Referencing items indirectly allows you to write more generic, reusable code. By using variables in place of actual item names, you can write a subprogram that can operate on any item whose name has been assigned to the indicated variable. Also, using indirect reference is mandatory when you refer to the value of a form bind variable (item, parameter, global variable) in PL/SQL that you write in a library or a menu module. Because libraries, menus, and forms are separate application modules, you cannot refer directly to the value of a form item in a menu-item command or library procedure.

您可以使用 NAME_IN 和 COPY 内置子程序间接引用项目。NAME_IN 函数返回指定变量或项目的内容。使用 NAME_IN 函数获取项目的值，而不直接引用项目。以下语句是等效的：

IF :emp.ename = 'smith' -- 直接引用
IF NAME_IN('emp.ename') = 'smith' -- 间接引用

返回值始终是一个字符串。要将 NAME_IN 用于 DATE 或 NUMBER 项目，请使用适当的转换函数将字符串转换为所需的数据类型：

date_var := TO_DATE(Name_In('order.date_item'));
num_var := TO_NUMBER(Name_In('order.number_item'));

NAME_IN 的注意事项：

· NAME_IN 函数不能返回全局或局部变量的内容。

· 在将在输入查询模式下执行的 PL/SQL 触发器中，您必须使用 NAME_IN 而不是普通的绑定变量符号来访问数据块中的值。（这是因为最终用户可能会在项目中键入关系运算符，从而产生一个 PL/SQL 无法处理的形式的值。）

COPY 程序 COPY 程序为指定的变量或项目分配指定的值。然而，与标准的 PL/SQL 赋值不同，使用 COPY 过程允许您间接引用其值被设置的项目：

:emp.ename := '史密斯'; -- 直接引用 Copy('smith','emp.ename'); -- 间接引用

COPY 可与 NAME_IN 函数一起使用，为名称存储在引用变量或项目中的项目赋值：

/* 将值 'smith' 放在名称存储在 ref_item 中的项目中 */ Copy('smith',Name_In('control.ref_item'));

为什么使用间接引用 间接引用
项允许您编写更通用、可重用的代码。通过使用变量代替实际项目名称，您可以编写一个子程序，该子程序可以对名称已分配给指定变量的任何项目进行操作。此外，当您在库或菜单模块中编写的 PL/SQL 中引用表单绑定变量（项目、参数、全局变量）的值时，必须使用间接引用。因为库、菜单和窗体是独立的应用程序模块，所以不能直接引用菜单项命令或库过程中窗体项的值。

回答by AnBisw

For a simple answer-

对于一个简单的答案-

In Oracle forms

在 Oracle 表格中

 :S_ORD.payment_type

would mean that you have a DATA_BLOCKnamed S_ORDwhich has an element(probably a TEXT ITEM) called PAYMENT_TYPE.

意味着你有一个DATA_BLOCKnamedS_ORD有一个element（可能是一个TEXT ITEM）叫PAYMENT_TYPE.

So unless there is any such Data block and element on the Oracle Form it will continue to give error. The bind variable using colon :does not work the same way as in SQLor PL/SQL. As a solution, you can DECLAREa CURSORon the tableS_ORDas

因此，除非 Oracle 表单上有任何此类数据块和元素，否则它将继续出错。使用冒号的绑定变量:与 inSQL或 中的工作方式不同PL/SQL。作为一个解决方案，你可以DECLARE一个CURSOR在桌子上S_ORD为

CURSOR c_pymnt_type IS
 SELECT payment_type
   FROM S_ORD;

and then OPEN, FETCHand CLOSEthe cursor inside the procedure.

然后OPEN，FETCH以及CLOSE程序内的光标。

回答by Lavi

I noticed that you are trying to follow the Oracle tutorial from http://www.oracle.com/webfolder/technetwork/tutorials/obe/forms/11g/formsmenuscreate/formsmenuscreate.htm

我注意到您正在尝试遵循http://www.oracle.com/webfolder/technetwork/tutorials/obe/forms/11g/formsmenuscreate/formsmenuscreate.htm 中的 Oracle 教程

The problem can be solved easily by going to each of the items in the data blocks and changing the item type in the property palette to the next one on the list after the one selected.

通过转到数据块中的每个项目并将属性选项板中的项目类型更改为选定项目后列表中的下一个项目，可以轻松解决该问题。

This is for oracle forms 10.

这适用于 Oracle 表格 10。

回答by APC

Obviously it is very hard to for us to know what it is you've done because we cannot see your code and you javen't given us much information. So this is a guess.

显然，我们很难知道你做了什么，因为我们看不到你的代码，而且你没有给我们太多信息。所以这是一个猜测。

S_ORD is a block in your form, based (I hope) on a table called S_ORD. Your posted snippet is running in some trigger, perhaps POST-QUERY or WHEN-VALIDATE-ITEM although it doesn't really matter, and is supposed to populate a field called PAYMENT_TYPE on that block.

S_ORD 是您表单中的一个块，基于（我希望）一个名为 S_ORD 的表。您发布的代码段在某个触发器中运行，可能是 POST-QUERY 或 WHEN-VALIDATE-ITEM，尽管它并不重要，并且应该在该块上填充一个名为 PAYMENT_TYPE 的字段。

What the error message is telling you is that block S_ORD doesn't have a field called PAYMENT_TYPE. (It says "bind variable" because the colon notation denotes bind variables and we can also reference things such as global variables in the same fashion).

错误消息告诉您的是，块 S_ORD 没有名为 PAYMENT_TYPE 的字段。（它说“绑定变量”是因为冒号表示绑定变量，我们也可以以相同的方式引用诸如全局变量之类的东西）。

Why doesn't the field exist? Either:

为什么该字段不存在？任何一个：

1. Your table has a column called PAYMENT_TYPE and you haven't selected it when you created the block; or
2. Your table doesn't have a column called PAYMENT_TYPE;
3. Your data block isn't called S_ORD.

1. 您的表有一个名为 PAYMENT_TYPE 的列，并且您在创建块时没有选择它；或者
2. 您的表没有名为 PAYMENT_TYPE 的列；
3. 您的数据块不称为 S_ORD。

In the first case you need to go into the block property editor and add the column. In the second case you need to add a non-base table item to your block. Find out how here. If you have named the data block something other than the table name (and there can be good reasons for doing so) then you need to use the block nam ein the call, not the table name).

在第一种情况下，您需要进入块属性编辑器并添加列。在第二种情况下，您需要向块中添加一个非基表项。 了解如何在这里。如果您将数据块命名为表名以外的其他名称（并且这样做可能有充分的理由），那么您需要在调用中使用块名，而不是表名）。