Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Conditional Execution of DDL in SQL?

    We are looking for a cleaner way to conditionally execute a DDL statement, controlled by a SQL statement. Specifically, we want to add a column to a table, but to only do that when the column does not already exist.

    So, can the below Anonymous Procedure be re-writted using, say, SELECT CASE WHEN END instead?



    SET SERVEROUTPUT ON
    .
    .
    .
    -- Need 10 lines of an anonymous PL/SQL procedure to conditionally execute one line of DDL,
    DECLARE
    XXX INTEGER;
    BEGIN
    SELECT COUNT(*) INTO XXX FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'SHIPPING_RULES_LX' AND COLUMN_NAME = 'SIGNATURE_COMMENT';
    IF (XXX > 0) THEN
    DBMS_OUTPUT.PUT_LINE('column SIGNATURE_COMMENT already exists in table SHIPPING_RULES_LX.');
    ELSE
    EXECUTE IMMEDIATE 'ALTER TABLE SHIPPING_RULES_LX ADD SIGNATURE_COMMENT VARCHAR2(255)';
    END IF;
    END;
    /

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You could just execute the DDL and trap any exception? Haven't tried it myself though....

    Similar to expecting a 'no_data_found' for a select into, the exception handler is planned as part of the code.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •