Home » SQL & PL/SQL » SQL & PL/SQL » Fetching data dynamically in Collections (Forms 6i, Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Fetching data dynamically in Collections [message #673614] Tue, 27 November 2018 05:54 Go to next message
chat2raj.s
Messages: 135
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 #673615 is a reply to message #673614] Tue, 27 November 2018 07:21 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does this question have anything to do with forms?
Re: Fetching data dynamically in Collections [message #673616 is a reply to message #673615] Tue, 27 November 2018 07:46 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
Am trying all this from the forms 6i only
Re: Fetching data dynamically in Collections [message #673617 is a reply to message #673614] Tue, 27 November 2018 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Waiting for your feedback in your previous topics...

Re: Fetching data dynamically in Collections [message #673618 is a reply to message #673617] Tue, 27 November 2018 08:09 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
Topic : Check for status of a computer in the network , was addressed and closed. I dont think if any others is still pending for my feedback.
Re: Fetching data dynamically in Collections [message #673619 is a reply to message #673618] Tue, 27 November 2018 08:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
The code you've posted is pure PL/SQL with no references to anything forms.
So how is forms involved?
Re: Fetching data dynamically in Collections [message #673620 is a reply to message #673618] Tue, 27 November 2018 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

chat2raj.s wrote on Tue, 27 November 2018 15:09
Topic : Check for status of a computer in the network , was addressed and closed. I dont think if any others is still pending for my feedback.
Really? My last post (which is the last one in the topic) never received a feedback.

This one receives answers from John and Bill who never received feedback.

This one receives a solution from msol25 but you never fed back and thanked him.

And the one that ends with a post from you that says "I will go through the documentation link and do changes on my mv and feedback.", I fail to find the promised feedback.

You should post the final solution to your problem to help people who will reach your topics.

Re: Fetching data dynamically in Collections [message #673628 is a reply to message #673620] Wed, 28 November 2018 00:04 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
My apologies to Michel. I have updated my remarks on those threads.

Cookiemonster, Should i close this thread and create a new one under sql/plsql or any other way.
Re: Fetching data dynamically in Collections [message #673631 is a reply to message #673628] Wed, 28 November 2018 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to just reply to his question, is this or no relater to Forms and how?

And are you really sure your database is 11.2?
http://www.orafaq.com/forum/mv/msg/205279/673627/#msg_673627

Re: Fetching data dynamically in Collections [message #673635 is a reply to message #673631] Wed, 28 November 2018 01:43 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
This particular message is not related to Forms and should have been posted under pl/sql section.
Re: Fetching data dynamically in Collections [message #673637 is a reply to message #673635] Wed, 28 November 2018 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
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 messageGo to next message
chat2raj.s
Messages: 135
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 #673651 is a reply to message #673650] Wed, 28 November 2018 06:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
RECORD is strictly PL/SQL type and can't be used in SQL. Use OBJECT type instead.

SY.
Re: Fetching data dynamically in Collections [message #673652 is a reply to message #673651] Wed, 28 November 2018 07:24 Go to previous messageGo to next message
chat2raj.s
Messages: 135
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 #673653 is a reply to message #673652] Wed, 28 November 2018 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK you are now in my kill file.

Re: Fetching data dynamically in Collections [message #673654 is a reply to message #673652] Wed, 28 November 2018 07:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then you should post the question in forms forum.

SY.
Re: Fetching data dynamically in Collections [message #673661 is a reply to message #673654] Wed, 28 November 2018 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
He did - it got moved here because it wasn't obvious how forms was involved.
And it still isn't obvious, there's nothing in that description that explains how or why forms needs to be involved.

[Updated on: Wed, 28 November 2018 09:21]

Report message to a moderator

Re: Fetching data dynamically in Collections [message #673667 is a reply to message #673661] Wed, 28 November 2018 23:09 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
I am using collections for first time and i didn't expect it to have some restrictions in the forms. Still i posted it under the forms as i have mentioned in the previous message already.
Re: Fetching data dynamically in Collections [message #673669 is a reply to message #673667] Thu, 29 November 2018 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to explain why forms is involved.
You still haven't done that.
You're not going to get useful answers until you do.
Re: Fetching data dynamically in Collections [message #673670 is a reply to message #673669] Thu, 29 November 2018 03:58 Go to previous messageGo to next message
chat2raj.s
Messages: 135
Registered: October 2010
Location: Chennai, India
Senior Member
The end user of this application needs an interface to browse for the excel sheet that needs to be uploaded. This information is used to update the Pricelist. So iam using forms 6i to develop this interface.
Re: Fetching data dynamically in Collections [message #673671 is a reply to message #673670] Thu, 29 November 2018 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you want the form to have a dynamic number of columns as well?
Re: Fetching data dynamically in Collections [message #673672 is a reply to message #673671] Thu, 29 November 2018 04:56 Go to previous message
chat2raj.s
Messages: 135
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
Previous Topic: Display Time
Next Topic: Charater set
Goto Forum:
  


Current Time: Fri Aug 07 23:18:17 CDT 2020