make multiple, composite query in oracle

Question

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');

Answer 1

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.

Answer 2

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;