Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Unanswered: Dynamically creating the FROM clause using cursors

    Hi,

    I'm fairly new to Oracle, but an old hack at Access.

    I have an Access application that runs Update/Insert/Delete statements through to an Oracle database. Each time an Update statement is issued, a control field common to all the tables called 'APPLICATION_ID' is updated as well, with the value 'MIKAPP'. These updates can be applied to any table.

    The table names are stored in a table called TABS, and to determine if I have updates in a specific table is easy enough. What I want to do is iterate through the TABS table (using TABLE_NAME) and dynamically create the FROM clause in my SELECT statement. I have seen variables used in the WHERE clause without explicitly being declared and not sure why I am getting an error in the FROM clause.

    This is the code:
    Code:
     declare
      v_tab tabs.table_name%type;
      cursor c_tables is
        select * from tabs;
      cursor c_curr_table is
        select count(*) from v_tab
        where APPLICATION_ID = 'MIKAPP';
    begin
      for r_table in c_tables
      loop
        v_tab := r_table.table_name;
        open c_curr_table;
          if c_curr_table%rowcount > 0
            then
            dbms_output.put_line(v_tab || ' ' || c_curr_table%rowcount);
          end if;
        close c_curr_table;
      end loop;
    end;
    and this is the code with the line numbers and error code:
    Code:
      1  declare
      2    v_tab tabs.table_name%type;
      3    cursor c_tables is
      4      select * from tabs;
      5    cursor c_curr_table is
      6      select count(*) from v_tab
      7      where APPLICATION_ID = 'MIKAPP';
      8  begin
      9    for r_table in c_tables
     10    loop
     11      v_tab := r_table.table_name;
     12        if c_curr_table%rowcount > 0
     13          then
     14          dbms_output.put_line(v_tab || ' ' || c_curr_table%rowcount);
     15        end if;
     16    end loop;
     17* end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-06550: line 6, column 26:
    PLS-00201: identifier 'V_TAB' must be declared
    ORA-06550: line 6, column 5:
    PL/SQL: SQL Statement ignored
    Thank you for your time.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Homer, do not select * from tabs

    If you wanted to select all columns then

    v_tab tabs.table_name%rowtype
    You only need to select table_name though.

    Do select table_name from tabs

    Mmmmkay?

    Oh, wait. You can't do this. You have to do the dynamic SQL thingy.
    Hold on....
    Last edited by Nocopy; 05-04-04 at 14:34.
    My way or the highway. Yeah

  3. #3
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68

    You had a good stab at it. You have a future in Oracle. :-)

    declare
    cnt number;
    begin
    for r_table in(select table_name from tabs)
    loop
    EXECUTE IMMEDIATE
    'select count(*) from '||r_table.table_name||
    ' where APPLICATION_ID = ''MIKAPP'''
    INTO cnt;
    if cnt >0 then
    dbms_output.put_line(r_table.table_name||' '||cnt);
    end if;
    end loop;
    end;

    Last edited by Nocopy; 05-04-04 at 14:43.
    My way or the highway. Yeah

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Thank you muchly, Nocopy! And I take your words on my initial try as a kindness.

    I had just found that EXECUTE IMMEDIATE line at http://www.orafaq.org/faqplsql.htm#DYNSQL, but was still scratching my head on how to use it.

    This is exactly what I need to get started on the further creation of my stored proc that will use this pl/sql block.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Well, I "heard" that in sqlserver or maybe access for that matter everything is more dynamic. I see you trying to do it the Sqlserverish way.
    PL/SQL is more statiky but it runs mighty fast.

    So, not bad, not bad.
    My way or the highway. Yeah

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This problem can be solved by only using SQL*Plus
    by spooling SQL statements into a file and then invoking the new file.

    SPOOL NEWSQL.SQL
    SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
    FROM USER_TABLES;
    SPOOL OFF
    @NEWSQL.SQL
    EXIT
    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.

  7. #7
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Quote Originally Posted by anacedent
    This problem can be solved by only using SQL*Plus
    by spooling SQL statements into a file and then invoking the new file.
    It can also be solved using Nocopy's method, but this looks handy as well. Not sure if it can be ported to a Procedure.

    Thanks!
    All code ADO/ADOX unless otherwise specified.
    Mike.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    you could do a ref cursor ...

    PHP Code:
    declare 

        
    vTable  varchar2(30);

        
    cursor cGetTables is
            select table_name
            from cols
            where column_name 
    'APPLICATION_ID';

        
    type ref_cur_1 is REF CURSOR;
            
    ref_cur             ref_cur_1;
            
    vCount              number;

    begin

        Open cGetTables
    ;
            
    Fetch cGetTables into vTable;
            While 
    cGetTables%found loop

              open ref_cur 
    for
              
    'select count(*) from '||vTable||' where APPLICATION_ID = ''MIKAPP''';

              
    FETCH ref_cur into vCount;

                if 
    vCount 0 then
                   
    /* do something here */
                
    end if;

              
    close ref_cur;

            
    Fetch cGetTables into vTable;
            
    End loop;
        
    Close cGetTables;

    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Thanks, Duck. I will read up on Ref Cursors at work tomorrow to see how they differentiate from the cursors I currently (sort of) know.

    The multitiered approaches in this thread will certainly be a good basis for furthering Oracle solutions as I migrate from Access and <cough>SQL Server.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

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