2 dimensional array in pl/sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: 2 dimensional array in pl/sql

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    2 dimensional array in pl/sql

    Have anyone used 2 dimensional array in pl/sql and could you please provide the usage. How to declare and assign the values to the array.

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    It is not possible to do that.
    For some purposes, there are work-arounds, but that depends on
    what you want.
    Please be some more specific.
    Regards
    Ben de Boer

  3. #3
    Join Date
    Nov 2002
    Posts
    170
    OK to be more specific:

    My input table is

    ID | QTY | DATE
    ------------------
    IDx QTx DTx

    The output table should look like

    Week1 | Week2 |........ | Weekx
    --------------------------------------
    IDx|QTx|IDx|QTx| .......|IDx|QTx

    I don't know how I can do it without using 2 dimensional array and don't want to use pro-c.

    I don't expect you to write codes just the approch should be enough if you can think of any other way. The only other way I can think of is creating x number of cursors and then inserting to the table.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by dbasupuser
    and don't want to use pro-c.
    Ugh - why in the world would you?

    So, how many weeks do you have to display?

    Do you have a table that translates dates to weeks?

    What is the ID that you plan to display for each week, since there is an ID for every day?

    When you say 'output table', do you simply mean resultset or do you have an actual table?

    Provide as many details as possible, please.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Feb 2001
    Posts
    180
    Here some hints:
    The output table should look like

    Week1 | Week2 |....... | Weekx
    --------------------------------------
    IDx| QTx | QTx | .......| QTx


    Input
    1 25 W1
    1 30 W2
    1 34 W3
    1 18 W4

    Be aware of the limits of a pl/sql table(binary_integer):
    Its magnitude range is -2**31 .. 2**31

    Say start at 1
    then store Id 1 in the range 1 - 53
    Id 2 in the range 54 - 106
    etc.

    When you suppose you have more room
    then make it easier
    then store Id 1 in the range 1 - 99
    Id 2 in the range 100 - 199
    etc.

    Now you can store the values as
    my_plsql_table(IDx * 100 + weekx) := QTx;

    week := mod(Index,100);
    ID := floor(Index/100);

    If the output-table is a physical table you have to
    map every value to a column anyhow, so you can just do that immediate
    and create a pl/sql-table as a output_table%rowtype
    Regards
    Ben de Boer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width