Friday, July 1, 2016

Oracle sequence starting with max value from a table

DECLARE
  ln_max_count NUMBER;
  v_sql        VARCHAR2(250);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting');
  SELECT 'CREATE SEQUENCE a MINVALUE 0 START WITH '
    ||MAX(attribute17)
    ||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM HZ_CUST_ACCT_SITES_ALL;
  DBMS_OUTPUT.PUT_LINE('v_sql - '||v_sql);
  EXECUTE IMMEDIATE v_sql;
  DBMS_OUTPUT.PUT_LINE('End');
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error - '||SQLERRM);
END;