how to add annonymus block to procedure Oracle?

Question

How I can create procedure with following code because I'm new in oracle always used sybase/ms sql and it was easier.

DECLARE
    temp     VARCHAR2 (255);
    last_val NUMBER(9, 0);
    CURSOR c1 IS
      SELECT DISTINCT table_name
      FROM   user_tab_cols
      WHERE  column_name = 'id';
BEGIN
    FOR asd IN c1 LOOP
        temp := asd.table_name;

        EXECUTE IMMEDIATE 'select NVL(max("id"),0)+1 from "'||temp||'"' INTO
        last_val;

        BEGIN
            EXECUTE IMMEDIATE 'drop sequence "seq_'|| temp||'"';

            EXECUTE IMMEDIATE 'create SEQUENCE "seq_'|| temp||'" MINVALUE '||
            last_val||
            'MAXVALUE     999999999999999999999999999 INCREMENT BY 1 NOCACHE';

            EXECUTE IMMEDIATE 'select '||temp||'.nextval from dual';

            EXECUTE IMMEDIATE 'ALTER SEQUENCE "seq_'||temp||'" INCREMENT BY 1';
        EXCEPTION
            WHEN OTHERS THEN
              NULL;
        END;
    END LOOP;

    COMMIT;
END;

As there are DDL statements in there with two implicit commits each the presence of the explicit commit is irrelevant. A stronger objection is WHEN OTHERS THEN NULL, which is awful practice. It probably does not matter as the wuery has DISTINCT and a sort operation should take care of the "snapshot too old" issue. And column_name = 'id' should be column_name = 'ID'. No need for an explicit cursor either -- stick to an implicit one. And don't declare temp, just reference c1.table_name. And last_val should just be an integer, not NUMBER(9,0) — David Aldridge, Apr 26 '12 at 08:28

score 2 · Accepted Answer · edited Apr 26 '12 at 08:25

2

Use like

CREATE OR replace PROCEDURE Proc_name
IS
  temp     VARCHAR2 (255);
  last_val NUMBER(9, 0);
  CURSOR c1 IS
    SELECT DISTINCT table_name
    FROM   user_tab_cols
    WHERE  column_name = 'id';
BEGIN
    FOR asd IN c1 LOOP
        temp := asd.table_name;

        EXECUTE IMMEDIATE 'select NVL(max("id"),0)+1 from "'||temp||'"' INTO
        last_val;

        BEGIN
            EXECUTE IMMEDIATE 'drop sequence "seq_'|| temp||'"';

            EXECUTE IMMEDIATE 'create SEQUENCE "seq_'|| temp||'" MINVALUE '||
            last_val||
            'MAXVALUE     999999999999999999999999999 INCREMENT BY 1 NOCACHE';

            EXECUTE IMMEDIATE 'select '||temp||'.nextval from dual';

            EXECUTE IMMEDIATE 'ALTER SEQUENCE "seq_'||temp||'" INCREMENT BY 1';
        EXCEPTION
            WHEN OTHERS THEN
              NULL;
        END;
    END LOOP;

    COMMIT;
END;

/

edited Apr 26 '12 at 08:25

Sathyajith Bhat

21,321
22
95
134

answered Apr 26 '12 at 08:14

hkutluay

6,794
2
33
53

3

As there are DDL statements in there with two implicit commits each the presence of the explicit commit is irrelevant. A stronger objection is WHEN OTHERS THEN NULL, which is awful practice. It probably does not matter as the wuery has DISTINCT and a sort operation should take care of the "snapshot too old" issue. And column_name = 'id' should be column_name = 'ID'. – David Aldridge Apr 26 '12 at 08:26
1

To second @DavidAldridge, NEVER use the horror that is `WHEN OTHERS THEN NULL`. It simply hides any exception that might be raised making debugging much more difficult and possibly introducing unexpected behaviour into your code. At the very least log the error somewhere so you could track it. – Ollie Apr 26 '12 at 08:29
@DavidAldridge - perhaps OP has chosen to use case-sensitive table and column names. – Bob Jarvis - Слава Україні Apr 26 '12 at 17:02
@Bob Hah hah hah, obviously not though -- no sane person would commit such an offence. – David Aldridge Apr 26 '12 at 18:35

how to add annonymus block to procedure Oracle?

1 Answers1