I took a shot at writing a PL/SQL function in my oracle database. The function works well and has returned the correct values for all the test data we've thrown at it. However, things take a turn for the worst when I attempt to call this function from within C#. For some cases, I get seemingly backwards return values. Values that should come back 1 are coming back 0 and things I expect to be 0 are coming back as 1.
I've even done side by side tests, running the function in the Oracle SQL developer and within my C# program and am amazed as the return value is different between the two. If someone could give me an idea of what could cause this behavior, I would be appreciative.
Here is my code, please let me know if you need more information:
create or replace Function cbnf
(
p_cert_num IN Varchar2,
p_student_id IN Varchar2
)
Return Number As
v_returnValue Number := 1;
v_cert_id CERTIFICATES.CERT_ID%Type;
v_valid_start CERTIFICATES.VALID_START%Type;
v_required_class REQUIREMENTS.COURSE_ID%Type;
v_required_grade REQUIREMENTS.GRADE%Type;
v_mostRecentClassDate GRADES.SEMESTER_ID%Type := 0;
Cursor CertCursor(pi_cert_num IN Varchar2) Is
SELECT cert_id certid, valid_start vstart, count (*) over() cnt,
LEAD(valid_start, 1) OVER (ORDER BY valid_start) nstart
FROM certificates
WHERE cert_number = pi_cert_num;
c_CertCursor CertCursor%Rowtype;
Cursor ReqCursor(pi_cert_id IN Varchar2) Is
SELECT course_id cid, grade g
FROM requirements
WHERE cert_id = pi_cert_id;
c_ReqCursor ReqCursor%Rowtype;
Cursor StuCursor(pi_student_id IN Varchar2, pi_course_id IN Varchar2 ) Is
SELECT grade g, semester_id semid
FROM grades
WHERE student_id = pi_student_id
AND course_id = pi_course_id;
c_StuCursor StuCursor%Rowtype;
Cursor ElecCursor(pi_cert_id IN Varchar2) IS
SELECT requirement etype, num_of_classes numClass
FROM Cert_elec_reqs
WHERE cert_id = pi_cert_id;
c_ElecCursor ElecCursor%Rowtype;
Begin
Open CertCursor(p_cert_num);
dbms_output.put_line('Getting cert information');
Loop
Fetch CertCursor
INTO c_CertCursor;
Exit When CertCursor%Notfound;
dbms_output.put_line('Number of cert revisions: '||c_CertCursor.cnt);
--If c_CertCursor.cnt > 1 Then
--v_returnValue := c_CertCursor.cnt;
--Return v_returnValue;
--End If;
If c_CertCursor.cnt = 1 OR c_CertCursor.cnt = CertCursor%ROWCOUNT Then
dbms_output.put_line('Version: '||CertCursor%ROWCOUNT);
dbms_output.put_line('Valid Start Date: '|| c_CertCursor.vstart);
dbms_output.put_line('Checking core classes');
Open ReqCursor(c_CertCursor.certid);
Loop
Fetch ReqCursor
INTO c_ReqCursor;
Exit When ReqCursor%Notfound;
Open StuCursor(p_student_id, c_ReqCursor.cid);
Fetch StuCursor
INTO c_StuCursor;
If StuCursor%Found Then
dbms_output.put_line(initcap('Required Class: ' || c_ReqCursor.cid ||
', Required Grade: ' || c_ReqCursor.g));
If c_StuCursor.g >= c_ReqCursor.g Then
dbms_output.put_line(initcap('Students grade for class ' ||
c_ReqCursor.cid || ': ' ||
c_StuCursor.g));
Else
dbms_output.put_line(initcap('Grade not high enough'));
v_returnValue := 0;
Return v_returnValue;
End If; --End student grade > required grade if
Else
dbms_output.put_line(initcap('Class Not Found'));
v_returnValue := 0;
Return v_returnValue;
End If; --End stucursor found if
Close StuCursor;
End Loop; -- End ReqCursor Loop
Close ReqCursor;
dbms_output.put_line('Checking Electives');
Open ElecCursor(c_CertCursor.certid);
Loop
Fetch ElecCursor
INTO c_ElecCursor;
Exit When ElecCursor%Notfound;
v_returnValue := she(p_student_id, c_ElecCursor.etype, c_ElecCursor.numClass, c_CertCursor.certid);
If v_returnValue = 0 Then
Return v_returnValue;
End If;
End Loop;
Close ElecCursor;
ElsIf c_CertCursor.cnt > 1 Then
dbms_output.put_line('Number of cert revisions greater than 1, processing first revision');
dbms_output.put_line('Number of rows returned: '||c_CertCursor.cnt);
dbms_output.put_line('Version is: '||CertCursor%ROWCOUNT);
Open ReqCursor(c_CertCursor.certid);
Loop
Fetch ReqCursor
INTO c_ReqCursor;
Exit When ReqCursor%Notfound;
Open StuCursor(p_student_id, c_ReqCursor.cid);
Fetch StuCursor
INTO c_StuCursor;
If StuCursor%Found Then
dbms_output.put_line(initcap('Required Class: ' || c_ReqCursor.cid ||
', Required Grade: ' || c_ReqCursor.g));
If c_StuCursor.g >= c_ReqCursor.g Then
dbms_output.put_line(initcap('Students grade for class ' ||
c_ReqCursor.cid || ': ' ||
c_StuCursor.g));
Else
dbms_output.put_line(initcap('Grade not high enough, trying next revision'));
Close StuCursor;
goto GoAgain;
End If; --End student grade > required grade if
If c_StuCursor.semid > v_mostRecentClassDate Then
v_mostRecentClassDate := c_StuCursor.semid;
End If; --End semid > most recent class if
Else
dbms_output.put_line(initcap('Class Not Found, trying next revision'));
Close StuCursor;
Goto GoAgain;
End If; -- End If StuCursor%Found
Close StuCursor;
End Loop; --End ReqCursor Loop
dbms_output.put_line('Validity dates for this cert are: '||
c_CertCursor.vstart||' - '||(c_CertCursor.nstart-3));
dbms_output.put_line('Students most recently taken class was: '||v_mostRecentClassDate);
Open ElecCursor(c_CertCursor.certid);
Loop
Fetch ElecCursor
INTO c_ElecCursor;
Exit When ElecCursor%Notfound;
v_returnValue := she_date(p_student_id, c_ElecCursor.etype, c_ElecCursor.numClass, c_CertCursor.certid, c_CertCursor.nstart-3);
If v_returnValue = 0 Then
dbms_output.put_line('Elective not in right date range, checking next version');
Close ElecCursor;
Goto GoAgain;
End If;
End Loop;
Close ElecCursor;
If v_mostRecentClassDate < (c_CertCursor.nstart-3) Then
Return v_returnValue;
Else
dbms_output.put_line('Classes not in right range, checking next version');
GoTo GoAgain;
End If;
<<GoAgain>>
dbms_output.put_line('Ending the reqcursor loop');
Close ReqCursor;
End If; --End count = 1 or count = rowcount if
End Loop; --End CertCursor Loop
Close Certcursor;
Return v_returnValue;
EXCEPTION
When NO_DATA_FOUND Then
v_returnValue := 0;
dbms_output.put_line('No_Data_Found Exception, returning 0');
Return v_returnValue;
End;