Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: How to fetch columns of a table in a cursor whose table name is a variable

    Hi,

    I am facing a new problem and would appreciate some help.
    I have a procedure, to which we are passing a table name as a variable.
    Depending on this table name passed, we have to select distinct values of a column in that table into a cursor.

    select distinct column1 from table_name;
    But here the table_name is unknown and is a variable. So in the cursor declaration I'll be unable to use it.

    So how to go about this?

    An altenative problem:In the same procedure, where table_name is a variable passed to it, suppose I have found out the number of distinct columns in that table as follows:

    v_query2 := 'SELECT COUNT(*) FROM (SELECT DISTINCT column1 FROM '||table_name||')';
    execute immediate v_query2 into v_dist;


    Now I need to assign each of these distinct column values into the elements of an associative array. I can use the index of the array from 1 to v_dist.

    type test_table is table of varchar2(50) index by binary_integer;
    How do I do this?

    Some tips please!

    Thanks
    Neel

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Depending on this table name passed, we have to select distinct values of a column in that table into a cursor.

    how does code know/decide correct column name to use in EXECUTE IMMEDIATE, since different tables have different column names.
    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.

Posting Permissions

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