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

what I have missed ?
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;
                }
            }

DB complains about F_INS_ORDER_DATA_DETAILS not existing, have you double checked on your DB it exists and does not have any typo? — Gusman
– Gusman, Commented Feb 17, 2017 at 15:31
@Gusman yes I did and after your comment I did copy it and paste it — sam
– sam, Commented Feb 17, 2017 at 15:43

Zohar Elkayam · Accepted Answer · 2017-02-28 08:43:40Z

2

+50

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.

answered Feb 28, 2017 at 8:43

Zohar Elkayam

3582 silver badges5 bronze badges

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

1 Comment

sam Over a year ago

you are right man I am catching return value from first function F_INS_ORDER_DATA and not catching return value from second function F_INS_ORDER_DATA_DETAILS.. Thanks a lot you deserve the 50 of population

Damith · Accepted Answer · 2017-02-17 16:29:51Z

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;
    }
}

same issue but now after moving the cmd.ExecuteNonQuery(); from the last line to the position you suggest the first function will insert the record and commit it even the second function not able to insert a record ... that way I place it at last line so if the second function fails the first function wil do nothing in database

Collectives™ on Stack Overflow

Executing multiple oracle function returns error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related