Explicit Cursor !! Want list of all columns ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Explicit Cursor !! Want list of all columns ?

  1. #1
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67

    Explicit Cursor !! Want list of all columns ?

    Hi,

    I have a dynamic cursor.

    declare....
    begin..
    open cur_test1 for 'select * from table1;';
    ...

    now in the above scenario i don't know what all columns are available in the table 'table1'.

    At runtime is it possible to know the list of all columns in the cursor and how do i point to those columns for value.

    Please help.
    Regards,
    Surajit K Mitra

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460
    You can check out USER_TAB_COLUMNS
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67

    Explicit Cursor !! Want list of all columns ?

    No, i am not looking at that.
    I will rephrase my problem in a more clear manner.

    Look at the code that i am pasting below.

    Assumption:
    For a given table there is only 1 primary key column.

    Objective:
    Provide table name
    Provide primary key value

    Result: retreive values from the tablename as per the primary key value passed.

    --- Code start here
    create or replace procedure sp_proc_1
    (
    ls_tablename user_tables.table_name%type,
    ls_pkval1 varchar2
    )
    is
    -- Fetch the primary key column
    cursor cur_metadata
    is
    SELECT uc.table_name, ucc.column_name
    FROM user_constraints uc, user_cons_columns ucc
    WHERE uc.constraint_name = ucc.constraint_name
    AND uc.constraint_type = 'P'
    AND uc.table_name = ls_tablename
    ORDER BY ucc.POSITION;

    cursor cur_metadata_1
    is
    SELECT column_name
    FROM user_tab_columns
    WHERE table_name = ls_tablename
    ORDER BY column_id;

    metadata_cur cur_metadata%rowtype;
    metadata_1_cur cur_metadata_1%rowtype;

    TYPE dyna_cursor_def IS REF CURSOR;
    dyna_cursor dyna_cursor_def;

    ls_selectstring_1 varchar2(4000) := 'SELECT ';
    ls_wherestring varchar2(4000) := ' WHERE ';
    ls_finalqry varchar2(4000);
    ll_totcolumns number(1) := 0;

    begin
    open cur_metadata;
    loop
    fetch cur_metadata into metadata_cur;
    exit when cur_metadata%notfound;

    ls_wherestring := ls_wherestring || rtrim(upper(metadata_cur.column_name)) || '=''' || ls_pkval1 || '''' || ' AND ';

    ll_totcolumns := ll_totcolumns + 1;
    dbms_output.put_line('ls_wherestring ' || ls_wherestring);
    end loop;
    close cur_metadata;

    open cur_metadata_1;
    loop
    fetch cur_metadata_1 into metadata_1_cur;
    exit when cur_metadata_1%notfound;
    ls_selectstring_1 := ls_selectstring_1 || rtrim(upper(metadata_1_cur.column_name)) || ',';
    end loop;
    close cur_metadata_1;

    ls_selectstring_1 := substr(ls_selectstring_1, 1, (length(ls_selectstring_1)-1)) || ' FROM ' || ls_tablename ;
    ls_wherestring := substr(ls_wherestring,1,(length(ls_wherestring)-4));

    -- [START] PROBLEM STARTS HERE
    -- Dynamic cursor execution
    open dyna_cursor_def for ls_selectstring_1;
    loop
    fetch dyna_cursor_def into dyna_cursor;
    exit when dyna_cursor_def%notfound;
    dbms_output.put_line('Rownum : ' || dyna_cursor_def%rowcount);
    -- i want to fetch the column name here, so that i can trace the values
    -- but there should not be any hardcoding of table names
    -- the record type variable should be dynamic in nature
    end loop;
    close dyna_cursor_def;
    -- [COMPLETED] PROBLEM ENDS HERE

    ls_selectstring_1 := substr(ls_selectstring_1, 1, (length(ls_selectstring_1)-1)) || ' FROM ' || ls_tablename ;
    ls_wherestring := substr(ls_wherestring,1,(length(ls_wherestring)-4));

    ls_finalqry := ls_selectstring_1 || ls_wherestring;

    dbms_output.put_line('ls_finalqry : ' ||lower(ls_finalqry));

    exception
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(SQLCODE, 'UNKNOWN APPLICATION ERROR - ' || SQLERRM );

    end sp_proc_1;
    /

    EXEC sp_proc_1(ls_tablename=>'TABLENAME',ls_pkval1=>'TEST')
    --- Code ends here

    Look at the block which is mentioned like "[START] PROBLEM STARTS HERE"

    Let me know if anyone can resolve the issue.
    Regards,
    Surajit K Mitra

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think there are some methods of dbms_sql that will let you get that information.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    No Help with DBMS_SQL .. can someone help me in this..
    Regards,
    Surajit K Mitra

  6. #6
    Join Date
    Feb 2005
    Posts
    158
    Check out
    http://asktom.oracle.com/pls/ask/f?p...37336448016847

    A more complex alternative would be to use DBMS_SQL to
    1) PARSE the query
    2) DESCRIBE the result set
    3) DEFINE_COLUMN according to the results from (2)
    4) Loop round with FETCH_ROWS for each row returned (in this case there should be 1 as you are using a primary key)
    4a) Loop round with COLUMN_VALUE for each column in the row

Posting Permissions

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



Click Here to Expand Forum to Full Width