Example is given below to fetch any Oracle objects DDL script using DBMS_Metadata.Get_DDL command in Oracle Forms using Forms_DDL command.

You can download this form for free including source code with following link from Google Drive Dbms_Utility.fmb

You may need to create a table in current schema/user by which you are logging with and below is the script for this:

Create Table DDL_Script (ddl varchar2(1000));

I will add further more tabs related to Dbms utility tasks to this form and will share also.


Following is the code written in Show DDL push button:
DECLARE
   v        VARCHAR2 (4000);
   objtpe   VARCHAR2 (100);
BEGIN
   --    v := dbms_metadata.get_ddl('TABLE', :objname, user);
   SELECT object_type
     INTO objtpe
     FROM user_objects
    WHERE object_name = :objname;

   :objtype := objtpe;

   FORMS_DDL ('drop table ddl_script');
   FORMS_DDL(   'create table ddl_script as select dbms_metadata.get_ddl('
             || CHR (39)
             || objtpe
             || CHR (39)
             || ','
             || CHR (39)
             || :objname
             || CHR (39)
             || ', user) ddl from dual');

   IF FORM_SUCCESS
   THEN
      SELECT ddl
        INTO :ddltxt
        FROM ddl_script
       WHERE ROWNUM = 1;
   ELSE
      :statbar := 'Message: Object does not exists.';
      FORMS_DDL ('create table ddl_script (ddl varchar2(1000))');
   END IF;
END;

Following is the code written in When-new-form-instance trigger:

set_window_property(forms_mdi_window, window_state, maximize);

DECLARE
   rg_list_id   RECORDGROUP;
   rg_name      VARCHAR2 (20) := 'OBJECTTYPES';
   ret_code     NUMBER;
   --The following holds a SELECT query from which the list elements are derived.
   v_select     VARCHAR2 (300);
BEGIN
   BEGIN
      FORMS_DDL ('drop table ddl_script');
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   BEGIN
      FORMS_DDL ('create table ddl_script (ddl varchar2(1000))');
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   ret_code := POPULATE_GROUP ('OBJECTNAMES');
   POPULATE_LIST ('OBJNAME', 'OBJECTNAMES');
END;

You can find the record group details itself in form which I am sharing with this post.

Ask Your Questions By Comment Below.

Example is given for using DBMS_Metadata.get_ddl in Oracle forms through forms_ddl command.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.