Converting user defined oracle function into Access syntax

[binoj]

I have a user defined Oracle Function that need to be converted to access syntax. Can someone help me with this. If you could guide me in the right direction(like right resources to read or similar examples), I can take a first stab at it. Asking someone to do it for me without attempting myself would be greedy, I guess. I am a newbie in access.

I have already linked the oracle table using a system DSN in access 2007.


CREATE OR REPLACE FUNCTION CBSDBA.cbs_get_issues(pn_CaseId IN NUMBER, pn_Format IN NUMBER DEFAULT 4)
RETURN VARCHAR2 IS

-- Purpose: To get the issues for a case

lv_IssString VARCHAR2(100);
lv_Delim VARCHAR2(1) := ' ';
ln_IssCnt NUMBER := 0;

CURSOR lcur_Issue IS
SELECT a.issuecode
FROM CbsCaseIssueCodes a
WHERE caseid = pn_CaseId;
BEGIN
FOR rec IN lcur_Issue LOOP
IF ln_IssCnt = 0 THEN
lv_IssString := rec.issuecode;
ELSE
IF MOD(ln_IssCnt, pn_Format) = 0 THEN
lv_IssString := lv_IssString || CHR(13) || rec.issuecode;
ELSE
lv_IssString := lv_IssString || lv_Delim || rec.issuecode;
END IF;
END IF;

ln_IssCnt := ln_IssCnt + 1;
END LOOP;

RETURN lv_IssString;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, 'Error in Cbs_Get_Issues: ' || SQLERRM);
END Cbs_Get_Issues;

 

[ChrisO]

I haven’t seen Oracle code before so please take this as a first approximation:-

Public Function cbs_get_issues(pn_CaseId As Long, _
                      Optional pn_Format As Long = 4) As String
                      
    '-- Purpose: To get the issues for a case
    
    Dim lv_IssString As String
    Dim lv_Delim     As String: lv_Delim = " "
    Dim ln_IssCnt    As Long:   ln_IssCnt = 0

    On Error GoTo EXCEPTION

    With CurrentDb.OpenRecordset(" SELECT issuecode" & _
                                 " FROM CbsCaseIssueCodes" & _
                                 " WHERE caseid = " & pn_CaseId)

        Do Until .EOF
            If ln_IssCnt = 0 Then
                lv_IssString = !issuecode
            Else
                If ln_IssCnt Mod pn_Format = 0 Then
                    lv_IssString = lv_IssString & Chr(13) & Chr(10) & !issuecode
                Else
                    lv_IssString = lv_IssString & lv_Delim & !issuecode
                End If
            End If
        
            ln_IssCnt = ln_IssCnt + 1
            .MoveNext
        Loop
        
    End With

    cbs_get_issues = lv_IssString
    
ExitProcedure:
    Exit Function
    
EXCEPTION:
    MsgBox "Error in Cbs_Get_Issues: " & Err.Number
    Resume ExitProcedure
    
End Function

I hope that’s close.

Chris.

 

[binoj]

You were right on.

I realised later that some of the data types got changed when linked into access especially the caseid changed to string. Based on that I tried to tweak the code you sent to something like this and it worked like a charm.


Public Function abs_get_issues(pn_CaseId As String, Optional pn_Format As Long = 4) As String
'Public Function abs_get_issues(pn_CaseId As String, Optional pn_Format As String) As String


'-- Purpose: To get the issues for a case

Dim lv_IssString As String
Dim lv_Delim As String: lv_Delim = " "
Dim ln_IssCnt As Long: ln_IssCnt = 0
'On Error GoTo EXCEPTION
With CurrentDb.OpenRecordset(" SELECT issuecode" & _
" FROM ABSCASEISSUECODES" & _
" WHERE caseid = '" & pn_CaseId & "'")
Do Until .EOF
If ln_IssCnt = 0 Then
lv_IssString = !ISSUECODE
Else
If ln_IssCnt Mod pn_Format = 0 Then
lv_IssString = lv_IssString & Chr(13) & Chr(10) & !ISSUECODE
Else
lv_IssString = lv_IssString & lv_Delim & !ISSUECODE
End If
End If

ln_IssCnt = ln_IssCnt + 1
.MoveNext
Loop

End With
abs_get_issues = lv_IssString


ExitProcedure:
Exit Function

EXCEPTION:
MsgBox "Error in Cbs_Get_Issues: " & Err.Number
Resume ExitProcedure

End Function