Forum Stats

  • 3,871,982 Users
  • 2,266,360 Discussions
  • 7,911,018 Comments

Discussions

Fetch table procedure

Vicky007
Vicky007 Member Posts: 60
edited Nov 30, 2009 1:04PM in SQL & PL/SQL
Hi ,
i have 22 tables of name format as 'GenAgile%' and 28 tables of name format as 'GenMicro%' .

table format ==> Name std quantity release


i have to write a procedure which will fetch these tables which are of above format (GenAgile% , GenMiccro% ) and PCT_FREE != 10 from db .

after retrieving table list i have to
alter PCT_FREE column of each retrieved table (set it to number which is input to procedure).

can anyone help me out to form procedure as i am new to this PL world.

i try to form query as

select table_name from user_tables where PCT_FREE <> 10 and table_name like 'GenAgile%' or table_name like 'GenMicro%'

but i dont know how to get all table names in loop and use it in alter command to update its std column by input ?

please help me out.

i have to update col PCT_FREE of user_tables table to user input

Question updated please read context......Edited by: user8713254 on Nov 30, 2009 8:12 AM

Edited by: user8713254 on Nov 30, 2009 8:18 AM
Tagged:

Best Answer

Answers

  • Zeeshan BaiG
    Zeeshan BaiG Member Posts: 2,094
    well u can use Dynamic SQL in PL/SQL for this and REF Cursors refer Oracle documentation for REF CURSORS and DYNAMIC SQL

    plz mark it helpful if it is
  • BluShadow
    BluShadow Member, Moderator Posts: 42,540 Red Diamond
    Are you wanting to update individual rows on each of those tables, or the whole table of rows where there is any row where std != 1?
  • Vicky007
    Vicky007 Member Posts: 60
    edited Nov 30, 2009 11:25AM
    select table_name from user_tables where PCT_FREE NOT equal to 10 and table_name like 'GenAgile%' or table_name like 'GenMicro%'


    PCT_FREE is field of user_tables


    if PCT_FREE not equal to 10 and table_name like GenAgile%' or 'GenMicro%' update PCT_FREE of user_tables to <input_number>
    commit;


    can any1 help me now ?

    Edited by: user8713254 on Nov 30, 2009 8:24 AM
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Nov 30, 2009 11:26AM
    Maybe something like this, not tested. I'm assuming you meant you want to update the individual rows with pct_free <> 10 for each of those tables located in all_tables.
    create or replace procedure t_proc(p_numval in number) is
      cursor cur_tab is 
        select table_name
          from all_tables t
         where t.table_name like 'GENAGILE%'
               or table_name like 'GENMICRO%';    
    begin
      for tab in cur_tab
      loop
        execute immediate 'update ' || tab.table_name || ' t
                              set t.pct_free = :1
                            where t.pct_free <> 10' using p_numval;
      end loop;
      commit;
    exception
    when others then
      rollback;
      raise;
    end;
  • Vicky007
    Vicky007 Member Posts: 60
    no its wrong i just want to update PCT_FREE column of "user_tables" table for those selected tables by INPUT
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Nov 30, 2009 11:32AM
    umm just a second... do you want to update THE user_tables itself? Because that's not possible, you're dealing with a data dictionary view here, that describes your real table in the database. It contains metadata and you can't change it directly, in any other way besides changing the table described by it. It would be like telling a lie to your database about those tables, and Oracle relies and depends on that information inside USER_TABLES to be able to operate correctly.

    Now if that's the case you could maybe explain to us what is it you need to accomplish please.

    Edited by: fsitja on Nov 30, 2009 2:31 PM for clarification
  • Vicky007
    Vicky007 Member Posts: 60
    Its required !
    cant we do it ?
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Nov 30, 2009 11:47AM
    You can't. Why do you need it? Let's not look into the microscope for a second now.

    What's the requirement behind this idea, that moved you down this path in first place?
  • 728534
    728534 Member Posts: 1,386
    Hi welcome,
    Please go through the forum link mentioned below before you try this out.
    <a class="jive_macro jive_macro_message" href="" __jive_macro_name="message" __default_attr="3867075">3867075</a>
    As far as syntax is concerned
    -- Created on 11/30/2009 by USER 
    declare 
      -- Local variables here
      i INTEGER :=20; /* variable you will pass to the procedure */
    begin
      -- Test statements here
      FOR x IN (SELECT table_name FROM user_tables WHERE pct_free !=10 AND (table_name LIKE 'GENAGILE%' OR table_name LIKE 'GENMICCRO%')) LOOP
      
     dbms_output.put_line (x.table_name); /* for debugging */
      EXECUTE IMMEDIATE 'ALTER table '||x.table_name||'  pctfree ' || i;
      END LOOP;
    end;
    Cheers!!!
    Bhushan
This discussion has been closed.