| How can I execute multiple SQL statements in one batch? [message #682652] |
Thu, 05 November 2020 07:42  |
 |
Darth Waiter
Messages: 41
Registered: October 2020
|
Member |
|
|
In MS SQL I simply write
create table a(a int)
create table b(b int)
drop table a
drop table b
How can I reproduce the same in Oracle 12c? I tried to wrap it in begin/end but Oracle did not like it.
Do I really have to go down execute immediate path?
begin
execute immediate 'create table a(a int)';
execute immediate 'create table b(b char(1))';
execute immediate 'drop table a';
execute immediate 'drop table b';
end; [Updated on: Thu, 05 November 2020 07:43] Report message to a moderator |
|
|
|
| Re: How can I execute multiple SQL statements in one batch? [message #682653 is a reply to message #682652] |
Thu, 05 November 2020 07:56  |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Are you looking for CREATE SCHEMA, such as the example on the docs:
CREATE SCHEMA AUTHORIZATION oe
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED'
GRANT select ON new_product_view TO hr; |
|
|
|
|
|
| Re: How can I execute multiple SQL statements in one batch? [message #682655 is a reply to message #682654] |
Thu, 05 November 2020 08:21 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, what do you think CREATE SCHEMA does? It is executes several DDLs in one call. Which is what you said you wanted.
If for some silly reason you want to do DDL in PL/SQL, then of course you have to use EXECUTE IMMEDIATE. PL/SQL is a statically compiled language, so without EXECUTE IMMEDIATE you can't expect code that contains references to non-existent objects to compile.
|
|
|
|
|
|
| Re: How can I execute multiple SQL statements in one batch? [message #682659 is a reply to message #682656] |
Thu, 05 November 2020 10:11 |
 |
EdStevens
Messages: 1375
Registered: September 2013
|
Senior Member |
|
|
Darth Waiter wrote on Thu, 05 November 2020 08:55I have no faintest idea what CREATE SCHEMA does, so I cannot possibly think of it.
My goal was to execute those DROP TABLE commands dynamically, from a .NET client application, in one shot, in order to prepare a clean sheet for my unit test scenario that would create them.
If there is no other way, then I will stick to EXECUTE IMMEDIATE.
Thank you!
Al
Ah, now you have introduced a further complexity - issuing these from a .net application. One way or another, you are going to have to code each of the CREATE and DROP commands. And in the end, no matter what you do, oracle will only process them one at a time. So what's the problem with having your .net code issue them sequentially? I see nothing gained by trying to do them in "one shot". I see nothing gained by wrapping them in an anonymous pl/sql code to use "dynamic" sql, especially when there is nothing dynamic about them. Even in mssql, that 'one shot' method was really a phantom. You still had to code each statement, and the database still processed them serially.
|
|
|
|
| Re: How can I execute multiple SQL statements in one batch? [message #682660 is a reply to message #682656] |
Thu, 05 November 2020 10:15 |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Oracle SQL interface does not accept several SQL commands in one statement.
Thus if you want to do it in one call you have to use PL/SQL which does not directly accept DDL statements and provides the EXECUTE IMMEDIATE command for this.
What about a feedback in your previous topics?
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
