5 Replies Latest reply: Sep 10, 2010 3:14 PM by 687520 RSS

    Manipulating data from an Oracle temporary table in VB.Net

    687520
      Hello,

      I have a VB.Net application that populates an Oracle 11g global temporary table (using 'ON COMMIT PRESERVE ROWS'). I can see data in the temporary table by loading it into a grid in my VB.Net application and everything looks correct.
      However, when I call an Oracle stored procedure from VB.Net that would manipulate the data in this temporary table, the stored procedure reports that my temporary table is empty.

      I understood that data in an Oracle global temporary table should be visible to all sessions so I'm not sure why this is happening and what the solution would be.
      I am using Visual Studio 2010 and installed ODAC 11.2.0.1.2
      Please advise.

      Regards,
      M. Rusu

      Edited by: user7047382 on Sep 7, 2010 12:42 PM

      Edited by: user7047382 on Sep 7, 2010 12:44 PM

      Edited by: user7047382 on Sep 7, 2010 1:27 PM
        • 1. Re: Manipulating data from an Oracle temporary table in VB.Net
          gdarling - oracle
          Hi,

          "+I understood that data in an Oracle global temporary table should be visible to all sessions+ " ... That is not correct. The data is only visible in the session that inserted it.

          One thing to watch out for too is that ODP has pooling by default, and if you ON COMMIT PRESERVE ROWS, putting a connection back in the pool doesnt clear out the temporary table so data may be there the next time you call con.Open (or, may not, depending on what connection you get).

          I think the usual interaction with temp tables is to start a transaction, use a single connection to do everything with it you need to do, then commit or rollback the txn to clear out the table.

          Hope it helps, corrections/comments welcome
          Greg
          • 2. Re: Manipulating data from an Oracle temporary table in VB.Net
            687520
            Thank you for your reply Greg. Yes, the data in temporary table is visible through the VB session and I can see and query it using the connection object.
            If I use the connection object in VB.Net, then the following SQL statement:
            SELECT * FROM tmp_table
            returns approx 500,000 rows.
            If I call a stored procedure (within the same session in VB.Net), then the same statement written in the stored procedure (I.e. SELECT * FROM tmp_table) returns 0 records.
            This is the problem. Maybe I should use a different approach?

            Regards,
            M. Rusu

            Edited by: user7047382 on Sep 7, 2010 8:06 PM

            Edited by: user7047382 on Sep 7, 2010 8:06 PM

            Edited by: user7047382 on Sep 7, 2010 8:07 PM

            Edited by: user7047382 on Sep 7, 2010 8:07 PM
            • 3. Re: Manipulating data from an Oracle temporary table in VB.Net
              gdarling - oracle
              What are you doing in the procedure, exactly? Iterating through a cursor? Opening a ref cursor? etc.

              I tried the following, and it worked as expected for me anyway. Perhaps you can break it and send it back?

              Hope it helps,
              Greg
              /*
              create global temporary table gtt(co1 varchar2(4000));
              create or replace function count_gtt return number as
              v1 number;
              begin
                select count(*) into v1 from gtt;
                return v1;
              end;
              /
              */
              
              using System;
              using System.Data;
              using Oracle.DataAccess.Client;
              
              class Program
              {
                  static void Main(string[] args)
                  {
                      string constr = "data source=orcl;user id=scott;password=tiger";
                      using (OracleConnection con = new OracleConnection(constr))
                      {
                          con.Open();
                          OracleTransaction txn = con.BeginTransaction();
                          string strsql = "insert into gtt values('foo')";
                          using (OracleCommand cmd = new OracleCommand(strsql, con))
                          {
                              cmd.ExecuteNonQuery();
              
                              cmd.CommandText = "count_gtt";
                              cmd.CommandType = CommandType.StoredProcedure;
                              cmd.Parameters.Add("", OracleDbType.Int32);
                              cmd.Parameters[0].Direction = ParameterDirection.ReturnValue;
                              cmd.ExecuteNonQuery();
                              Console.WriteLine(cmd.Parameters[0].Value.ToString());
                             
                          }
                          txn.Rollback();
                      }
                  }
              }
              • 4. Re: Manipulating data from an Oracle temporary table in VB.Net
                687520
                Thanks for the code Greg.
                There is a small change I need to do in order to be identical to my scenario.
                I don't insert data into temporary table through the connection object in VB as you do here. I insert data by calling another stored procedure.
                I shall try both cases. Yours may work correctly but mine does not seem to work as expected...

                Regards,
                M. Rusu
                • 5. Re: Manipulating data from an Oracle temporary table in VB.Net
                  687520
                  Greg:

                  I tried both scenarios and they both work correctly. Data in temporary table can be seen through a stored procedure.
                  The problem was a bug in my database, a mismatch between the temp table and the production table. The error was not reported into VB.Net due to an incomplete error handler.
                  Anyways, everything works now as expected.

                  Thanks again for your help,
                  M. Rusu