I want to execute multiple statements in a single transaction in oracle. Following are my queries:
Create table temp_table as Select * from table;
SELECT * FROM temp_table d;
drop table temp_table ;
I am using sql comment text in asp.net
I am using executenonquery command in asp.net.
I'm not sure if you're asking how you can execute 3 statements at once, or how to execute 3 statements in the same transaction.
To execute 3 at once, you'd need to wrap them in an anonymous block.
To execute 3 statements in a transaction, you'd use an OracleTransaction object to control the commit.
In your case though, you can't execute those 3 statements in a single transaction, as two of them are DDL so will force a commit.
Hope it helps,
Thanks for the reply. I want to execute 3 statement at once in a sequential manner. I put all the statements inside a BEGIN and END like
Create temp table;
but it is throwing error that
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
You can't issue ddl directly in a procedure, you'd need to use execute immediate for that: PLS-00103
Sorry that didn't jump out at me initially.
I'm really not sure why you'd want to issue those three inside a block in the first place though. Where is the data going to "go" when you select it? You can't return it as a ref cursor because the table is already dropped by the time the data will be fetched from the cursor.
You may want to look into use Global Temporary Tables instead. http://docs.oracle.com/cd/E11882_01/server.112/e10595/tables003.htm#i1006400
I am not using stored procedure, I am directly executing the query. I tried with Global temp table and execute immediate also, it is not working. This was working in sybase and now I need to change to oracle.
This is what I tried now and I got the error saying table/view does not exists.
EXECUTE IMMEDIATE 'Create GLOBAL TEMPORARY TABLE temp_table ON COMMIT DELETE ROWS AS
a as x,
b as y,
c as a,
d as y
SELECT * FROM im_temp_table d
EXECUTE IMMEDIATE 'drop table temp_table';
Here's an example that executes 3 statements;
begin insert into foo values(1); insert into foo values(2); insert into foo values(3); end;
The block doesn't compile because temp_table doesn't exist at the point you're trying to compile the anonymous block. I'd recommend re-reading the doc link and forum link provided to get a better understanding of how temp tables work, as it's simply different with Oracle. You don't create Oracle temporary tables on the fly; you create them ahead of time and then just use them. The data itself is already specific to a particular session; you don't create and drop the table each time.
Also, you can't just "select * from table" in plsql. The results have to GO SOMEWHERE. Usually you'd either open a cursor and process it in the block, or send out a ref cursor if you want to send the data to a client side app. The ref cursor data wouldn't actually be fetched until the block completes though, so you'd need to use ON COMMIT PRESERVE ROWS, which would also mean you'd need to clean up the data yourself (delete the data from the table when you're done with it).