1 Reply Latest reply: Jan 3, 2013 6:11 AM by Tridus RSS

    Argument type mismatch when supplying BFILE parameter to stored procedure

    982629
      Hello, I have a big problem with the following procedure:

      create or replace procedure INSERTARFOTOEMPLEADO (EMPP_ID NUMBER,EMPLEADOS IN BFILE) AS
      l_bfile BFILE;
      l_blob BLOB;
      begin
      select EMP_FOTO into l_blob from EMPLEADO_ROH where EMP_ID=EMPP_ID for update;

      l_bfile := EMPLEADOS;
      DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
      DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
      DBMS_LOB.fileclose(l_bfile);
      commit;
      end;

      And my c# code is

      OracleConnection ConexionOracle = new OracleConnection("Data Source = XE; User ID=RoyalOpera; Password=1;");
      ConexionOracle.Open();
      String bfile = "BFILENAME('EMPLEADOS','VANESAKARINA.JPG')";
      OracleCommand ComandOracle4 = new OracleCommand("INSERTARFOTOEMPLEADO",ConexionOracle);
      ComandOracle4.CommandType = CommandType.StoredProcedure;
      ComandOracle4.Parameters.Add("EMMP",2);
      ComandOracle4.Parameters.Add("EMPLEADOS",bfile);
      ComandOracle4.ExecuteNonQuery();
      ConexionOracle.Close();


      And my error is

      ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'INSERTARFOTOEMPLEADO'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      Please Help!!! Thanks.. Gracias
      Isaias
        • 1. Re: Argument type mismatch when supplying BFILE parameter to stored procedure
          Tridus
          Well, one problem is here:
          ComandOracle4.Parameters.Add("EMMP",2);
          ComandOracle4.Parameters.Add("EMPLEADOS",bfile);
          You're not setting a type on these, so Oracle doesn't know what it is. You can specify that as part of adding the parameters
          ComandOracle4.Parameters.Add("EMMP", OracleDbType.Decimal, 2, ParameterDirection.Input); // (Note that this might be Int32 or Int64 as well, I can't tell from the provided information)
          ComandOracle4.Parameters.Add("EMPLEADOS",OracleDbType.BFile, bfile, ParameterDirection.Input);
          [url http://docs.oracle.com/cd/E11882_01/win.112/e18754/OracleParameterCollectionClass.htm#autoId14]You can see this call in the documentation, which has a link to the list of types as well.