ODP.NET

Hi All,

I'm an ODP developer since year and half and I'm having a continuous problem when passing date from vb.net to oracle procedure but this time I'm sure that i did all things correct but still i'm not getting correct result

the following Function where created inside a package and I'm using the same from my VB program :

CREATE OR REPLACE PACKAGE JOB_OPERATION AS
FUNCTION CHECK_CONTRACTED_BUILDING(TK_DT IN DATE, BLD_CODE IN VARCHAR2) RETURN NUMBER;
END JOB_OPERATION;
/

Package Body :
CREATE OR REPLACE PACKAGE BODY JOB_OPERATION AS
FUNCTION CHECK_CONTRACTED_BUILDING(TK_DT IN DATE, BLD_CODE IN VARCHAR2) RETURN NUMBER
IS

BLD_CD VARCHAR2(10) := BLD_CODE;

TICKET_DATE DATE := TK_DT;
CONT_DOC_NO VARCHAR2(12);
CONT_SYSID NUMBER(12);

CONTRACT_COUNT NUMBER(2);

CURSOR C_GET_CONT_COUNT(T_DT IN DATE,TCK_BLD IN VARCHAR2) IS
SELECT NVL((SELECT COUNT(*)
FROM (
SELECT CONTH_SYS_ID,CONTH_CODE
FROM CONTRACT_HEAD,CONTRACT_SCOPE
WHERE CONTH_SYS_ID = CSCP_CONT_SYSID
AND CONTH_APPR_STATUS = 'Y'
AND CONTH_CANC_STATUS = 'N'
AND CONTH_FRZ_FLAG = 'N'
AND CSCP_BLD_CODE = TCK_BLD
AND T_DT BETWEEN CONTH_ST_DT AND CONTH_EN_DT)) ,0) COUNTER
FROM DUAL;

CURSOR C_GET_CONT_DETAIL(DT IN DATE, BUILDING IN VARCHAR2) IS
SELECT CONTH_SYS_ID,CONTH_CODE
FROM CONTRACT_HEAD,CONTRACT_SCOPE
WHERE CONTH_SYS_ID = CSCP_CONT_SYSID
AND CONTH_APPR_STATUS = 'Y'
AND CONTH_CANC_STATUS = 'N'
AND CONTH_FRZ_FLAG = 'N'
AND CSCP_BLD_CODE = BUILDING
AND DT BETWEEN CONTH_ST_DT AND CONTH_EN_DT;

BEGIN

IF C_GET_CONT_COUNT%ISOPEN THEN
CLOSE C_GET_CONT_COUNT;
END IF;

OPEN C_GET_CONT_COUNT(TICKET_DATE,BLD_CD);

FETCH C_GET_CONT_COUNT INTO CONTRACT_COUNT;


CLOSE C_GET_CONT_COUNT;


IF CONTRACT_COUNT = 0 THEN
RETURN 0;
ELSE IF CONTRACT_COUNT = 1 THEN

IF C_GET_CONT_DETAIL%ISOPEN THEN
CLOSE C_GET_CONT_DETAIL;
END IF;

OPEN C_GET_CONT_DETAIL(TICKET_DATE,BLD_CD);
FETCH C_GET_CONT_DETAIL INTO CONT_SYSID,CONT_DOC_NO;

CLOSE C_GET_CONT_DETAIL;

RETURN CONT_SYSID;

ELSE IF CONTRACT_COUNT > 1 THEN
RETURN -1;
END IF;
END IF;
END IF;

END CHECK_CONTRACTED_BUILDING;
END JOB_OPERATION;
/

Now to explain the code above that when I pass a ticket date and building code to that function then the function will check if that building is covered under maintenance contract or not within specified date passed then

if Building is not covered by a contract Function will return : 0 (Zero)

else if Building is covered by a maintenance contract and that Maintenance contract is valid then function will return the System_id Number of that contract lets say it's 81 .

now when I call a function from SQLplus it return the following :


SQL> set serveroutput on
SQL> declare
2 x number(4);
3 begin
4 x:= job_operation.CHECK_CONTRACTED_BUILDING(to_date('22/5/2012','DD/MM/YYYY'),'BLD-2');
5 dbms_output.put_line(x);
6 end;
7 /
81

PL/SQL procedure successfully completed.


but when I'm calling that function forom the following VB.net Code it's always showing to me result as Zero (0)

Friend Function get_building_contract(ByVal ticket_date As Date, ByVal building_code As String) As Long

Dim tran_conn_PROC As New OracleConnection(FRMLOGIN.ORACONNECTIONSTRING.ToString)
Dim tran_comd_PROC As New OracleCommand
Dim formatted_date As String = ticket_date.ToString("d-MMM-yyyy")

tran_comd_PROC.Connection = tran_conn_PROC


tran_comd_PROC.CommandText = "JOB_OPERATION.CHECK_CONTRACTED_BUILDING"
tran_comd_PROC.CommandType = CommandType.StoredProcedure
tran_comd_PROC.Connection.Open()


Try

MessageBox.Show(ticket_date)
tran_comd_PROC.Parameters.Add("contract_sys_id", OracleDbType.Long, 12, Nothing, ParameterDirection.ReturnValue)
tran_comd_PROC.Parameters.Add("tck_dt", OracleDbType.Date, ParameterDirection.Input)
tran_comd_PROC.Parameters.Add("building_code", OracleDbType.Varchar2, 10, Nothing, ParameterDirection.Input)

tran_comd_PROC.Parameters("tck_dt").Value = formatted_date

tran_comd_PROC.Parameters("building_code").Value = building_code.ToUpper


tran_comd_PROC.ExecuteNonQuery()
Dim RESULT As Long = CType(tran_comd_PROC.Parameters("contract_sys_id").Value.ToString, Long)

Catch oraex As OracleException
MessageBox.Show(oraex.Message)
Return False

Catch ex As Exception
MessageBox.Show(ex.Message)
Return False

End Try

tran_conn_PROC.Close()
tran_comd_PROC.Dispose()
End Function



Now I need to know why it's giving me zero always when I feed the same parameter values from .net.

Also I have another question :
- is there any way to extend the debuging of the VB.net application and go to see what exact parameter values has been passed to oracle procedure (is there any way to monitor also the execution of oracle function)

Thanks