DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: dynamic cursor and pl/sql table

  1. #1
    Ervin Rodriguez Guest

    dynamic cursor and pl/sql table


    Hello there, I have a problem. I am trying to execute a dynamic cursor. I
    was sucessful doing that but the problem is that I am building my cursor
    out of the user_columns_tab table where all my columns are listed for my
    table. The problem is that I know the number of columns in my table which
    is five. Some columns are of number data type and other of varchar2. Since
    I know the number of columns, i can declare a record type variable and store
    all the values. The problem comes when I want to add a new column to my
    table. If i do, then i have to modify my program to accomodate the new column.
    I don't want to do that. I would like my program to dynamically store all
    my columns into memory even if I add new columns in the future. I tried
    to do it with a pl/sql table but the problem is that the columns vary in
    data type. How can I dynamically fetch all the columns in my table into
    memory even when i add new ones in the future? remember, columns have different
    data type. Here is my code which didn't work with table because of the
    different data type. thanks


    DECLARE
    v_cursor VARCHAR2(4000) := 'SELECT';
    TYPE t_RefCur IS REF CURSOR;

    TYPE get_data IS TABLE OF VARCHAR2(2000);
    myData get_data;

    v_ReturnCursor t_RefCur;

    v_count PLS_INTEGER := 1;


    CURSOR get_column IS
    SELECT column_name
    FROM user_tab_columns
    WHERE table_name = 'STD_AE_TBL';
    BEGIN
    FOR rec IN get_column LOOP
    v_cursor := v_cursor || ' ' || rec.column_name|| ',';
    END LOOP;
    v_cursor := substr(v_cursor, 1, length(v_cursor) -1);
    v_cursor := v_cursor || ' from STD_AE_TBL';

    OPEN v_ReturnCursor FOR v_cursor;
    LOOP
    FETCH v_ReturnCursor INTO myDATA(v_count);
    EXIT WHEN v_ReturnCursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(myData(v_count));
    v_count := v_count + 1;
    END LOOP;
    CLOSE v_ReturnCursor;
    END;

  2. #2
    Boris Milrud Guest

    Re: dynamic cursor and pl/sql table


    Ervin,

    Since you want to fetch all the columns, including any added columns in future,
    then it's a perfect case for using * notation. Instead of building the following
    string:

    select column1, column2, ... columnN from myTable;

    why don't you use the simplified syntax:

    select * from myTable;

    That syntax would cover all future table modifications, i.e. added or removed
    columns. That would also simplified you PL/SQL block substantially since
    you don't need to extract columns from user_tab_columns dictionary in order
    to build your SQL string.

    Here is the sample code for 9i version (I am using 9.2.0.1.0), that uses
    Dept table as an example. Keep in mind: I am using bulk fetching (BULK COLLECT
    clause) that loads all the data into the collection in one single step: that's
    much more efficient. It's available for dynamic SQL only in 9i version.

    Here is the code:

    declare

    type tDept is table of Dept%rowtype;
    vDept tDept;

    vTable varchar2(30) := 'Dept';
    vCursor sys_refcursor;

    begin

    open vCursor for ('select * from ' || vTable);
    fetch vCursor bulk collect into vDept;
    close vCursor;

    if vDept.count > 0
    then
    dbms_output.put_line('Elements: ' || to_char(vDept.count));

    for n in vDept.first..vDept.last
    loop
    --put the code to process the elements in the collection
    end loop;

    end if;

    end;
    /

    Let me know if you have any questions.
    Boris.


    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >Hello there, I have a problem. I am trying to execute a dynamic cursor.

    I
    >was sucessful doing that but the problem is that I am building my cursor
    >out of the user_columns_tab table where all my columns are listed for my
    >table. The problem is that I know the number of columns in my table which
    >is five. Some columns are of number data type and other of varchar2. Since
    >I know the number of columns, i can declare a record type variable and store
    >all the values. The problem comes when I want to add a new column to my
    >table. If i do, then i have to modify my program to accomodate the new

    column.
    > I don't want to do that. I would like my program to dynamically store

    all
    >my columns into memory even if I add new columns in the future. I tried
    >to do it with a pl/sql table but the problem is that the columns vary in
    >data type. How can I dynamically fetch all the columns in my table into
    >memory even when i add new ones in the future? remember, columns have different
    >data type. Here is my code which didn't work with table because of the
    >different data type. thanks
    >
    >
    >DECLARE
    >v_cursor VARCHAR2(4000) := 'SELECT';
    >TYPE t_RefCur IS REF CURSOR;
    >
    >TYPE get_data IS TABLE OF VARCHAR2(2000);
    >myData get_data;
    >
    >v_ReturnCursor t_RefCur;
    >
    >v_count PLS_INTEGER := 1;
    >
    >
    >CURSOR get_column IS
    > SELECT column_name
    > FROM user_tab_columns
    > WHERE table_name = 'STD_AE_TBL';
    >BEGIN
    > FOR rec IN get_column LOOP
    > v_cursor := v_cursor || ' ' || rec.column_name|| ',';
    > END LOOP;
    > v_cursor := substr(v_cursor, 1, length(v_cursor) -1);
    > v_cursor := v_cursor || ' from STD_AE_TBL';
    >
    > OPEN v_ReturnCursor FOR v_cursor;
    > LOOP
    > FETCH v_ReturnCursor INTO myDATA(v_count);
    > EXIT WHEN v_ReturnCursor%NOTFOUND;
    > DBMS_OUTPUT.PUT_LINE(myData(v_count));
    > v_count := v_count + 1;
    > END LOOP;
    > CLOSE v_ReturnCursor;
    >END;



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center