Insert new row into Oracle database with C#

Question

I want to perform CRUD operations via C#. Obviously, I'm very new to this. I managed to delete the record, but I am having difficulty opening a new record. There is procedure for inserting new row in PL/SQL. I tried to write this procedure with C#. I think I'm creating the attr parameter incorrectly.

These are codes in PL/SQL for inserting new record. There is no problem here.

DECLARE
   a_ VARCHAR2(32000) := NULL; --p0
   b_ VARCHAR2(32000) := NULL; --p1
   c_ VARCHAR2(32000) := NULL; --p2
   d_ VARCHAR2(32000) := 'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30); --p3
   e_ VARCHAR2(32000) := 'DO'; --p4

BEGIN


ifsapp.PURCHASE_REQUISITION_API.NEW__( a_ , b_ , c_ , d_ , e_ );


EXCEPTION 
WHEN OTHERS THEN 
rollback;

raise;

END;

DECLARE
BEGIN

    COMMIT;

END;

This the New__ procedure belongs to ifsapp.PURCHASE_REQUISITION_API.NEW__ for inserting a new row:

PROCEDURE New__ (
   info_       OUT    VARCHAR2,
   objid_      OUT    VARCHAR2,
   objversion_ OUT    VARCHAR2,
   attr_       IN OUT VARCHAR2,
   action_     IN     VARCHAR2 );

This is my C# code. And I can not insert a new row. It get errors as shown below:

public void InserNewRecord()
{
    SetConnection(); // Sets the connection con = new OracleConnection
    cmd = new OracleCommand();
    cmd.Connection = con;
    cmd.CommandText = "ifsapp.PURCHASE_REQUISITION_API.NEW__";
    cmd.CommandType = CommandType.StoredProcedure;

    string attr_ = "'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30);";
    string action_ = "DO";

    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "info_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "objid_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "objversion_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "attr_",
        Value = attr_,
        Direction = ParameterDirection.InputOutput
    });
    cmd.Parameters.Add("action_", OracleDbType.Varchar2).Value = action_;

    int x=cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
}

This is the error:

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-20111: UserAllowedSite.RECNOTEXIST:

Site not used for user IFSAPP

ORA-06512: at "IFSAPP.ERROR_SYS", line 203

ORA-06512: at "IFSAPP.USER_ALLOWED_SITE_API", line 610

ORA-06512: at "IFSAPP.PURCHASE_REQUISITION_API", line 680

ORA-06512: at "IFSAPP.PURCHASE_REQUISITION_API", line 1117

Answer 1

Do not expect bind parameters to be evaluated as SQL expressions; they are treated as literal values so when you use:

string attr_ = "'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30);";

cmd.Parameters.Add(
  new OracleParameter()
  {
    ParameterName = "attr_",
    Value = attr_,
    Direction = ParameterDirection.InputOutput
  }
);

Then the value passed to the parameter is literally 'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30); and it does NOT evaluate || as a string concatenation operator nor CHR(31) as the 31^st ASCII character, etc.

Pass the string as:

string attr_ = "CONTRACT\u001FBISAM\u001EREQUISITIONER_CODE\u001FIFSAPP\u001EORDER_CODE\u001F1\u001E";

or:

string attr_ = "CONTRACT" + ((char) 31) +
               "BISAM" + ((char) 30) +
               "REQUISITIONER_CODE" + ((char) 31) +
               "IFSAPP" + ((char) 30) +
               "ORDER_CODE" + ((char) 31) +
               "1" + ((char) 30);