Oracle Query

[zahid_ali]

Hello ppl...i have a toughie for you.......

i am kinda stuck in this one...so pls help me out.....

i need to find out the create table syntax including not null primary key...foreign key...etc for the two tables

attended
and
publishedat

i will describe all the tables as shown...

SQL> describe user1.faculty;
Name Null? Type
----------------------------------------- -------- ------------
NAME NOT NULL CHAR(20)
ID# NOT NULL NUMBER(4)
ADDRESS CHAR(15)
PHONE# CHAR(15)
DEPARTMENT CHAR(5)
SALARY NOT NULL NUMBER(9,2)

SQL> describe user1.conferences;
Name Null? Type
----------------------------------------- -------- ------------
CID NOT NULL CHAR(4)
CNAME NOT NULL CHAR(20)
CITY CHAR(15)
CDATE CHAR(10)
ORGFID NUMBER(4)

SQL> describe user1.grants;
Name Null? Type
----------------------------------------- -------- ------------
FID NOT NULL NUMBER(4)
GRANTNAME NOT NULL CHAR(20)
GRANTAMOUNT NUMBER(9,2)
DURATION NUMBER(3)

SQL> describe user1.attended;
Name Null? Type
----------------------------------------- -------- ------------
FID NOT NULL NUMBER(4)
CNAME NOT NULL CHAR(20)

SQL> describe user1.publishedat;
Name Null? Type
----------------------------------------- -------- ------------
FID NOT NULL NUMBER(4)
CNAME NOT NULL CHAR(20)
CSESSION NOT NULL CHAR(5)
BEGINPAGE NUMBER(4)
ENDPAGE NUMBER(4)


So pls pls help me find out the create table syntax for only attended table and publishedat tables only......

[skhanal]

Have you looked at DBMS_METADATA package, it generates scripts for any database object.

[zahid_ali]

Yes i have checked the DBMS metadata package...but unfortunately i don't have it with me.....and plus i did not understand how exactly to use it to specify creations...so...pls help me...just let me know the crete table syntax for the two tables....thank you for your message..!!!!!!

[skhanal]

you just need to execute

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

Where DEPT is table name in SCOTT schema

[zahid_ali]

thanks for the reply....i shall try that...
thanks once again....

[zahid_ali]

hello ppl....
just wanted to say thanx for the replies.....

it was good to receive ur replies..... ne with with more replies pls feel free.....

how many of you ppl know relational algebra... coz i am getting really confused with some areas in it like 'quotient' operator etc... i will post some examples soon .. !!! thanks ppl

-zahid

[fbchua]

Hi,

I think you can have another option, other than using dbms_metadata.get_ddl you could also use export and import but upon import you will specify INDEXFILE parameter. The contents of the indexfile are purely ddl statements to recreate the structure of the specific object or objects included in the export dump file. Also if your worrying if it will import the export file into your database, it will not... It will only produce a file containing the ddl statements that you will need to recreate the objects. Simply do a search on the indexfile to find specific table that you wish to recreate and copy the script to a sql window.

Thanks.