05-24-04, 16:14 #1Registered User
- Join Date
- Jul 2003
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,
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.');
EXECUTE IMMEDIATE 'ALTER TABLE SHIPPING_RULES_LX ADD SIGNATURE_COMMENT VARCHAR2(255)';
05-24-04, 20:50 #2Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
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.
BillPlease 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.