This discussion is archived
5 Replies Latest reply: Sep 10, 2010 1:14 PM by 687520 RSS

Manipulating data from an Oracle temporary table in VB.Net

687520 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points