7 Replies Latest reply: Mar 28, 2012 8:06 AM by 926357 RSS

    How to execute multiple sql statements in oracle?

    926357
      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.


      Thanks,
      Divya
        • 1. Re: How to execute multiple sql statements in oracle?
          gdarling - oracle
          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
          • 2. Re: How to execute multiple sql statements in oracle?
            926357
            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
            • 3. Re: How to execute multiple sql statements in oracle?
              gdarling - oracle
              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

              Greg
              • 4. Re: How to execute multiple sql statements in oracle?
                926357
                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;
                • 5. Re: How to execute multiple sql statements in oracle?
                  SigCle
                  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
                  • 6. Re: How to execute multiple sql statements in oracle?
                    gdarling - oracle
                    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