Results 1 to 2 of 2

Thread: array usage

  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: array usage

    Hi All

    I have a requirement in plsql which is like this


    I have a particular dept no as my in parameter to the plsql stored proc.


    depending on the deptno passed i query a table called account_details
    for a column called account_status.

    this account_status colmn can have 2 values enable or disable

    now for the particular dept passed i want to retrieve the account_status column(only enable) ideally in an array

    I would like to know which is the best collection type to use in this case.


    now from this array i want to set the values back in the table to disable

    after the processing is over i want to enable back the users which i had disabled from the array i.e set the value bak to enable in the table

    ie i want to temporarily disable the enabled users & set them back to enabled state .

    but there are certain entries in the table which are already in disabled state , they should not be enabled in any case

    how can i take care of this


    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: array usage

    You should look at "BULK COLLECT" and "FORALL" with a nested table colection:

    Code:
    DECLARE
      ...
      TYPE account_tab_type IS TABLE OF account.id%TYPE;
      account_tab account_tab_type;
      ...
    BEGIN
      SELECT id
      BULK COLLECT INTO account_tab
      FROM account
      WHERE dept_id = p_dept_id
      AND status = 'ENABLED';
    
      ...
    
      FORALL i IN 1..account_tab.COUNT
        UPDATE account
        SET status='DISABLED'
        WHERE id = account_tab(i);
    
      ...

Posting Permissions

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