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.

I find it hard to believe 32K is not enough for a single insert statement. You could send a whole package under that limit...! — rsenna, Dec 12 '13 at 19:50
Thats space for 1000 columns at least,unless you named each one with a full Dostoyevsky novel. — Mihai, Dec 12 '13 at 19:51
The order must match and it is _always_ a good idea to explicitly list the columns into which you are inserting _and_ the columns from which you are selecting. — GriffeyDog, Dec 12 '13 at 19:56
The maximum length of a column name is 30 characters. If you list the columns for both the `INSERT` and the `SELECT`, as you should, you would only exceed the 32k limit if your tables have more than 500 columns. That would imply a pretty serious normalization issue. Using dynamic SQL in such an environment implies even greater normalization issues. The fact that you'd need to use `dbms_sql` rather than `EXECUTE IMMEDIATE` would seem to be a very minor issue in the scheme of things. — Justin Cave, Dec 12 '13 at 20:03
@JustinCave this is data ware house for storing probe values and my tables have 900+ columns :) — WBAR, Dec 12 '13 at 20:24
@WBAR - Given that Oracle has a limit of 1000 columns (more or less), that strikes me as a rather problematic design even for a data warehouse. — Justin Cave, Dec 12 '13 at 20:27
@WBAR Maybe this will be useful http://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names — Mihai, Dec 12 '13 at 20:29
I also reached this limit a while ago. It was not due to number of columns but usage of bigger calculations, i.e. formula. Example `SUM (salary) OVER (PARTITION BY department_id ORDER BY hire_date RANGE BETWEEN 365 PRECEDING AND 365 FOLLOWING) department_total` - 130 characters for one column! — Wernfried Domscheit, Dec 12 '13 at 21:12
Have you actually tested this in 11.2 and found that there is a 32k limit? The docs suggest otherwise. — David Aldridge, Dec 12 '13 at 22:48
@DavidAldridge I'm building VARCHAR2 which have 32767 chars limit under PL/SQL — WBAR, Dec 13 '13 at 14:08

score 4 · Answer 1 · answered Dec 12 '13 at 19:44

4

No,specify the columns:

INSERT INTO TABLE_A SELECT col2,col1,col3 FROM TABLE_B

answered Dec 12 '13 at 19:44

Mihai

26,325
7
66
81

order in `SELECT` must be correct for target column's order ? – WBAR Dec 12 '13 at 19:52
Yeah the order in select must match the default order of the table in which you insert. – Mihai Dec 12 '13 at 19:53

score 2 · Accepted Answer · answered Dec 12 '13 at 21:05

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;
/

Will ORACLE automatically match correct columns in INSERT from SELECT?

2 Answers2