Friday, 8 July 2011

How to register your own table in oracle apps...database

Hi frnd the following code(plsql procedure) is useful to you to register your own table in oracle apps.(Mostly it is usful to you when you work in alert management in oracle and want to creat alert on your own table)
---------------------------------------------------------------------------------------------------------

STEP--1-first you create your table. and add data in table.
Ex---
create table my_alert(item varchar2(10), qty number);


------------------------------------------------------------------------------
STEP--2-then run the following code..it will creat the procedure  "Proc_ad_ad".

CREATE OR REPLACE PROCEDURE proc_ad_ad (ap_sname VARCHAR2, ptable_name VARCHAR2)
  IS
     CURSOR c1  IS
      SELECT ROWNUM, column_name, data_type, data_length
      FROM all_tab_columns
      WHERE table_name = upper(ptable_name);

  va c1%ROWTYPE;
  BEGIN
      ad_dd.register_table (ap_sname, ptable_name, 'T');

    OPEN c1;

      LOOP
          FETCH c1  INTO va;

      EXIT WHEN c1%NOTFOUND;
         ad_dd.register_column(ap_sname,ptable_name,va.column_name,va.ROWNUM,va.data_type,va.data_length,'Y','N');
DBMS_OUTPUT.put_line ( va.ROWNUM ||va.column_name||va.data_type||va.data_length);
      END LOOP;

    CLOSE c1;

COMMIT;
END proc_ad_ad;
/
show error;
------------------------------------------------------------------------------------------------------------------
STEP-3
register table using following code.

begin
proc_ad_ad('PO', 'my_alert');
end;

--------------------------------------------------------

we provide 2 parameter to this procedure
1.Application Name (Ex.PO,AP,etc)
2.Table Name.(Ex-My_aalert).
-------------------------------------------------------

No comments:

Post a Comment