Home » SQL & PL/SQL » SQL & PL/SQL » logic problem
logic problem [message #253987] Wed, 25 July 2007 07:44 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i have the following script, the idea is to add new column
to 3 tables together in a single piece of code

but the control isnt going to the 2nd table..and with this
code, only the table1 is altered

can anyone tell me why?

thanks in advance


SET serverout ON SIZE 999999
SET VERIFY OFF


DECLARE
    t_tblsp   VARCHAR2(30); 
    i_tblsp   VARCHAR2(30); 
    l_sqlstmt VARCHAR2(32767);

BEGIN -- OUTER BLOCK
   BEGIN
        dbms_output.put_line('Inside TABLE1');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('TABLE1');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('TABLE1')
            AND COLUMN_NAME = UPPER('NEWCOL');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 
                                    add NEWCOL  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;

   
   BEGIN
        dbms_output.put_line('Inside TABLE1');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('TABLE2');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('TABLE2')
            AND COLUMN_NAME = UPPER('NEWCOL');
            return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE TABLE2 
                                    add NEWCOL  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    

   
   BEGIN
        dbms_output.put_line('Inside TABLE1');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('TABLE3');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('TABLE3')
            AND COLUMN_NAME = UPPER('NEWCOL');
            return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE TABLE3 
                                    add NEWCOL  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
END; -- OUTER BLOCK

/

Re: logic problem [message #253991 is a reply to message #253987] Wed, 25 July 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
gautamvv wrote on Wed, 25 July 2007 08:44

but the control isnt going to the 2nd table..and with this
code, only the table1 is altered

can anyone tell me why?

thanks in advance


    
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
END; -- OUTER BLOCK

/




Removing that might help you.
Re: logic problem [message #253994 is a reply to message #253987] Wed, 25 July 2007 08:01 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member

SET serverout ON SIZE 999999
SET VERIFY OFF


DECLARE
    t_tblsp   VARCHAR2(30); 
    i_tblsp   VARCHAR2(30); 
    l_sqlstmt VARCHAR2(32767);

BEGIN -- OUTER BLOCK
   BEGIN
        dbms_output.put_line('Inside ETSP_STOP_PAYMENT');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSP_STOP_PAYMENT');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSP_STOP_PAYMENT')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSP_STOP_PAYMENT 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;

   
   BEGIN
        dbms_output.put_line('Inside ETSP_RANGE_STOP_PAYMENT');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSP_RANGE_STOP_PAYMENT');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSP_RANGE_STOP_PAYMENT')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSP_RANGE_STOP_PAYMENT 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    

   
   BEGIN
        dbms_output.put_line('Inside ETSH_STOP_PAYMENT_HISTORY');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSH_STOP_PAYMENT_HISTORY');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSH_STOP_PAYMENT_HISTORY')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSH_STOP_PAYMENT_HISTORY 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    
    --EXCEPTION
    --WHEN OTHERS THEN
       -- NULL;
END; -- OUTER BLOCK

/





no change, i have commented the return too

but only one table is altered
Re: logic problem [message #253996 is a reply to message #253987] Wed, 25 July 2007 08:03 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
works now, i am so silly

SET serverout ON SIZE 999999
SET VERIFY OFF


DECLARE
    t_tblsp   VARCHAR2(30); 
    i_tblsp   VARCHAR2(30); 
    l_sqlstmt VARCHAR2(32767);

BEGIN -- OUTER BLOCK
   BEGIN
        dbms_output.put_line('Inside ETSP_STOP_PAYMENT');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSP_STOP_PAYMENT');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSP_STOP_PAYMENT')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSP_STOP_PAYMENT 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;

   
   BEGIN
        dbms_output.put_line('Inside ETSP_RANGE_STOP_PAYMENT');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSP_RANGE_STOP_PAYMENT');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSP_RANGE_STOP_PAYMENT')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSP_RANGE_STOP_PAYMENT 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    

   
   BEGIN
        dbms_output.put_line('Inside ETSH_STOP_PAYMENT_HISTORY');
        SELECT Table_Name INTO l_sqlstmt 
        FROM User_Tables WHERE Table_Name = UPPER('ETSH_STOP_PAYMENT_HISTORY');
        -- Table already exists
        BEGIN 
            SELECT COLUMN_NAME INTO l_sqlstmt
            FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('ETSH_STOP_PAYMENT_HISTORY')
            AND COLUMN_NAME = UPPER('HOST_STOPPED_DATE');
            -- return;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXECUTE IMMEDIATE 'ALTER TABLE ETSH_STOP_PAYMENT_HISTORY 
                                    add HOST_STOPPED_DATE  DATE ';
        END;
        RETURN;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        NULL;
    END;
    
    --EXCEPTION
    --WHEN OTHERS THEN
       -- NULL;
END; -- OUTER BLOCK

/




Re: logic problem [message #253997 is a reply to message #253987] Wed, 25 July 2007 08:03 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just 3 "alter table" in a SQL script?

Regards
Michel
Previous Topic: ora-04098 urgent
Next Topic: Trigger questions (merged)
Goto Forum:
  


Current Time: Fri Dec 09 19:40:11 CST 2016

Total time taken to generate the page: 0.09922 seconds