Will ORACLE automatically match correct columns in INSERT from SELECT?

Question

In Oracle 11gR2 i have this problem:

I have for example TABLE_A with structure:

+--------+--------+--------+
|  COL1  |  COL2  |  COL3  |
+--------+--------+--------+

and TABLE_B_ with structure:

+--------+--------+--------+
|  COL2  |  COL1  |  COL3  |
+--------+--------+--------+

(so column definitions are the same but they are in different order)

Executing something like that:

INSERT INTO TABLE_A 
SELECT * FROM TABLE_B;

Will work ok?

My problem is that making list of columns will exceed 32767 chars for EXECUTE IMMEDIATE statement.

Answer 1

In newer Oracle release you can use CLOB in EXECUTE IMMEDIATE, but I don't know if the 32K limit applies also.

Anyway, you can use DBMS_SQL package to run statements bigger than 32k.

DECLARE
    stmt DBMS_SQL.VARCHAR2A;
    c number;
    res number;
BEGIN
    stmt(1) := 'insert into table_a (';
    stmt(2) := 'col_a, ';
    stmt(3) := 'col_b, ';
    stmt(4) := 'col_c) ';
    stmt(5) := 'select ';
    stmt(6) := 'col_bb, ';
    stmt(7) := 'col_cc + col_ee + DECODE(...), ';
    stmt(8) := 'col_dd) ';
    stmt(9) := 'from table_b ';
    stmt(10) := 'where ... '; 
    -- each element can have up to 32K characters, number of elements is (almost) unlimited
    c := DBMS_SQL.open_cursor;
    DBMS_SQL.parse(c, stmt, 1,10, TRUE, DBMS_SQL.NATIVE);
    res := DBMS_SQL.execute(c);
    DBMS_SQL.close_cursor(c);
END;
/

Answer 2

No,specify the columns:

INSERT INTO TABLE_A SELECT col2,col1,col3 FROM TABLE_B