Oracle Developer Tools for Visual Studio

Hi,

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,
Greg

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
BEGIN
Create temp table;
Select query;
Drop table;
END;
but it is throwing error that


Error report:
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.
*Action:

Divya

You can't issue ddl directly in a procedure, you'd need to use execute immediate for that:
310955
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

Greg

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.

BEGIN
EXECUTE IMMEDIATE 'Create GLOBAL TEMPORARY TABLE temp_table ON COMMIT DELETE ROWS AS
Select
a as x,
b as y,
c as a,
d as y
from
tablea
Where
z= 1
and q=''22-SEP-12''
group by
a';
SELECT * FROM im_temp_table d
EXECUTE IMMEDIATE 'drop table temp_table';
END;

Dear qdarling,

Could you mind give an example on plsql procedure "To execute 3 at once, you'd need to wrap them in an anonymous block."

Best Regards,
LinXianHan

SigCle ,
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;

923354,
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).

Corrections/comments welcome.
Greg

Thanks.