CODEWITHSUNDEEP
sqlformsoracle-databasevariablesbind
mysticfalls
mysticfalls

Reputation: 455

Error 49 bad bind variable oracle forms

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.

Upvotes: 5

Views: 20701

Answers (4)

Lavi
Lavi

Reputation: 1

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 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.

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7912

For a simple answer-

In Oracle forms

 :S_ORD.payment_type

would mean that you have a DATA_BLOCK named S_ORD which has an element (probably a TEXT ITEM) called 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 SQL or PL/SQL. As a solution, you can DECLARE a CURSOR on the tableS_ORD as

CURSOR c_pymnt_type IS
 SELECT payment_type
   FROM S_ORD;

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

Upvotes: 1

APC
APC

Reputation: 146239

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.

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).

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.

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).

Upvotes: 0

Rene
Rene

Reputation: 10541

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

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

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.

Upvotes: 2

Related Questions