Skip to Main Content

E-Business Suite

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need form personalisation help in Inventory responsibility to restrict DFF

User_H6M8AJan 8 2020 — edited Feb 9 2021

Hello Gurus,

Please can you advise how to approach for the below requirement.

In Inventory Master there are 10 DFF shown in the form.

I need to restrict 2 of the DFF fields to one particular user only so that they can only enter values there.

For example there are users A,B,C,D .I want to create a form personalisation so that user "A" can only change 2 of the DFF out of 10 only.

Please need help

Comments

Zeeshan BaiG
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
Answer
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?
Marked as Answer by Vicky007 · Sep 27 2020
Vicky007
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
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
no its wrong i just want to update PCT_FREE column of "user_tables" table for those selected tables by INPUT
fsitja
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
Its required !
cant we do it ?
fsitja
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
Hi welcome,
Please go through the forum link mentioned below before you try this out.
3867075
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
1 - 9

Post Details

Added on Jan 8 2020
0 comments
23 views