Executing multiple oracle function returns error

Question

as you can see I am trying to execute two oracle function using c# the first function F_INS_ORDER_DATA return value that will be used as in parameters with second but when the execution of second function F_INS_ORDER_DATA_DETAILS I am getting below error

I test both function in toad and they are working fine so

1. what I have missed ?
2. please if there is any improvement to my code or better ways to do it I will be really thankful for any suggestion

ora-06550: line 1, column 7:

PLS-00221: 'F_INS_ORDER_DATA_DETAILS' is not a procedure or is undefined

ora-06550: line 1, column 7:

pl/sql: statement ignored

            string connstr = @"Data Source=orcl; User Id=user; password=pwd;";
            string cmdtxt01 = @"F_INS_ORDER_DATA";
            string cmdtxt02 = @"F_INS_ORDER_DATA_DETAILS";
            int INVOICE_ID;

            using (OracleConnection conn = new OracleConnection(connstr))
            using (OracleCommand cmd = new OracleCommand(cmdtxt01, conn))
            {
                try
                {
                    conn.Open();

                    cmd.CommandText = cmdtxt01;
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Clear();

                    cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int32, ParameterDirection.ReturnValue);

                    cmd.Parameters.Add(new OracleParameter(":P_CUSTOMER_ID", OracleDbType.Int64)).Value = TB_CUSTOMER_ID.Text;
                    cmd.Parameters.Add(new OracleParameter(":P_ORDER_NOTE", OracleDbType.Varchar2)).Value = TB_ORDER_NOTE.Text;
                    cmd.Parameters.Add(new OracleParameter(":P_CREATED_BY", OracleDbType.Varchar2)).Value = "SYSTEM";

                    cmd.ExecuteNonQuery();

                    TB_INVOICE_ID.Text = cmd.Parameters[":vORDER_ID"].Value.ToString();
                    int.TryParse(TB_INVOICE_ID.Text, out INVOICE_ID);

                    cmd.CommandText = cmdtxt02;
                    cmd.CommandType = CommandType.StoredProcedure;

                    foreach (DataGridViewRow Row in DGV_INVOICE.Rows)
                    {
                        cmd.Parameters.Clear();

                        cmd.Parameters.Add(new OracleParameter(":P_ORDER_ID", OracleDbType.Int32)).Value = INVOICE_ID;
                        cmd.Parameters.Add(new OracleParameter(":P_PRODUCT_ID", OracleDbType.Int64)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_PRODUCT_ID"].Index].Value;
                        cmd.Parameters.Add(new OracleParameter(":P_UNIT_PRICE", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_UNIT_PRICE"].Index].Value;
                        cmd.Parameters.Add(new OracleParameter(":P_QUANTITY", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_QUANTITY"].Index].Value;
                        cmd.Parameters.Add(new OracleParameter(":P_DISCOUNT", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_DISCOUNT"].Index].Value;
                        cmd.Parameters.Add(new OracleParameter(":P_ORDER_STATUS", OracleDbType.Varchar2)).Value = '1';
                        cmd.Parameters.Add(new OracleParameter(":P_ITEM_NOTE", OracleDbType.Varchar2)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_ITEM_NOTE"].Index].Value;
                        cmd.Parameters.Add(new OracleParameter(":P_CREATED_BY", OracleDbType.Varchar2)).Value = "SYSTEM";

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception EX)
                {
                    MessageBox.Show(EX.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }

Answer 1

Oracle distinguish between procedures and functions.

Procedures are stored code that do not return a value. Function are stored code that do return a value. The way you use them (in Oracle) is a bit different.

Since the database complains that F_INS_ORDER_DATA_DETAILS is not a procedure, it might be a function (with a return value) but you're not catching it like you do in the first call:

cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int32, ParameterDirection.ReturnValue);

So make sure you're either using a stored procedure:

create or replace procedure F_INS_ORDER_DATA_DETAILS ...

or catching the return value from the function.

Answer 2

using (OracleConnection conn = new OracleConnection(connstr))
using (OracleCommand cmd = new OracleCommand(cmdtxt01, conn))
{
    try
    {
        conn.Open();

        cmd.CommandText = cmdtxt01;
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(":vORDER_ID", OracleDbType.Int32, ParameterDirection.ReturnValue);

        cmd.Parameters.Add(new OracleParameter(":P_CUSTOMER_ID", OracleDbType.Int32)).Value = TB_CUSTOMER_ID.Text;
        cmd.Parameters.Add(new OracleParameter(":P_ORDER_NOTE", OracleDbType.Varchar2)).Value = TB_ORDER_NOTE.Text;
        cmd.Parameters.Add(new OracleParameter(":P_CREATED_BY", OracleDbType.Varchar2)).Value = "SYSTEM";
        //excute first SP
        cmd.ExecuteNonQuery();
        //get the value
        TB_INVOICE_ID.Text = cmd.Parameters[":vORDER_ID"].Value.ToString();
        int INVOICE_ID;
        // parse return value
        int.TryParse(TB_INVOICE_ID.Text, out INVOICE_ID);

        // now set 2nd SP 
        cmd.CommandText = cmdtxt02;
        cmd.CommandType = CommandType.StoredProcedure;

        foreach (DataGridViewRow Row in DGV_INVOICE.Rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new OracleParameter(":P_ORDER_ID", OracleDbType.Int32)).Value = INVOICE_ID;
            cmd.Parameters.Add(new OracleParameter(":P_PRODUCT_ID", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_PRODUCT_ID"].Index].Value;
            cmd.Parameters.Add(new OracleParameter(":P_UNIT_PRICE", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_UNIT_PRICE"].Index].Value;
            cmd.Parameters.Add(new OracleParameter(":P_QUANTITY", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_QUANTITY"].Index].Value;
            cmd.Parameters.Add(new OracleParameter(":P_DISCOUNT", OracleDbType.Int32)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_DISCOUNT"].Index].Value;
            cmd.Parameters.Add(new OracleParameter(":P_ORDER_STATUS", OracleDbType.Varchar2)).Value = '1';
            cmd.Parameters.Add(new OracleParameter(":P_ITEM_NOTE", OracleDbType.Varchar2)).Value = Row.Cells[DGV_INVOICE.Columns["DGV_ITEM_NOTE"].Index].Value;
            cmd.Parameters.Add(new OracleParameter(":P_CREATED_BY", OracleDbType.Varchar2)).Value = "SYSTEM";

            cmd.ExecuteNonQuery();
        }


    }
    catch (Exception EX)
    {
        MessageBox.Show(EX.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }
}