have a stored procedure which has a table as one of its "out" parameters. I'm not able to call this procedure from c# .net. please let me know how i can access it. The procedure has been called from vb. I want to move the following code from vb to c#.
VB code
Function ReportTableRetrieved(objReportTable As OraParamArray) As Boolean
Const constArraySize = 100
Dim i As Integer
Dim strSQL As String
Dim strMsg As String
Dim strReportDate As String
Dim strSysDate As String
strReportDate = RetrieveReportDate
strSysDate = gstrSysDate
gdbSLAM.Parameters.Add "report_dt_in", strReportDate, ORAPARM_INPUT
gdbSLAM.Parameters("report_dt_in").serverType = ORATYPE_DATE
gdbSLAM.Parameters.Add "sys_dt_in", strSysDate, ORAPARM_INPUT
gdbSLAM.Parameters("sys_dt_in").serverType = ORATYPE_DATE
gdbSLAM.Parameters.Add "err_msg_out", "", ORAPARM_OUTPUT
gdbSLAM.Parameters("err_msg_out").serverType = ORATYPE_VARCHAR2
gdbSLAM.Parameters.AddTable "report_table", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, constArraySize, 255
strSQL = "Begin MRM.RUN_REPORT(:report_dt_in, "
strSQL = strSQL & " :sys_dt_in, "
strSQL = strSQL & " :report_table, "
strSQL = strSQL & " :err_msg_out); end;"
On Error Resume Next
gdbSLAM.ExecuteSQL (strSQL)
If Err.Number Then
MsgBox Err.Description, vbCritical
Else
Set objReportTable = gdbSLAM.Parameters("report_table")
ReportTableRetrieved = True
End If
For i = 0 To gdbSLAM.Parameters.Count - 1
gdbSLAM.Parameters.Remove 0
Next
End Function
And the stored procedure in oracle
PROCEDURE RUN_REPORT
(
report_dt_in IN DATE,
sys_dt_in IN DATE,
report_table OUT RPT_LINE_OUT,
err_msg_out OUT VARCHAR2
)
IS
BEGIN
l_include_basis := FALSE;
report_dt := report_dt_in;
rl_idx := 0;
-- Determine whether to include data from current or history tables.
IF report_dt_in = sys_dt_in THEN
l_include_current_data := 1;
ELSE
l_include_current_data := 0;
END IF;
INIT_TABLE;
GET_GAPS;
GET_INVEST_DATA;
GET_LOAN_BASIS;
GET_NOTIONAL_TOTALS;
FILL_TABLE;
report_table := rl_tab;
EXCEPTION
WHEN OTHERS THEN
IF l_sblock IS NULL THEN
l_sblock := 'MRM.RUN_REPORT';
errMsg := 'Err info: ' || l_sblock || ': ' || SQLCODE || ': ' || SUBSTR(SQLERRM,11,500);
errs.log('NONE', l_sblock, SQLCODE, SUBSTR(SQLERRM,11,500), l_usr, errMsg);
END IF;
err_msg_out := errMsg;
RAISE;
END RUN_REPORT;