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!

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

Discussion in 'SQL PL/SQL' started by neel134, Dec 2, 2010.

  1. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    For your first 'problem':

    Code (SQL):
     
    DECLARE
       TYPE EmpCurTyp IS REF CURSOR;
       emp_cv   EmpCurTyp;
       v_ename VARCHAR2(15);
       v_sal   NUMBER := 1000;
       TABLE_NAME VARCHAR2(30) := 'employees';
    BEGIN
       OPEN emp_cv FOR 'SELECT last_name, salary FROM ' || TABLE_NAME ||
          ' WHERE salary > :s' USING v_sal;
       CLOSE emp_cv;
    END;
    /
     
    Notice the dynamic query in the open statement.A similar solution exists for your second problem; simply use a dynamic statement for the cursor that retrieves the distinct values then populate the array with the returned elements.
     
    kiran.marla likes this.
  3. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    thanks David.

    Neel