Home » Infrastructure » Windows » Has anyone actually created a .NET stored procedure? (Oracle 11 g R2)
Has anyone actually created a .NET stored procedure? [message #625599] Fri, 10 October 2014 15:44 Go to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
I see documentation on creating .NET stored procedures callable from PL/SQL, but I have seen no examples. I have seen examples of how to do it using the Oracle Deployment wizerd, however I want to avoid that if possible.

If anyone has done this can you share what either worked for you, or what the deployment wizard put into the DB as far as;

Creates a PL/SQL wrapper in the user's database schema for each procedure or function, according to the parameter type mappings defined by the user.


I am trying to create this manually, and not succeeding.

Also if you could show what it created for the library that would be great too.
Re: Has anyone actually created a .NET stored procedure? [message #625608 is a reply to message #625599] Sat, 11 October 2014 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So, can I think that you now definitively give up my suggestion in How can I make an existing VB.NET DLL's functions available to stored procedures and I have not to spend time to try to convert your function in PL/SQL?
Re: Has anyone actually created a .NET stored procedure? [message #625627 is a reply to message #625608] Sat, 11 October 2014 15:11 Go to previous messageGo to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
Absolutely not. I personally do not have the time needed to implement this. You said you would look into it but you also said "... but for the moment I have many things to do and so less time."

I need a solution soon, as this is causing a problem with a deliverable on a project I am working on. If you nor I have the time to rewrite it in PL/SQL perhaps someone else has successfully implemented VB.NET PL/SQL procedures.

If you are going to look at it soon, and get back to me within a day or two, then I would be interested in hearing your take on this project.
Re: Has anyone actually created a .NET stored procedure? [message #625628 is a reply to message #625627] Sat, 11 October 2014 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If I have time I'd take nothing, the only condition is that the code will be public and published.
I need you provide me a full test case: input and output expected this one. I know nothing about "United States Postal Service's Addressing Standards" so I will just make the code work for the test case you'll provide.

Re: Has anyone actually created a .NET stored procedure? [message #625629 is a reply to message #625628] Sat, 11 October 2014 16:18 Go to previous messageGo to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
Actually, I would want it to be fully Open Source. I have provided a couple of minor enhancements to Daniel the author of the original code.

I would like them incorporated in the pl/sql code as well. There are a large group of test cases in the VB version in AddressLineStandardizationTest.vb. In that code which appears to be test cases he has lines like;

Assert.AreEqual("1 BRANCH RD # 620", t("1 BRANCH RD #620"))


The first address would be output, the second would be input.

The changes that I added to the VB code handle the case where an apartment number is first, I move it to the end, ("apt 121 9875 NW 1st st" becomes "9875 NW 1ST ST APT 121") and there is a routine in his code that converts numeric words to numbers like eighteen = 18, I increased its ability to handle numbers between 21 and 99, he stopped at 20.
Re: Has anyone actually created a .NET stored procedure? [message #625631 is a reply to message #625629] Sun, 12 October 2014 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Pot the code you modified.

Re: Has anyone actually created a .NET stored procedure? [message #625651 is a reply to message #625631] Sun, 12 October 2014 08:31 Go to previous message
aceinc
Messages: 20
Registered: October 2009
Junior Member
Michel Cadot wrote on Sun, 12 October 2014 01:11

Pot the code you modified.



I added the following at the end of the "with numbers section;
            .Add("THIRTY", "30")
            .Add("FORTY", "40")
            .Add("FIFTY", "50")
            .Add("SIXTY", "60")
            .Add("SEVENTY", "70")
            .Add("EIGHTY", "80")
            .Add("NINETY", "90")

I added the following before the comment "' Remove dashes between numberic/non-numerics combinations"
        ' Check for apartment number first addresses, change to apt # last.
        mtch = Regex.Match(address, "^(APT|APARTMENT|SUITE|STE|UNIT) *(NUMBER|NO|#)? *([0-9A-Z-]+) (.*)$")
        If mtch.Success Then
            atom = convertGroupsToArray(mtch)
            address = atom(4) & " " & atom(1) & " " & atom(2) & " " & atom(3)
        End If

I changed the word to number section to;
        ' Convert numeric words to integers.
        Dim lastPart As String = ""
        Dim lastNumberWord As Boolean = False
        parts = Split(address)
        For key As Integer = 0 To UBound(parts)
            If numbers.ContainsKey(parts(key)) Then
                parts(key) = numbers(parts(key))
                If lastNumberWord _
                Then
                    If lastPart <> "" And _
                        lastPart.Substring(lastPart.Length - 1, 1) = "0" _
                    Then
                        If parts(key).Substring(parts(key).Length - 1, 1) = "0" _
                        Then
                            lastPart = lastPart & parts(key)
                        Else
                            lastPart = lastPart.Substring(0, lastPart.Length - 1) & parts(key)
                        End If
                        parts(key) = ""
                        parts(key - 1) = lastPart
                    End If
                    lastNumberWord = False
                Else
                    If parts(key).Length = 2 _
                    Then
                        lastNumberWord = True
                    Else
                        lastNumberWord = False
                    End If
                End If
            Else
                lastNumberWord = False

            End If
            lastPart = parts(key)
        Next
        address = Join(parts)

        ' Remove duplilcate spaces (introduced by numeric word to numbers.
        address = Regex.Replace(address, "\s+", " ")



The change numeric word to numbers change is not elegant, but iy seems to work.

[Updated on: Sun, 12 October 2014 08:39]

Report message to a moderator

Previous Topic: How can I make an existing VB.NET DLL's functions available to stored procedures?
Next Topic: Oracle services
Goto Forum:
  


Current Time: Tue Nov 30 07:00:39 CST 2021