How can i make multiple, composite query in oracle?
for example this several queries in one step?
1
CREATE TABLE test (id NUMBER PRIMARY KEY, name VARCHAR2(30));
2
CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1;
3
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
4
INSERT INTO test (name) VALUES ('Jon');
5
INSERT INTO test (name) VALUES ('Meloun');
We solved it by wrapping each statement in an EXECUTE IMMEDIATE command inside a PL/SQL script:
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test (id NUMBER PRIMARY KEY, name VARCHAR2(30))';
EXECUTE IMMEDIATE 'CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1';
-- etc
END;
By and large DDL statements have to executed one at a time. It is true that Oracle supports the CREATE SCHEMA syntax, but this only permits the creation of multiple tables and/or views plus grants on them. It doesn't include ancilliary objects such as sequences or triggers.
You could turn the two inserts into a single statement like this:
INSERT INTO test (name)
select 'Jon' from dual
union all
select 'Meloun' from dual
/
This might be more trouble than its worth, but I suppose it does give you a simple transactionality: it inserts either all the rows or none of them.
