4 Replies Latest reply: May 8, 2011 5:02 AM by William Robertson RSS

    How to execute multiple sql query in one time?

    860159
      HI
      I'm trying to convert my sql project In Oracle. In sql i could run multiple select statement/query in once and they return in multiple table result respectively. but in oracle its not executed.
      like:
      sqlQry := "Select * from abc; select * from qwe; select * from kkk; select * from xyz"
      its return 4 table abc, qwe,kkk,xyz to my dataset result

      how it is possible in oracle 10g
        • 1. Re: How to execute multiple sql query in one time?
          sb92075
          .

          Edited by: sb92075 on May 7, 2011 8:00 PM
          • 2. Re: How to execute multiple sql query in one time?
            JustinCave
            A direct conversion probably isn't possible.

            You could, of course, send all four requests separately.
            If you are doing something akin to joining the four results in your client application, however, it would be far more efficient to simply join the four tables in a single SQL statement.
            You could also replace this with a single procedure that has four OUT parameters that are all SYS_REFCURSORs.

            Justin
            • 3. Re: How to execute multiple sql query in one time?
              Billy~Verreynne
              Saten Chamoli wrote:

              I'm trying to convert my sql project In Oracle. In sql i could run multiple select statement/query in once and they return in multiple table result respectively. but in oracle its not executed.
              like:
              sqlQry := "Select * from abc; select * from qwe; select * from kkk; select * from xyz"
              its return 4 table abc, qwe,kkk,xyz to my dataset result
              That is pretty much a hack - there are no ANSI SQL standards supporting this syntax. It makes no sense either.

              If you want to combine 4 data sets, there are the UNION and UNION ALL set commands.

              If you want to create 4 cursors with a single call, then use the following (anonymous PL/SQL block) call:
              begin
                open :c1 for select * from abc; 
                open :c2 for select * from qwe; 
                open :c3 for select * from kkk; 
                open :c4 for select * from xyz;
              end;
              Bind 4 client cursor variables to the ref cursors c1 to c4 and make the call to Oracle.

              And I suggest that you read up on Oracle concepts and fundamentals as your approach with you "sql project" shows ignorance in this regard.
              • 4. Re: How to execute multiple sql query in one time?
                William Robertson
                Also I know SQL Server users call it "SQL" for short, but that can cause confusion once you are dealing with more than one database product. We have SQL in Oracle as well.