Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Using data from Collection list

    Hi,
    I have a procedure which takes a list of values from expns_tab pl/sql table and e_act_cd from the list as defined below. I need to check if the e_act_cd is in the list then perform few operations based on that.

    But I'm a little confused on how I can select from the v_ac_tab. Any ideas?
    The place where I'm confused is in bold below.

    PROCEDURE p_exp_amt_by_acct_cd (
    expns_tab IN expns_list
    -- acct_cd_tab in acct_cd_list
    )

    TYPE ac_list IS TABLE OF varchar2 (10);
    v_ac_tab ac_list := ac_list ('11', '22','33', '44');


    FOR rec IN (SELECT e_act_cd, NVL (SUM (amt), 0)
    FROM TABLE (CAST (expns_tab AS expns_list))
    WHERE cct = 'aaa'
    AND e_act_cd IN (select * from TABLE( CAST (v_ac_tab AS ac_list)))
    GROUP BY e_act_cd)
    loop.......
    Last edited by nandinir; 09-04-08 at 12:28.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That TYPE needs to be defined in the database if you want to select from it:

    Code:
    SQL> create TYPE ac_list IS TABLE OF varchar2 (10);
      2  /
    
    Type created.

Posting Permissions

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