Error 49 incorrect Oracle variable bindings

Question

I would like to ask about 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 am new to oracle forms, so not sure if I have a missing setup or something else. The S_ORD table exists and has a payment_type column, which consists of the values ​​"CREDIT" and "CASH". Thanks.

Answer 1

Oracle Forms does not allow the use of bind variables when code is in a library or menu.

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

You can indirectly reference the NAME_IN and COPY elements of the Subroutine. The NAME_IN function returns the contents of the specified variable or element. Use the NAME_IN function to get the value of an element without directly accessing the element. The following statements are equivalent:

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

The return value is always a character string. To use NAME_IN for DATE or NUMBER, convert the string to the desired data type using 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:

. Function NAME_IN cannot return the contents of a global or local variable.

· In PL / SQL triggers that will be executed in I / O mode, you should use NAME_IN instead of the usual variable binding record to access the value in the data block. (This is because the end user can inject relational operators into the element, creating a value that is not in the form that PL / SQL can handle.)

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

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

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

/ * put the value "blacksmith" in the element whose name is stored in ref_item * / Copy ('blacksmith', Name_In ('control.ref_item'));

Why use indirect links
Linking to objects indirectly allows you to write a more general, reusable code. Using variables instead of the names of the actual elements, you can write a routine that can work on any element whose name is assigned to the specified variable. Also, the use of an indirect reference is required if you refer to the value of form bind variable (item, parameter, global variable) in PL / SQL, which you write in the library or in the menu module. Since libraries, menus, and forms are separate application modules, you cannot directly refer to the value of a form element in a command or library in the Procedure menu.

Answer 2

Obviously, it’s very difficult for us to understand what you did, because we cannot see your code, and you did not provide us a lot of information. So this is an assumption.

S_ORD is a block in your form based (hopefully) on the S_ORD table. Your published fragment works in some kind of trigger, perhaps POST-QUERY or WHEN-VALIDATE-ITEM, although this does not really matter, and it is assumed that the PAYMENT_TYPE field will be filled on this block.

That error message tells you that this S_ORD block does not have a PAYMENT_TYPE field. (He says “bind a variable” because the colon designates bind variables, and we can also refer to things like global variables in the same way).

Why does the field not exist? Or:

• There is a PAYMENT_TYPE column in the table and you did not select it when creating the block; or
• There is no PAYMENT_TYPE column in your table;
• Your data block is not called S_ORD.

In the first case, you need to go to the block properties editor and add a column. In the second case, you need to add an element without a base table to your block. Find out how here . If you called the data block something other than the table name (and there may be good reasons for this), you need to use the nam ein block to call, not the table name).

Answer 3

For a simple answer -

In Oracle Forms

  :S_ORD.payment_type 

means that you have a DATA_BLOCK named S_ORD that has an element (possibly TEXT ITEM ) called PAYMENT_TYPE .

Therefore, if there is no such data block and element in the Oracle form, it will continue to give an error. The bind variable using a colon : does not work as it does in SQL or PL/SQL . As a solution, you can DECLARE a CURSOR in table S_ORD as

 CURSOR c_pymnt_type IS SELECT payment_type FROM S_ORD; 

and then OPEN , FETCH and CLOSE cursor inside the procedure.

Answer 4

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

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

This is for oracle forms 10.