CODEWITHSUNDEEP
c#oracle-database
William
William

Reputation: 502

Using global temporary table inside stored procedure in C#

I want to use a global temporary table inside my procedure in Oracle. To accomplish that, I've created a global temporary table:

CREATE GLOBAL TEMPORARY TABLE temp_test
(id int)
ON COMMIT PRESERVE ROWS;

And I have created a procedure too:

CREATE OR REPLACE PROCEDURE PROC_TEST ( p_recordset OUT SYS_REFCURSOR) AS 
BEGIN
OPEN p_recordset FOR  
 SELECT * FROM temp_test;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  NULL;
   WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
     RAISE;    
END PROC_TEST;

When I execute the procedure inserting rows in my temporary table, it works normally:

INSERT INTO temp_test (id) values(1);
INSERT INTO temp_test (id) values(2);
INSERT INTO temp_test (id) values(3);
INSERT INTO temp_test (id) values(4);
INSERT INTO temp_test (id) values(5);
INSERT INTO temp_test (id) values(6);
INSERT INTO temp_test (id) values(7);
INSERT INTO temp_test (id) values(8);
INSERT INTO temp_test (id) values(9);
INSERT INTO temp_test (id) values(10);
INSERT INTO temp_test (id) values(11);

var c refcursor;
execute proc_test(:c);
print c;

But when I run it in a C# application, it doesn't return any rows in this procedure, through this code:

using (OracleConnection connection = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyContext"].ToString()))
            {
                connection.Open();
                OracleCommand command = connection.CreateCommand();

                command.CommandText = "DELETE FROM temp_test";
                command.ExecuteNonQuery();

                for (int i = 0; i < 10; i++)
                {
                    command.CommandText = string.Format("INSERT INTO TEMP_INT_1(ID_INT) VALUES ({0})", i);
                    command.ExecuteNonQuery();
                }

                command.CommandText = "PROC_TEST";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new OracleParameter("p_recordset", OracleType.Cursor)).Direction = ParameterDirection.Output;

                OracleDataAdapter adapter = new OracleDataAdapter(command);
                DataSet ds = new DataSet();
                adapter.Fill(ds);



                connection.Close();
            }

What should I do to return these rows properly in my C# application?

Upvotes: 2

Views: 1295

Answers (1)

William
William

Reputation: 502

I was looking for insert rows in TEMP_INT_1 table and tried to select the TEMP_TEST table, that why it wasn't working.

Upvotes: 1

Related Questions