Results 1 to 3 of 3

Thread: dynamic cursor

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    14

    Unanswered: dynamic cursor

    I have 2 questions

    I have a string say something like
    str varchar2;
    select c1,c2,c3 from tab1 which i form dynamically.
    Is there anyway i can have a cursor associated with it?
    something like cursor c1 is (execute str)
    I don't know the column names since i have generated them dynamically and formed them into string.
    but i can get the column names into a var say
    str2:=ColumnDefinitionTab(j).column_name;
    So if i have to get a particular data for the column using the cursor how do i do it?

    Regds
    Manojkv

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246

    Re: dynamic cursor

    You don't manually pull data from a cursor into variables. Let the syntax of the Execute Immediate command do it for you. For example:

    declare
    v1 varchar2(30);
    v2 varchar2(30);
    v3 number;
    cursor c1 is
    select 'select count(*) from ' || object_name
    from user_objects
    where object_type = 'TABLE';
    begin
    open c1;
    loop
    fetch c1 into v1;
    exit when c1%notfound;
    v2 := v1;
    execute immediate (v1) into (v3);
    dbms_output.put_line (v3);
    end loop;
    close c1;
    end;
    /

    Notes:
    1. the above code was from memory. I did not check for syntax.
    2. if you are doing DDL then the dynamic sql must include BEGIN nd END statements around the command. This is not required when performing DML, as shown above.
    3. I reassigned the dynamic SQL statement into variable v2 just prior to runtime due to an Oracle bug that occassionally yields error "end of communication on channel ..."
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    To do a dynamic cursor you will have to use REF Cursors. Here is an example:

    DECLARE
    TYPE curvar_type IS REF CURSOR;

    curvar curvar_type;
    column_1 VARCHAR2 (100);
    column_2 VARCHAR2 (100);
    myQuery VARCHAR2 (1000);

    BEGIN

    myQuery := 'SELECT col1, col2 FROM table1';

    OPEN curvar FOR myquery;

    WHILE TRUE LOOP
    FETCH curvar INTO column_1, column_2;
    EXIT WHEN curvar%NOTFOUND;
    END LOOP;

    CLOSE curvar;

    END;

    Hope this helps.

    JoeB

Posting Permissions

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