Fetch table procedure

Vicky007 · Nov 30, 2009 11:04AM

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

BluShadow · Nov 30, 2009 11:07AM

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?

Zeeshan BaiG · Nov 30, 2009 11:07AM

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 · Nov 30, 2009 11:07AM

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 · Nov 30, 2009 11:23AM

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 · Nov 30, 2009 11:25AM

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 · Nov 30, 2009 11:28AM

no its wrong i just want to update PCT_FREE column of "user_tables" table for those selected tables by INPUT

fsitja · Nov 30, 2009 11:29AM

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 · Nov 30, 2009 11:43AM

Its required !
cant we do it ?

fsitja · 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 · Nov 30, 2009 1:04PM

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

Forum Stats

Quick Links

Categories

Fetch table procedure

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Fetch table procedure

Best Answer

Answers