Home » Developer & Programmer » Forms » How to get the ddl of a table in forms?
How to get the ddl of a table in forms? [message #155927] Thu, 19 January 2006 13:16 Go to next message
mentor1936
Messages: 1
Registered: January 2006
Location: Asia
Junior Member
Hi,

This is my first posting to this forum. I am using forms 6i and 9i database for testing purpose on windows O/S. I want to develop a small form application for getting the structure of any table in a particular schema.

My Application will be based on the following scenario:
In order to achieve what I am wanting, I am planning to use two display screens in a form’s block with a ddl button.

For Example After populating all the tables of Scott’s schema in screen no: 1, I highlight the Scott’s EMP table then I click the ddl button in order to get the ddl of that table in a screen no: 2.

In this regard I like to ask the following:
1. What is the sql/technique for getting the ddl of any table in the database?
2. How should the table name of a particular schema display in a screen no: 1 so when the control goes to any table in a screen no: 1, the ddl of that table will be displayed after pressing the ddl button?
3. What technique I adopt and how should I implement it in forms in order to display the ddl of a table in forms screen no: 2 when I click the ddl Button?

Please give me your ideas and suggestion in order to accomplish what I am wanting. Related codes will also be helpful form me.

May GOD bring this year with full of happiness to those people who will assist me regarding my task.

John

Upd-mod: Remove excess spacing so I can READ IT ON ONE PAGE!!

[Updated on: Thu, 19 January 2006 19:10] by Moderator

Report message to a moderator

Re: How to get the ddl of a table in forms? [message #155952 is a reply to message #155927] Thu, 19 January 2006 19:18 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
First WINDOW will be based on either ALL_TABLES or USER_TABLES. When you press the button you will use code similar to below, which I copied from a SQL deck, to populate a multi-line field on your main block that you then show in a new modal window.
select 'PROMPT Drop and Create Table my_usr.' || substr ('&1', 1, 30) || '.'
  from dual
/
select 'DROP TABLE my_usr.' || substr ('&1', 1, 30) || ';'
  from dual
/
select 'CREATE TABLE my_usr.' || substr ('&1', 1, 30)
  from dual
/
select   decode (column_id,
                 1, ' (',
                 ' ,')
         || rpad (column_name, 30)
         || ' '
         || rpad (data_type
                  || decode (data_type,
                             'NUMBER', '(' || data_precision || decode (data_scale,
                                                                        0, '',
                                                                        ',' || data_scale) || ')',
                             'CHAR', '(' || data_length || ')',
                             'VARCHAR2', '(' || data_length || ')',
                             ' '),
                  15)
         || ' '
         || decode (nullable,
                    'N', ' NOT NULL',
                    ' ')
    from dba_tab_columns
   where owner = 'MY_USR'
     and table_name = upper ('&1')
order by column_id
/
select ' ) TABLESPACE &2'
  from dual
/
select '  STORAGE ('
       || decode (upper ('&2'),
                  'SYSTEM', 'INITIAL ' || floor (initial_extent / 100) || ' NEXT '
                   || floor (next_extent / 100),
                  'INITIAL ' || floor (initial_extent * 2 / 3) || ' NEXT ' || floor (next_extent * 2 / 3) )
       || ' MAXEXTENTS UNLIMITED PCTINCREASE 0);'
  from dba_tables
 where owner = 'MY_USR'
   and table_name = upper ('&1')
/

I can't remeber where to get the tablespace name but you could hardcode it. Substitute your table name for the &1 argument and remember to use chr(10) as a new line delimiter.

David

[Updated on: Thu, 19 January 2006 19:20]

Report message to a moderator

Re: How to get the ddl of a table in forms? [message #193889 is a reply to message #155927] Tue, 19 September 2006 16:38 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try using dbms_metadata package in the database. It will spit out a clob or some such thing of the DDL. You can write a short wrapper around it for you needs.

Good luck, Kevin
Previous Topic: Bar Code Reading
Next Topic: object oriented database
Goto Forum:
  


Current Time: Fri Dec 09 02:10:17 CST 2016

Total time taken to generate the page: 0.11124 seconds