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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
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 #605983 is a reply to message #605982] |
Fri, 17 January 2014 01:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 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 #605989 is a reply to message #605983] |
Fri, 17 January 2014 03:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
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 #606123 is a reply to message #605980] |
Mon, 20 January 2014 01:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 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).
|
|
|