Dear Tom,
I've searched this site to find a way to use DBMS_METADATA.GET_DDL in order to get ONLY a FOREIGN KEY ddl.
I used the following code to get PRIMARY KEY ddls:

create or replace function fn_get_PKFK_ddl (i_constraint_name in varchar2 )
return clob
is

h number;
o_constraint_ddl       varchar2(32760);

begin
     h:=dbms_metadata.open('CONSTRAINT');
     dbms_metadata.set_transform_param (DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
     dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
     dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
     o_constraint_ddl:=DBMS_METADATA.GET_DDL('CONSTRAINT' ,i_constraint_name); 
     dbms_metadata.close(h);

     return (o_constraint_ddl) ;

end  fn_get_PKFK_ddl  ;


SQL> select fn_get_PKFK_ddl ('PK_DEPT') from dual;

Which Gives:

  ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "SYSTEM"  ENABLE ;
 
and that's nice.


How do I get a FOREIGN KEY DDL?

Thanks in advance,
Shimon Tourgeman.
 

NCRMST3:SQL>desc e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

NCRMST3:SQL>alter table e modify sal not null;

Table altered.

NCRMST3:SQL>desc e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                       NOT NULL NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

NCRMST3:SQL>begin
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS'  2  ,true);
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
  3    4  dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true);
  5  dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',false);
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATO  6  R',true);
end;
  7    8  /

PL/SQL procedure successfully completed.

NCRMST3:SQL>select dbms_metadata.get_ddl('TABLE','E') from dual;

DBMS_METADATA.GET_DDL('TABLE','E')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."E"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2) NOT NULL ENABLE,
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;


Why the not null constraint is not shown seperately as alter table e modify sal not null? Is there any way i can get not null constraints seperate like foreign keys?

Regards
Shailesh 

Hi,
I replaced the print_clob with dbms_output.put_line, and rerun the statements. However, in 9i it still does not include the semicolon.
I have tried it on the 10g express installed, and it works there.

Here is a more detailed extract:
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
---------- --- ----------------- ------------------ ---------
              1 LIFEDEV
gemini
9.2.0.5.0         30-AUG-09 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL


SQL> BEGIN
  2      dbms_metadata.set_transform_param(dbms_metadata.session_transform
  3                                       ,'SQLTERMINATOR'
  4                                       ,TRUE);
  5  
  6      dbms_output.put_line((dbms_metadata.get_ddl('REF_CONSTRAINT'
  7                                      ,'FK2_HMRC_DATA')));
  8  END;
  9  /

  ALTER TABLE "LPARKER_DEV"."HMRC_DATA" ADD CONSTRAINT "FK2_HMRC_DATA" FOREIGN
KEY ("REPORT_HISTORY_ID")
          REFERENCES "LPARKER_DEV"."HMRC_REPORT_HISTORY"
("REPORT_HISTORY_ID") ENABLE

Hi,
Here is a complete example run in 9.2.0.5.0.
SQL> CREATE TABLE test_column (col1 VARCHAR2(1));

Table created.

SQL> COMMENT ON COLUMN test_column.col1 IS 'Client''s address';

Comment created.

SQL> DECLARE
  2      ln_comment_handle       NUMBER;
  3      ln_comment_trans_handle NUMBER;
  4      lt_comment_ddls         sys.ku$_ddls;
  5  BEGIN
  6      ln_comment_handle       := dbms_metadata.OPEN(object_type => 'COMMENT');
  7      ln_comment_trans_handle := dbms_metadata.add_transform(handle => ln_comment_handle
  8                                                            ,NAME   => 'DDL');
  9  
 10      dbms_metadata.set_filter(handle => ln_comment_handle
 11                              ,NAME   => 'BASE_OBJECT_NAME'
 12                              ,VALUE  => 'TEST_COLUMN');
 13  
 14      lt_comment_ddls := dbms_metadata.fetch_ddl(ln_comment_handle);
 15      DBMS_OUTPUT.PUT_LINE('Got ' || lt_comment_ddls(1).ddltext);
 16  END;
 17  /
Got  COMMENT ON COLUMN "LPARKER_DEV"."TEST_COLUMN"."COL1" IS 'Client's address'


PL/SQL procedure successfully completed.

SQL> COMMENT ON COLUMN "LPARKER_DEV"."TEST_COLUMN"."COL1" IS 'Client's address'
  2  ;
ERROR:
ORA-01756: quoted string not properly terminated

Knowing my luck it is probably resolved in a later version.

Thanks