Home » Developer & Programmer » Forms » How to store multiple values in a variable & display it in its field in Forms 6i??
How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605980] Fri, 17 January 2014 01:30 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

For that i put in the following code for trigger KEY-NEXT-ITEM for Cust_Code:
DECLARE
  FD_NO NUMBER;
  FD_AMT NUMBER;
  OPN_DT DATE;
  MAT_DT DATE;
BEGIN
  SELECT ACCT_FD_NO, AMOUNT, ACCT_OPN_DT, MATURITY_DATE INTO FD_NO, FD_AMT, OPN_DT, MAT_DT
  FROM KEC_FDACCT_MSTR WHERE ACCT_CUST_CODE = :CUST_CODE;
 
  :ACCT_FD_NO := FD_NO;
  :FD_AMOUNT := FD_AMT;
  :ACCT_OPN_DATE:= OPN_DT;
  :MATURITY_DATE:= MAT_DT;
END;



This works fine if a Cust_Code has only one Acct_Fd_No

But when Cust_Code has more than one Acct_Fd_No , it throws an error :FRM-40735: KEY-NEXT-ITEM trigger raised unhandled exception ORA-01422.
I know the error because it returns more than one row.
So how can i store multiple rows in one variable ??
I just want to display all the Acct_Fd_No's in the field , when i enter the Cust_Code , for that particular Cust_Code like below.
For example, Cust_Code=124 has 3 Acct_Fd_No (33738, 33765, 33872).

Thank you
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605981 is a reply to message #605980] Fri, 17 January 2014 01:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In a cursor loop, concatenate ACCT_FD_NO values into a single form item. Make sure that items are large enough to accept (possibly) long values, or use SUBSTR to cut the result short.
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605982 is a reply to message #605981] Fri, 17 January 2014 01:39 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,

Could please give sample example please?

Thank You
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605983 is a reply to message #605982] Fri, 17 January 2014 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
These are DEPTNO = 20 employees:
SQL> select ename from emp where deptno = 20 order by ename;

ENAME
----------
ADAMS
FORD
JONES
SCOTT
SMITH


In the following example, L_ENAME variable acts as if it was a form item whose length is 15 characters. If you have a look at the above output, you'll notice that these names - when concatenated with a comma in between - exceed 15 characters. Therefore, I'll use SUBSTR to make sure that the result fits into the variable. LTRIM is here to remove a leading comma character.
SQL> DECLARE
  2     l_ename   VARCHAR2 (15);
  3  BEGIN
  4     FOR cur_r IN (  SELECT ename
  5                       FROM emp
  6                      WHERE deptno = 20
  7                   ORDER BY ename)
  8     LOOP
  9        l_ename := LTRIM (SUBSTR (l_ename || ', ' || cur_r.ename, 1, 15), ', ');
 10     END LOOP;
 11
 12     DBMS_OUTPUT.put_line ('DEPTNO 20 employees: ' || l_ename);
 13  END;
 14  /
DEPTNO 20 employees: ADAMS, FORD, JO

PL/SQL procedure successfully completed.

SQL>
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605984 is a reply to message #605983] Fri, 17 January 2014 02:03 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Woh! Thank You Littlefoot Good solution Smile
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605989 is a reply to message #605983] Fri, 17 January 2014 03:25 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,

Quote:

In a cursor loop, concatenate ACCT_FD_NO values into a single form item. Make sure that items are large enough to accept (possibly) long values, or use SUBSTR to cut the result short.


I tried with the code below: This works perfect in Toad for Oracle.
DECLARE 
    l_fd VARCHAR2 (1000); 
BEGIN 
    FOR cur_r IN (SELECT acct_fd_no 
                  FROM   kec_fdacct_mstr 
                  WHERE  staus = 'E' 
                  AND acct_cust_code = '336' 
                  ORDER  BY acct_fd_no) LOOP 
        l_fd := cur_r.acct_fd_no; 

        dbms_output.Put_line ('FD336:  ' || l_fd); 
    END LOOP; 
END; 


The Result is :

FD336: 330470

FD336: 330472

FD336: 331767

FD336: 332023

FD336: 332024

But when i tried the same in Oracle Forms 6i its working but not properly & also i get error:

I put in the below code for trigger KEY-NEXT-ITEM for Cust_Code:
DECLARE 
    l_fd VARCHAR2 (1000); 
BEGIN 
    FOR cur_r IN (SELECT acct_fd_no 
                  FROM   kec_fdacct_mstr 
                  WHERE  staus = 'E' 
                  AND acct_cust_code = :CUST_CODE) LOOP 
        l_fd := cur_r.acct_fd_no; 

        :ACCT_FD_NO := l_fd; 

        next_record; 
    END LOOP; 
END; 

When I enter the Cust_Code in the field & press tab or next_item then,

Only two values are getting displayed in the Acct_Fd_No field line item instead of 5 values in 5 line items like below.

330470

331767

And I'm also getting error : FRM-40202:Field must be entered.
What should i change in my code such that every single value gets displayed in each line item of Acct_Fd_No??

Please suggest me?

[Updated on: Fri, 17 January 2014 03:25]

Report message to a moderator

Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605991 is a reply to message #605989] Fri, 17 January 2014 03:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This message looks like the third ACCT_FD_NO is null. What happens when you modify your code to
    FOR cur_r IN (SELECT acct_fd_no 
                  FROM   kec_fdacct_mstr 
                  WHERE  staus = 'E' 
                  AND acct_cust_code = :CUST_CODE
                  AND acct_fd_no IS NOT NULL            --> add this line
                 ) LOOP 
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605995 is a reply to message #605991] Fri, 17 January 2014 04:17 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
This message looks like the third ACCT_FD_NO is null. What happens when you modify your code to

Sorry for i am late reply to you..

The third ACCT_FD_NO is not null. because it a unique constraint column in Database.
The error (FRM-40202:Field must be entered) of the field being null is shown for Cust_Code.
Because the Cust_Code field becomes blank immediately.

Anyways the code which you suggested in your second reply is right.
The only problem is , i'm not able to display the captured value in next line items.
Its getting displayed in the same line item.
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605996 is a reply to message #605995] Fri, 17 January 2014 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If CUST_CODE requires a value, why don't you add it to your code?
BEGIN 
    FOR cur_r IN (SELECT acct_fd_no,
                         cust_code                     --> here ...
                  FROM   kec_fdacct_mstr 
                  WHERE  staus = 'E' 
                  AND acct_cust_code = :CUST_CODE
                 ) 
    LOOP 
       :ACCT_FD_NO := cur_r.acct_fd_no;
       :CUST_CODE  := cur_r.cust_code;                 --> ... and here

        next_record; 
    END LOOP; 
END; 
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605998 is a reply to message #605996] Fri, 17 January 2014 04:55 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,


I have already added that in my code.
{ FOR cur_r IN (SELECT acct_fd_no FROM kec_fdacct_mstr WHERE staus = 'E' AND acct_cust_code = :CUST_CODE) }
That is all fine.
But the problem is i'm not able to display the captured value in next consecutive line items.
It is getting displayed in first & second line item only.
The first value gets displayed in first line item, then the cursor moves from first line item to second line item,
the second value gets displayed in the second line item, (this is because of NEXT_RECORD in my code)
later all the values are getting displayed in the second line item only.
I want it to display one below the other.
The problem is the cursor is not moving to third line item after the second line item.
So the remaining values gets displayed in the second line item only.
So do you have any idea how to do it?? Please let me know.

Thank You.. Smile
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #605999 is a reply to message #605998] Fri, 17 January 2014 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
For some reason, NEXT_RECORD doesn't go into the third record. I don't know why. Try to include MESSAGE call into the loop which would display values returned by cursor FOR loop - maybe you'll find some useful information.

Also, include ORDER BY into the SELECT statement and run the same SELECT in SQL*Plus so that you could compare values returned in SQL*Plus and your form.
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606002 is a reply to message #605999] Fri, 17 January 2014 05:26 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
I have already CHECKED by adding mesaage call , so only , i could tell that all the values are getting displayed in the second line item only one after another.
FOR CUR_R IN (SELECT  ACCT_FD_NO FROM KEC_FDACCT_MSTR 
              WHERE   STAUS='E' 
              AND ACCT_CUST_CODE=:CUST_CODE  
              ORDER BY ACCT_FD_NO)
       LOOP
          :ACCT_FD_NO :=CUR_R.ACCT_FD_NO;
          MESSAGE('FD: '|| CUR_R.ACCT_FD_NO);
          MESSAGE(' ');
          NEXT_RECORD;
       END LOOP;


But i'm not getting to know how to make the cursor move from one line item to next line item i.e from second line item to third line item & third line item to fourth line item & so on..

Please suggest me.
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606003 is a reply to message #606002] Fri, 17 January 2014 05:29 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NEXT_RECORD should do it ...

What happens if you modify it to
loop
  ...
  create_record;        --> instead of next_record
end loop;
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606005 is a reply to message #606003] Fri, 17 January 2014 05:44 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

What happens if you modify it to
loop
...
create_record; --> instead of next_record
end loop;

No, its working in the same way as before for next_record
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606006 is a reply to message #606005] Fri, 17 January 2014 05:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That just doesn't make sense. Try to recompile the form (Ctrl + Shift + K) and then run it again.
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606008 is a reply to message #606006] Fri, 17 January 2014 06:21 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
That just doesn't make sense. Try to recompile the form (Ctrl + Shift + K) and then run it again.


When ever i change my code, i'll always compile my code & then only run.
Still i compiles again now & tried, its working in the same way. No change.

Thank You Very Much to help me more & gave a replied with patience.. Smile
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606123 is a reply to message #605980] Mon, 20 January 2014 01:36 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,

At item-level (CUST_CODE), trigger:KEY-NEXT-ITEM.
BEGIN
FOR CUR_R IN (SELECT  ACCT_FD_NO FROM KEC_FDACCT_MSTR
              WHERE   STAUS='E' 
              AND ACCT_CUST_CODE=:CUST_CODE 
              ORDER BY ACCT_FD_NO)
LOOP
   :ACCT_FD_NO :=CUR_R.ACCT_FD_NO;
   NEXT_RECORD;
END LOOP;
END;


After i navigate from CUST_CODE field, all the ACCT_FD_NO values gets displayed one below the other.
But the CUST_CODE field becomes blank immediately.

I have put the items in detail in my question.

Actually in form, for item CUST_CODE , in property palette , Number of items displayed =1.
and for item ACCT_FD_NO , in property palette , Number of items displayed =20.

So when the cursor is in first record (first line item) , it shows value for CUST_CODE.
When the cursor goes to second or third or fourth record, CUST_CODE becomes blank.
But i want to retain the CUST_CODE value for all the records.
1 customer (CUST_CODE) has multiple accounts (ACCT_FD_NO).

Suggest me Please?

Thank You
Re: How to store multiple values in a variable & display it in its field in Forms 6i?? [message #606126 is a reply to message #606123] Mon, 20 January 2014 01:48 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that you are trying to simulate a master-detail relationship, with CUST_CODE being a master and ACCT_FD_NO being its details. Why didn't you create such a layout, then? It would be easier to maintain and - what a great news! - you could use built-in Forms' querying mechanism.

Now, you have different number of displayed items in the same block; NEXT_RECORD navigates to a new record and all items in that block are empty. Your code fills ACCT_FD_NO, but the rest (which includes CUST_CODE remains empty). If you insist on this "solution", move CUST_CODE into another block.

If I were you, I'd rewrite it. This is a mess (which doesn't work, above all).
Previous Topic: Cannot make more than 1 Db connect
Next Topic: Need help while inserting record in forms
Goto Forum:
  


Current Time: Fri Mar 29 04:04:03 CDT 2024