Script To Create Sequences Dynamically

[kamal480]

DECLARE
CURSOR DOOM IS SELECT TABLE_NAME FROM USER_TABLES;
V_ID 	NUMBER;
V_DML    VARCHAR2(3000);
V_PK_COL VARCHAR2(100);
V_FOUND  NUMBER;
BEGIN
FOR REC IN DOOM LOOP

IF LENGTH(REC.TABLE_NAME||'_SEQ') >30 THEN
SELECT COUNT(*) INTO V_FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME=SUBSTR(REC.TABLE_NAME,1,26)||'_SEQ';
ELSE
SELECT COUNT(*) INTO V_FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME=REC.TABLE_NAME||'_SEQ';
END IF;

IF V_FOUND=0 THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*)+1 FROM '||REC.TABLE_NAME INTO V_ID;
IF LENGTH(REC.TABLE_NAME||'_SEQ') >30 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE '||SUBSTR(REC.TABLE_NAME,4,30)||'_SEQ'||' START WITH '||V_ID||' INCREMENT BY 1 MINVALUE '||V_ID||' MAXVALUE 9999999999 NOCYCLE NOCACHE ORDER';
ELSE
EXECUTE IMMEDIATE 'CREATE SEQUENCE '||REC.TABLE_NAME||'_SEQ'||' START WITH '||V_ID||' INCREMENT BY 1 MINVALUE '||V_ID||' MAXVALUE 9999999999 NOCYCLE NOCACHE ORDER';
END IF;
EXCEPTION
    	WHEN OTHERS THEN
      	DBMS_OUTPUT.PUT_LINE(V_DML);
END;
ELSE --- THE SEQUENCE IS FOUND DONT DO ANYTHING 
NULL;
END IF;

END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;