How to call Oracle stored procedure as command text using c#.net core 6.0

Question

Hi,

I have a Multi Line Text box where user copies the PL/SQL syntex for calling the stored procedure as below:

Step1: Oracle table structure

Step2: Created a stored procedure

create or replace NONEDITIONABLE PROCEDURE GetUserIdByUserName(User_Name1 in varchar2,User_Id1 OUT NUMBER)  
AS  
BEGIN  
select user_id into User_Id1 from EMP_CONTACT_INFO where user_name=User_Name1 and ROWNUM=1;  
END;  

Step3: User pass the below PL/SQL block in Multi Line Text box to call the above stored procedure using c#.net core 6.0

DECLARE  
  USER_NAME1 VARCHAR2(200);  
  USER_ID1 NUMBER;  
BEGIN  
  USER_NAME1 := 'John';  
  
  GETUSERIDBYUSERNAME(  
    USER_NAME1 => USER_NAME1,  
    USER_ID1 => USER_ID1  
  );  
  :USER_ID1 := USER_ID1;  
END;  

Note: PL/SQL block passing in Multi Line textbox is generic. Means, User can call any stored procedure of PL/SQL block.

Step4: I am using below code snippet to execute the PL/SQL block.

using (OracleConnection conn = new OracleConnection(_connectionString))  
            {  
                await conn.OpenAsync();  
                OracleCommand objCmd = new OracleCommand(sqlQuery, conn);  
                objCmd.CommandType = CommandType.Text;  
                OracleDataAdapter da = new OracleDataAdapter(objCmd);  
                da.Fill(ds);  
           }  

But it throws below error

Answer 1

your pl/sql block has an output binding parameter (:USER_ID1) , but you did not add any binding output parameters to objCmd

using (OracleConnection conn = new OracleConnection(_connectionString)) 
             { 
                 await conn.OpenAsync(); 
                 OracleCommand objCmd = new OracleCommand(sqlQuery, conn); 
                 objCmd.CommandType = CommandType.Text; 
                 OracleParameter param1 = objCmd.Parameters.Add("USER_ID1", OracleDbType.Number); 
                 param1.Direction = ParameterDirection.Output; 
                 OracleDataAdapter da = new OracleDataAdapter(objCmd); 
                 da.Fill(ds); 
            } 

I realize you want a generic, so you will need to write a parser, that figures out the parameters names and whether they are an input or output. You will probably need to query the database to get the datatypes, or not allow parameter binding.