| How to get the ddl of a table in forms? [message #155927] |
Thu, 19 January 2006 13:16  |
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  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
