Need to execute a procedure and execute a select query against a temp table together in one script in oracle using ADO.net

Question

Need to execute a procedure and execute a select query against a temp table together in one script in oracle using ADO.net. I am a starter to oracle. This is my code:

string queryString = "DECLARE PI_ACCOUNTS_COUNT NUMBER; pstdh account_dataheader; BEGIN SGC.EGOV.GET_ACOUNTS_DEBT( 7660774, 'CPR', '530806835', PI_ACCOUNTS_COUNT, pstdh );EXECUTE IMMEDIATE 'select * from SGC.EGOV_ARR_ACCOUNTS where rownum = 1';END; ";

Then I used execute reader to execute it

I am not getting any errors executing this and also there is no result.

I was able to execute the procedure alone and get the output parameters like this.

            using (OracleConnection conn = new OracleConnection(connection))  // C#
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SGC.EGOV.GET_ACOUNTS_DEBT";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("pl_gsn", OracleDbType.Int64).Value = 7660774;
                cmd.Parameters.Add("pc_id_type", OracleDbType.Varchar2).Value = "asd";
                cmd.Parameters.Add("pc_doc_id", OracleDbType.Varchar2).Value= "asd";
                cmd.Parameters.Add("PI_ACCOUNTS_COUNT",OracleDbType.Int32).Direction = ParameterDirection.Output;
                OracleParameter objParam = new OracleParameter();
                objParam.OracleDbType = OracleDbType.Object;
                objParam.Direction = ParameterDirection.Output;
                objParam.UdtTypeName = "ACCOUNT_DATAHEADER";
                objParam.Value = pst_dataheader;
                cmd.Parameters.Add(objParam);

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    response.AccountsCount = Convert.ToInt32(cmd.Parameters["PI_ACCOUNTS_COUNT"].Value.ToString());
                    pst_dataheader = (ACCOUNT_DATAHEADER)objParam.Value;
                    response.AccountsCount = pst_dataheader.ACCOUNTS_COUNT;
                    response.ErrorCode= pst_dataheader.ERRCODE;`
                    response.TotalBillCount = pst_dataheader.TOTALBILLCOUNT;

                }
                catch (Exception)
                {

                    throw;
                }

Also, I get results when executing the below statement in sql developer plus: set serveroutput on

Any kind of help is highly appreciated. Thanks in Advance folks..

Pavel Gatnar · Accepted Answer · 2015-03-17 13:32:48Z

0

replace all values in your queryString you want to be passed as parameters by bind variables :name (e.g. :pl_gsn instead of 7660774)
create command OracleCommand cmd = new OracleCommand(queryString, conn);
add parameters to the cmd in the order of :name bind variables (e.g. cmd.Parameters.Add("pl_gsn", OracleDbType.Int64, 7660774, ParameterDirection.Input);)
execute

answered Mar 17, 2015 at 13:32

Pavel Gatnar

4,0532 gold badges22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need to execute a procedure and execute a select query against a temp table together in one script in oracle using ADO.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related