Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    11

    Unanswered: Initializing Collections in ORacle

    Hi Friends,

    Happy new year to all of you

    I have a simple requirement in which I'm declaring a collection of type nested table as follows.

    TYPE dummy IS TABLE OF VARCHAR2(400);

    Now, I have to initialize this nested table with at least 1,00,000 or 0.1 Million records like:

    d1 dummy := dummy('abcd|efgh|ijkl',
    .....
    .....
    .....
    'sdsf|erty|lkiu');

    then

    FOR i in d1.first .. d1.last
    loop
    do some operation;
    end loop;

    While doing this I'm getting the following error:

    PLS-00320:
    the declaration of the type of this expression is incomplete or malformed

    But, when I looked into the cause of this error, it says something else. I think the problem is arising due to lack of memory i.e. oracle is failing to allocate enough space while initializing this huge collection.

    Also I tried:

    d1 dummy;

    d1.EXTEND(100000);

    d1 := dummy (......Initialization of 0.1 million data......);

    Note: It is even failing for 30k records but for 10k, it worked.

    But it also failed with some memory issue. I read somewhere in the oracle documentation that the upper limit of nested table is equivalent to that of the PLS_INTEGER which is very high indeed (around 2.1 * 10^9).

    Please let me know how can I do huge initialization of nested table or is there any alternative for nested table in this case.

    Any swift help to resolve this issue will be of great help.

    Thanks in advance .

    Best Regards,
    Supreeth K
    Last edited by Supreeth K; 01-06-14 at 03:34.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > is there any alternative for nested table in this case.
    do not use nested tables.
    What is advantage of nested table over non-nested table other than complexity & errors?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2013
    Posts
    11
    Hi,

    I can't use any normal table to initialize the data as this is not our requirement.

    This collection will be later referred by a function/procedure to separate each field and insert them into the actual tables.

    Can you please propose an alternative in this scenario?

    Thanks.

    Regards,
    Supreeth K

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can't use any normal table to initialize the data as this is not our requirement.
    I have NEVER seen any business requirement stating the database object type to store data
    This appears to be just a standard homework assignment. for SQL class.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2013
    Posts
    11
    Hi,

    The data is just a pipeline separated string which will be separated and inserted later into the respective tables. There are nearly such 0.1 million records.

    I can create a dummy table and insert all these data into it and later define a cursor on this table to fetch the required data. But this can't be done in our case as we have some privilege issues.

    Regards,
    Supreeth K

Posting Permissions

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