Re: script to dynmically create all foreign key constraints

Steve wrote:

> I know this sounds kind of hokey but the ON DELETE
> CASCADE functionality is only ever required once every odd month so. I
> know I can get the constraints from import export but I'd like a
> dynaminc sql that I can just edit one and run.

See the details on the DBMS_SQL package. It supports an EXEC SQL function similar to that in Pro*C - allowing you to write dynamic SQL inside a stored proc.

E.g. (written from memory and not tested)

PROCEDURE ExecSQL ( sql IN VARCHAR2, ret OUT integer ) IS   cid integer;
BEGIN
  cid := DBMS_SQL.Open_Cursor;

  dbms_sql.Parse( cid, sql, DBMS_SQL.NATIVE );   ret := DBMS_SQL.Execute( cid );

  DBMS_SQL.Close_Cursor( cid );

  EXCEPTION
    WHEN OTHERS THEN

      raise_application_error(-20101, 'Error in ExecSQL : ' ||
                              SQLERRM(SQLCODE));

  commit;
END;  

--
Billy