1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query vs cursor into array.

Discussion in 'SQL PL/SQL' started by Kirill, Nov 5, 2015.

  1. Kirill

    Kirill Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Toronto
    OK!

    Have following table with data ( just an example )

    COL1 COL2
    1 2
    2 3
    4 5
    5 6
    11 12
    12 13

    I can receive only one value from either column and I need to get the whole set.
    Number of related rows unknown.

    Q: Is it even possible to have query that will return the whole set - my guess is not.
    I think the looping through and retrieving all the values into array will work better.

    Just need an opinion from the side.

    Thanks!
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Your question isn't really clear so I'm having to make some guesses here. Presumably you are asking how to retrieve the data from a database table into PL/SQL, but since you don't mention PL/SQL at all in your question... perhaps not.

    In PL/SQL, 'arrays' are generally referred to as 'collections' -- and there are several types (Associative arrays, Nested tables, and Varrays). The one I use most often is associative arrays. an associative array stores multiple rows of one 'item' and indexes each row so that you can retrieve it by referencing the index key. Often (even normally) that item is a single variable... and therefore will hold a single column value. However, it is also possible to use a collection in combination with a record. A record can hold multiple different values (with different data types) in a single row. You can therefore create a record that matches the column values that you want to store, and then create an associative array that stores multiple instances (rows) of that record. Between the two, you can load the values of a table into an array (often through a CURSOR FOR LOOP) and then reference the table data in your code.

    The following link provides more details on records and collections.

    https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Define 'the whole set' as it's not clear to me what it is that you're wanting. If the 'whole set' is the entire contents of the table then:

    select * from my_table_of_stuff;

    returns 'the whole set'. You also specify:

    "I can receive only one value from either column and I need to get the whole set."

    Does this mean you want ALL values from the specified column? Again, a simple SQL query does the job:

    select col1 from my_table_of_stuff;

    Voila! The every value stored in COL1 is returned. Change the column name and it works for any column in that table.

    Your problem 'definition' is ambiguous, at best, and confusing at the worst. Please provide an understandable definition to this 'problem' so others can understand what you want, because as it is now I doubt anyone can provide a usable solution.