Zdennis ar-extensions

(Probably merits its own ticket!)

As implemented, the oracle import function inherits the default behaviour, which I think produces a statement of the form:

INSERT INTO table(a,b) VALUES (1,2), VALUES(3,4) ...

Oracle doesn't like that. Actually, AFAICT Oracle doesn't really support arbitrary numbers of INSERTs of the ar-extensions type at all.

There's this:

INSERT ALL INTO tableA(v,w,x,id) VALUES (1,2,3,tablea_seq.nextval) INTO tableB(y,z,id) VALUES ('a', 'b',tableb_seq.nextval) SELECT 1 FROM DUAL;

...which is flexible but won't work for multiple inserts on the same table using a sequence (because the sequence is only referenced once).

There's more luck with wrapping the INSERTs in a PL/SQL block thus:

BEGIN INSERT INTO tableA(v,w,id) VALUES (1,2,tablea_seq.nextval); INSERT INTO tableA(v,w,id) VALUES (2,3,tablea_seq.nextval); INSERT INTO tableA(v,w,id) VALUES (4,5,tablea_seq.nextval); COMMIT; END

That works, but there's a maximum size to the command that doesn't depend on string size, but on the number of "Diana Nodes". That seems to reference a partially-compiled state of PL/SQL, which is an extended form of Ada. I suspect thinking AST wouldn't be too wrong. Anyhow, we're restricted to 64K of these things, but what code constructs constitute a node is not clear. On one test with a small table I was able to get up to about 7500 inserts and 870KB of text, so it should be too bad, although it does add an element of uncertainty.

I have a patch in the works, I just wanted to document where I'd got to in its own ticket.

There used to be an "array processing" facility using the Oracle libraries, but I haven't looked at it for almost 2 decades so I need to do some digging on that front. I don't know if it would be capable of being handled dynamically in any case.

Ho hum.