create table / sequence if not exist.

it’s quite uncommon to check first and then create the table. If you want to have a running code according to your method, this will be:

BEGIN
  DECLARE
    nCount NUMBER;
    v_sql LONG;
  BEGIN -- try to execute sql for UFI_RQST table
    SELECT count(*) into nCount FROM dba_tables where table_name = 'UFI_RQST';
    IF(nCount <= 0)
    THEN
      v_sql:='create table UFI_RQST (UFI_RQST_ID NUMBER(5) PRIMARY KEY, RQST CLOB, RSPNS VARCHAR2(15), CRTD_DT DATE DEFAULT (sysdate))';
      EXECUTE IMMEDIATE v_sql;
      EXECUTE IMMEDIATE 'GRANT SELECT ON UFI_RQST_SEQ TO SNAPADMIN';
      EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON UFI_RQST TO SNAPADMIN';

    END IF;
  END;

  DECLARE v_dummy NUMBER;
  BEGIN -- try to find sequence in data dictionary
    SELECT
      1
    INTO v_dummy
    FROM user_sequences
    WHERE sequence_name = 'UFI_RQST_SEQ';

    -- if sequence found, do nothing
    EXCEPTION WHEN no_data_found THEN -- sequence not found, create it
    EXECUTE IMMEDIATE 'CREATE SEQUENCE UFI_RQST_SEQ
                       START WITH     1
                       INCREMENT BY   1
                       NOCACHE
                       NOCYCLE';
  END;
END;

But I’d rather go catch on the Exception, saves you some unnecessary lines of code:

declare
v_sql LONG;
begin

v_sql:='create table EMPLOYEE
  (
  ID NUMBER(3),
  NAME VARCHAR2(30) NOT NULL
  )';
execute immediate v_sql;

EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END; 
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s