Fetching data dynamically in Collections [message #673614] |
Tue, 27 November 2018 05:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
I need to use collections as below, but want to print the output dynamically instead of specifying PL01, PL02, PL03...as this is going to be bigger list. If not possible using RECORD type, how can this be achieved using collections.
DECLARE
TYPE RT IS RECORD (ITEM VARCHAR2(20),
PL01 NUMBER,
PL02 NUMBER,
PL03 NUMBER,
PL04 NUMBER,
PL05 NUMBER);
TYPE TT IS TABLE OF RT;
MY_REC RT;
MY_TAB TT := TT();
BEGIN
MY_TAB.EXTEND;
MY_TAB(1).ITEM := 'ABC';
MY_TAB(1).PL01 := '40';
MY_TAB(1).PL02 := '42';
MY_TAB(1).PL03 := '44';
MY_TAB(1).PL04 := '46';
MY_TAB(1).PL05 := '48';
MY_TAB.EXTEND;
MY_TAB(2).ITEM := 'DEF';
MY_TAB(2).PL01 := '60';
MY_TAB(2).PL02 := '62';
MY_TAB(2).PL03 := '64';
MY_TAB(2).PL04 := '66';
MY_TAB(2).PL05 := '68';
FOR I IN 1..2
LOOP
Dbms_Output.PUT_LINE(MY_TAB(I).ITEM||' - '||MY_TAB(I).PL01||' - '||MY_TAB(I).PL02||' - '||
MY_TAB(I).PL03||' - '||MY_TAB(I).PL04||' - '||MY_TAB(I).PL05);
END LOOP;
END;
/
|
|
|
|
|
|
|
|
|
|
|
|
Re: Fetching data dynamically in Collections [message #673637 is a reply to message #673635] |
Wed, 28 November 2018 01:59 ![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) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> DECLARE
2
3 TYPE RT IS RECORD (ITEM VARCHAR2(20),
4 PL sys.odcinumberlist);
5
6 TYPE TT IS TABLE OF RT;
7
8 MY_REC RT;
9 MY_TAB TT := TT();
10
11 BEGIN
12
13 MY_TAB.EXTEND;
14
15 MY_TAB(1).ITEM := 'ABC';
16 MY_TAB(1).PL := sys.odcinumberlist(40,42,44,46,48);
17
18 MY_TAB.EXTEND;
19
20 MY_TAB(2).ITEM := 'DEF';
21 MY_TAB(2).PL := sys.odcinumberlist(60,62,64,66,68);
22
23
24 FOR I IN my_tab.first..my_tab.last
25 LOOP
26 Dbms_Output.PUT(MY_TAB(I).ITEM);
27 for j in my_tab(i).pl.first..my_tab(i).pl.last loop
28 Dbms_Output.PUT(' - '||MY_TAB(I).PL(j));
29 end loop;
30 Dbms_Output.new_line;
31 END LOOP;
32
33 END;
34 /
ABC - 40 - 42 - 44 - 46 - 48
DEF - 60 - 62 - 64 - 66 - 68
PL/SQL procedure successfully completed.
|
|
|
Re: Fetching data dynamically in Collections [message #673650 is a reply to message #673637] |
Wed, 28 November 2018 06:29 ![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) |
chat2raj.s
Messages: 161 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
Since i need to push excel data into the collections from the forms 6i, i am trying to create the TYPE in the database and to access from forms. I tried the above as well as table inside a Record and both had same error.
SQL> CREATE OR REPLACE TYPE PRC_LST IS TABLE OF NUMBER;
2 /
Type created.
SQL> CREATE OR REPLACE TYPE RT IS RECORD (ITEM VARCHAR2(20), PLR PRC_LST);
2 /
Warning: Type created with compilation errors.
SQL> SHOW ERR
Errors for TYPE RT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/12 PLS-00103: Encountered the symbol "RECORD" when expecting one of the following:
array varray table object fixed varying opaque sparse The symbol "object was inserted before "RECORD" to continue.
SQL> SPOOL OFF
|
|
|
|
Re: Fetching data dynamically in Collections [message #673652 is a reply to message #673651] |
Wed, 28 November 2018 07:24 ![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) |
chat2raj.s
Messages: 161 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
Thanks SY, i was able to created as on Object.
I need to explain my Business Scenario, to make clear on what i am trying to achieve out of this.
Business scenario : Having a item price list in an excel sheet as price list code in the first row (each column refers to a price list) and different items in each row. The number of price list and the items will differ every time. Now i need to populate this excel in collections in same format (Table with items in rows and price list code as columns) and use it to update the correct price list.
Forms 6i is not supporting nested tables as well as schema level user defined types.
What other ways to achieve this ?
|
|
|
|
|
|
|
|
|
|
Re: Fetching data dynamically in Collections [message #673672 is a reply to message #673671] |
Thu, 29 November 2018 04:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
chat2raj.s
Messages: 161 Registered: October 2010 Location: Chennai, India
|
Senior Member |
|
|
The interface is just going to help the user to browse the excel file for upload and to initiate the upload process. There are no more fields required in the form itself.
I want to close this thread as of now and shall get back for further help in a new thread.
Thanks
|
|
|