OLEDB Provider를 사용해서 Oracle Stored Procedure(pl sql) 실행하기

보안 관련 공부를 하다가 PL SQL를 작성해서 어플리케이션을 개발하면

SQL 인젝션을 예방할 수 있다고 해서 공부하게 되었다.

 

OLEDB를 사용해서 connection을 맺고 파라미터를 주고 받아서 데이타를 가져오는 예제가 필요한 분들에게

도움이 되었으면 ....

 

ORACLE STORED PROCEDURE : EMPNO를 입력새허 SAL를 받아오는 예제

CREATE OR REPLACE
PROCEDURE GetEmpSalary(p_in IN varchar2, p_out OUT emp.SAL%TYPE) is
      
  v_empsal emp.SAL%TYPE; 
 
BEGIN

  DBMS_OUTPUT.PUT_LINE('emp no  : ' ||p_in);
  --DBMS_OUTPUT.PUT_LINE('p out : ' ||p_out);
 
  SELECT sal
INTO v_empsal
FROM emp
WHERE empno = p_in ;
 
  --DBMS_OUTPUT.PUT_LINE('Query Result : ' ||v_empsal);
  p_out := v_empsal;
  DBMS_OUTPUT.PUT_LINE('salary : ' ||p_out);
 
 EXCEPTION
       WHEN DUP_VAL_ON_INDEX
        THEN DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러 발생');
       WHEN TOO_MANY_ROWS
        THEN DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러 발생');
       WHEN NO_DATA_FOUND
        THEN DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러 발생');
       WHEN OTHERS
        THEN DBMS_OUTPUT.PUT_LINE('기타 에러 발생');

END ;

 

위 Stored Procedure를 오렌지에서 실행한 결과 : Salary를 잘 받아오는 것을 확인

 

  

 

C# OLEDB로 CONNECTION, PARAMETER, COMMAND를 작성하여 결과를 받아오는 코드

 

    private void GetEmpSalary(string EmpNO)
    {
        int Salary = 0;

        String sConnectInfo = ConfigurationManager.AppSettings["TNS name : connection 정보"];
        OleDbConnection conn = new OleDbConnection(sConnectInfo);

        try
        {
            conn.Open();

            using (var command = new OleDbCommand("GetEmpSalary", conn))
            {
                command.CommandType = CommandType.StoredProcedure;

 

                OleDbParameter p_in = new OleDbParameter("p_in", OleDbType.VarChar);
                p_in.Direction = ParameterDirection.Input;
                p_in.Value = EmpNO;
                //p_in.Size = EmpNO.Length;// OleDbParameter의 size가 0으로 설정되어 Error가 나는 bug가 있는 것 같다.

                OleDbParameter p_out = new OleDbParameter("p_out", OleDbType.Numeric);
                p_out.Direction = ParameterDirection.Output;
                p_out.Value = 0;

 

                command.Parameters.Add(p_in);
                command.Parameters.Add(p_out);

                command.ExecuteNonQuery();
                Salary = int.Parse(p_out.Value.ToString());
            }

        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            conn.Close();
        }
    }

 

아래와 같이 결과 값을 받아 오는 것을 확인 할 수 있다.