Home » SQL & PL/SQL » SQL & PL/SQL » How can I execute multiple SQL statements in one batch? (Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0 Windows)
How can I execute multiple SQL statements in one batch? [message #682652] Thu, 05 November 2020 07:42 Go to next message
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
and it executes without questions asked.

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 Go to previous messageGo to next message
John Watson
Messages: 8547
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 #682654 is a reply to message #682653] Thu, 05 November 2020 08:15 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Sorry, I am lost. What does CREATE SCHEMA have to do with my question?
I only want to batch several statements into one shot.
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 Go to previous messageGo to next message
John Watson
Messages: 8547
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 #682656 is a reply to message #682655] Thu, 05 November 2020 08:55 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
I 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
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 Go to previous messageGo to next message
EdStevens
Messages: 1316
Registered: September 2013
Senior Member
Darth Waiter wrote on Thu, 05 November 2020 08:55
I 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 Go to previous message
Michel Cadot
Messages: 67850
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?

Previous Topic: How can I model a column in a new table after a column in an existing table?
Next Topic: ORA-00979: not a GROUP BY expression
Goto Forum:
  


Current Time: Sun May 16 12:26:19 CDT 2021