How to execute Oracle stored procedure with User Defined DataTypes Parameters in Oracle from .NET Front-End

Question

I am using Oracle as backend and C# as frontend.

Oracle Package Declaration is as follows:

CREATE OR REPLACE PACKAGE SamplePackage IS

TYPE t_number IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

PROCEDURE SampleProc(run_no IN t_number, Id IN t_number);

END;


CREATE OR REPLACE PACKAGE BODY SamplePackage IS

  PROCEDURE SampleProc(run_no IN t_number, Id IN t_number) IS

    l_cnt      NUMBER := 0;
  
  BEGIN
  
    FOR i IN 1 .. Id.count
    LOOP
    
      INSERT INTO SampleSchema.SampleTable
        (run_no,
         Id)
      VALUES
        (run_no(i),
         Id(i));
    
      -- commit every 1000 rows
      l_cnt := l_cnt + 1;
    
      IF (MOD(l_cnt,
              1000) = 0) THEN
        COMMIT;
      END IF;
    
    END LOOP;
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001,
                              'SamplePackage.SampleProc: ' || SQLERRM || dbms_utility.format_error_backtrace);
  END SampleProc;

END;

The frontend C# code is as follows:

string ConnectionString = ConfigurationManager.ConnectionStrings["oracleConnection"].ConnectionString.ToString();
        
using (var connection = new OracleConnection(ConnectionString))
{
    connection.Open();

    double[] run_no = new double[2];
    double[] Id = new double[2];

    run_no[0] = 10;
    run_no[1] = 12;

    Id[0] = 100;
    Id[1] = 120;

    OracleParameter ora_run_no = new OracleParameter();
    ora_run_no.ParameterName = "run_no";
    ora_run_no.OracleDbType = OracleDbType.Double;
    ora_run_no.Direction = ParameterDirection.Input;
    ora_run_no.UdtTypeName = "t_number";
    ora_run_no.Value = run_no;

    OracleParameter ora_p_quote_id = new OracleParameter();
    ora_Id.ParameterName = "Id";
    ora_Id.OracleDbType = OracleDbType.Double;
    ora_Id.Direction = ParameterDirection.Input;
    ora_Id.UdtTypeName = "t_number";
    ora_Id.Value = Id;

    OracleCommand cmdInsert = new OracleCommand();
    cmdInsert.Connection = connection;
    cmdInsert.CommandType = CommandType.StoredProcedure;
    cmdInsert.CommandText = "SampleSchema.SamplePackage.SampleProc";

    cmdInsert.BindByName = true;
    cmdInsert.ArrayBindCount = Id.Length;

    cmdInsert.Parameters.Add(ora_run_no);
    cmdInsert.Parameters.Add(ora_Id);

    cmdInsert.ExecuteNonQuery();
}

There is some confusion here. When I execute the .NET code, it throws error at ExecuteNonQuery as below

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SAMPLEPROC'

ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

In the Oracle backend, I am using t_number user-defined datatype. The only thing I need to know is how to handle it as an array and successfully execute from C#.

Littlefoot · Accepted Answer · 2021-05-25 10:51:54Z

1

I don't know anything about tools you're using, but - as type is declared within the package, I'd say that this:

ora_Id.UdtTypeName = "t_number";

should've been

ora_Id.UdtTypeName = "SampleSchema.SamplePackage.t_number";

just like you used for the procedure itself.

Other than that, as type you use is a table of numbers, you could try to use Oracle's built-in type: sys.odcinumberlist instead.

answered May 25, 2021 at 10:51

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

4 Comments

Ch. Over a year ago

To execute successfully should I need to create temp table for those 2 fields each and change the UdtTypeName Or current double[] array works? Because the Package and Procedure modifications using sys.odcinumberlist takes long process it is handled by other team. It still throws the same error.

Littlefoot Over a year ago

Sorry; as I said, I don't know tools you use so I can't comment on that. I hope you'll manage to make it work.

Ch. Over a year ago

I am using ODP.NET for this and still I am stuck.

Ch. Over a year ago

I am able to use sys.odcinumberlist successfully. But I need it for t_number. When I specify ora_Id.UdtTypeName as t_number it is not recognizing. What is the way out?

Collectives™ on Stack Overflow

How to execute Oracle stored procedure with User Defined DataTypes Parameters in Oracle from .NET Front-End

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related