Oracle function to C#

I have some Oracle code that computes a ticket # based on a passed number (p_passed_number = 1190154)

I'm trying to convert it to C# to use in a project to compare a passed value with the one that Oracle produces.

I can't get this to work in C#, it doesn't return the same values.  Oracle code:
declare
     v_pre_ticket number;
     v_twotickets varchar2(100);
begin
    v_pre_ticket:=trunc((trunc(sysdate)-to_date('31/DEC/1973','DD/MON/YYYY'))/7);
    v_pre_ticket:=v_pre_ticket*1190154;
         
    v_twotickets:=lower(rawtohex(utl_raw.cast_from_number(p_passed_number)))||md5_digest(to_char(v_pre_ticket));
    dbms_output.put_line(v_twotickets);
end;

Open in new window

It returns: c4021402375e979ed9eea6d1381a619e8d1d6ef393

C# code: (testing in dotnetfiddle.net)
using System;
using System.Security.Cryptography;
using System.Text;

public class Program
{
    static string getLogonTicket(string PID)
    {
        int iPID;
        if (!int.TryParse(PID, out iPID))
            return "";

        var weeks = (DateTime.Now.Date - new DateTime(1973, 12, 31)).Days / 7;
        var pre_ticket = weeks * iPID;
        return ("f" + iPID.ToString("X") + DoubleToMD5(pre_ticket)).ToLower();
    }
    static string DoubleToMD5(double Value)
    {
        var md5 = new MD5CryptoServiceProvider();
        var bytehash = md5.ComputeHash(ASCIIEncoding.Default.GetBytes(Value.ToString()));
        return BitConverter.ToString(bytehash).Replace("-", "");
    }

    public static void Main()
    {
        int PID = 1190154;
        Console.WriteLine(Program.getLogonTicket(PID.ToString()));
    }
}

Open in new window

It returns: f12290a2df0679bec3b841e5d573a82ecd611fe
I'm pretty sure I'm missing some code but not sure how to convert it:    
lower(rawtohex(utl_raw.cast_from_number(p_passed_number)))

Open in new window

LVL 26
Eddie ShipmanAll-around developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't think md5_digest is an Oracle provided function.

To help with this, we would need that code.

Do you need your own functions for this?  DBMS_CRYPTO should have everything you need right out of the box.

To know for sure, we would need to know what you are trying to do with the code.
0
Eddie ShipmanAll-around developerAuthor Commented:
I need the above converted to C#, but it can use library code.

This is the code for the md5_digest function:
CREATE OR REPLACE function CIC3.md5_digest (
vin_string IN varchar2
) return varchar2 IS
--
-- Return an MD5 hash of the input string.
--
BEGIN
return lower(dbms_obfuscation_toolkit.md5 (
input => utl_raw.cast_to_raw(vin_string)
));
END md5_digest;
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>I need the above converted to C#

I understand that.  What I was asking is what specifically does the code do?  Maybe it could be simplified to make porting easier.

It looks like it is generating an MD5 hash then concatenating the original number to it then converting it to hex.

Seems the C# attempt is going straight to hash.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eddie ShipmanAll-around developerAuthor Commented:
>> Seems the C# attempt is going straight to hash.

("f" + iPID.ToString("X") + DoubleToMD5(pre_ticket)).ToLower();

Open in new window

converts the PID to hex and concatenates it, just like in the Oracle code.
However, this is the full Oracle code:
function calculateticket RETURN varchar2 IS 
    v_pre_ticket number;
    v_twotickets varchar2(100);
begin
    v_pre_ticket:=trunc((trunc(sysdate)-to_date('31/DEC/1973','DD/MON/YYYY'))/7);
    v_pre_ticket:=v_pre_ticket*1190154;

    v_twotickets:=','||lower(rawtohex(utl_raw.cast_from_number(1190154)))||md5_digest(to_char(v_pre_ticket));
    v_twotickets:=v_twotickets||',f'||ltrim(to_char(1190154,'xxxxxxxxxx'),' ')||md5_digest(to_char(v_pre_ticket));
    return v_twotickets;
end;

Open in new window

The last line, before the return is doing the same thing as the C# code above.
It actually outputs:
,c4021402375e979ed9eea6d1381a619e8d1d6ef393,f12290a5e979ed9eea6d1381a619e8d1d6ef393

Open in new window

and I only need the first part, between the commas.
I hope I was clearer...
0
slightwv (䄆 Netminder) Commented:
>>I hope I was clearer...

As far as the code goes, yes.  As far as what the code is actually trying to accomplish, no.

I don't see the need to hash a value, concatenate additional values on it, do some other "magic" then convert it to hex.  I guess it makes sense to someone.

>>and I only need the first part, between the commas.

Then you would be changing the Oracle code to match C#, not making C# match the Oracle code.

But if that is what you want, just comment out the Oracle code (two dashes is easiest for a single line).

Change line9 from:
v_twotickets:=v_twotickets||',f'||ltrim(to_char(1190154,'xxxxxxxxxx'),' ')||md5_digest(to_char(v_pre_ticket));

to:
--v_twotickets:=v_twotickets||',f'||ltrim(to_char(1190154,'xxxxxxxxxx'),' ')||md5_digest(to_char(v_pre_ticket));
0
Eddie ShipmanAll-around developerAuthor Commented:
No, I want the C# code to return the same thing as what the Oracle code returns. I CANNOT modify the Oracle code as it is used in many other applications.
0
slightwv (䄆 Netminder) Commented:
>> I CANNOT modify the Oracle code as it is used in many other applications.

I was wondering.  The "only need the first part, between the commas" threw me off.

Let me play a little to see if I can figure it all out.
0
Eddie ShipmanAll-around developerAuthor Commented:
As I pointed out, I think this is the key line:
lower(rawtohex(utl_raw.cast_from_number(p_passed_number)))

Open in new window

I found a C# RawToHex function but it returns a value totally different than the Oracle function:
C#: 0A291200
Oracle: C402140237 (SQL to produce: select rawtohex(1190154) from dual)

C# RawToHex:
public static string RawToHex(byte[] data)
{
	StringBuilder sb = new StringBuilder();
	for (int i = 0; i < data.Length; i++)
    	sb.AppendFormat("{0:X2}", data[i]);
	return sb.ToString();
}	

Open in new window

Calling it:
byte[] bytes = BitConverter.GetBytes(1190154);
Console.WriteLine(RawToHex(bytes));

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>As I pointed out, I think this is the key line:

That is what I am down to as well.  I'm trying to reproduce what Oracle has for  a RAW as well.



I also had an issue with the multiplication of weeks and iPID producing different results but a quick change and cast fixed that:
double pre_ticket = (Double)weeks * (Double)iPID
0
Eddie ShipmanAll-around developerAuthor Commented:
Well, yippie do dah... I found another way for me to actually test the authentication. I'm actually surprised my Oracle developer didn't come up with this idea instead of making me jump through the hoops here...I was really wanting him to convert it to C# but he looked at me and said "you can do it".

Anyway, we had another function in our Oracle DB that would return the PID when sent the LogonTicket. I can use that to test if the PID matches the one being sent with the LogonTicket.

So essentially, it is just a LogonTicket "decoder" and I just call it with the LogonTicket and see if the result matches the PID passed.

The LogonTicket is generated when the user logs in and the function to create it is passed their username and SHA1 hashed password. It then gets their record and computes the ticket using the Oracle code above and returns it to the application where it is stored in a session variable and is used in all our web service function calls for authentication.

You don't have to do anymore work on this if you don't want to but I will give you the points...
0
slightwv (䄆 Netminder) Commented:
No problem.  Glad you were able to find an alternate solution.

I'll likely still continue to play with it.  Now I sort of have to figure it out...
0
Eddie ShipmanAll-around developerAuthor Commented:
do you want me to keep it open, just in case you figure it out?
0
slightwv (䄆 Netminder) Commented:
You can close it.

I can post what I find after that.
0
Eddie ShipmanAll-around developerAuthor Commented:
Can't wait to see what you come up with. I can't find any conversions for that Oracle RAWTOHEX but it is the utl_raw.cast_from_number that is the issue.
0
slightwv (䄆 Netminder) Commented:
I've admitted defeat.  I'm not a math person and this has quickly gotten way over my head!

I've read and reread everything I can find on how Oracle stores numbers and understand very little of it.

I've sent the link to this question to one of the "Mathiest" guys I know who also is VERY GOOD with Oracle.

I'm hoping he can dumb the Math behind Oracle's number storage enough for me to port to .Net.  He knows some VB, he might even post the code here to do it...

Until then, don't hold your breath that I'll be able to port it...
0
sdstuberCommented:
This still needs to be converted to c#; but will hopefully give a headstart by showing how the conversions happen.

CREATE OR REPLACE FUNCTION num2raw(p_number IN NUMBER)
    RETURN RAW
IS
    v_exponent   INTEGER;
    v_result     VARCHAR2(50);
    v_temp       INTEGER := p_number;
    v_bcd        INTEGER;
BEGIN
    -- This verion only supports non-negative integers
    IF p_number < 0 OR p_number != v_temp
    THEN
        RAISE VALUE_ERROR;
    END IF;

    CASE
        WHEN p_number = 0
        THEN
            v_result := '80';
        ELSE
            CASE
                WHEN p_number < 10
                THEN
                    v_exponent := 193;
                ELSE
                    v_exponent := 193 + (FLOOR(LOG(10, p_number)) - 1) / 2;
            END CASE;

            WHILE MOD(v_temp, 100) = 0
            LOOP
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            WHILE v_temp > 0
            LOOP
                v_bcd := MOD(v_temp, 100);
                v_result := TO_CHAR(v_bcd + 1, 'fm0X') || v_result;
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            v_result := TO_CHAR(v_exponent, 'fmXX') || v_result;
    END CASE;

    RETURN HEXTORAW(v_result);
END;

Open in new window

0
sdstuberCommented:
This version should work for most integers in the range [-1E38,1E126)
This one simplifies some of the logic by using LOG(100,x)  rather than LOG(10,x) and then adjusting and dividing.  It also eliminates the need for a special rules for values 1-9.

Getting it to work for the full range of negative numbers isn't too difficult except for values at the limit significant digits, then the rules seem to change on the internal byte format.  If there is consistency it's due to some pattern I haven't recognized yet.

There is another problem with multiples of 99999999999999999999999999999999999999 where the LOG function has a rounding error.
I've hardcoded fixes in for a couple values but others will still return wrong results.  As with the extreme negatives, this is at the limits of significant digits in oracle.


CREATE OR REPLACE FUNCTION num2raw(p_number IN NUMBER)
    RETURN RAW
IS
    v_exponent   INTEGER;
    v_result     VARCHAR2(50);
    v_temp       NUMBER := p_number;
    v_bcd        INTEGER;
BEGIN
    -- This verion only supports integers
    IF p_number != TRUNC(v_temp) OR p_number < -1E38
    THEN
        RAISE VALUE_ERROR;
    END IF;

    CASE
        WHEN p_number = 0
        THEN
            v_result := '80';
        WHEN p_number = -99999999999999999999999999999999999999
        THEN
            -- special case to handle internal rounding error in oracle LOG function
            -- FLOOR(LOG(100, 99999999999999999999999999999999999999))
            -- returns 19 but it should be 18
            v_result := '2C0202020202020202020202020202020202020266';
        WHEN p_number = 99999999999999999999999999999999999999
        THEN
            -- special case to handle internal rounding error in oracle LOG function
            -- FLOOR(LOG(100, 99999999999999999999999999999999999999))
            -- returns 19 but it should be 18
            v_result := 'D364646464646464646464646464646464646464';
        WHEN p_number > 0
        THEN
            v_exponent := 193 + FLOOR(LOG(100, p_number));

            WHILE MOD(v_temp, 100) = 0
            LOOP
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            WHILE v_temp > 0
            LOOP
                v_bcd := MOD(v_temp, 100);
                v_result := TO_CHAR(v_bcd + 1, 'fm0X') || v_result;
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            v_result := TO_CHAR(v_exponent, 'fmXX') || v_result;
        ELSE
            v_temp := -p_number;
            v_exponent := 62 - FLOOR(LOG(100, v_temp));

            WHILE MOD(v_temp, 100) = 0
            LOOP
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            WHILE v_temp > 0
            LOOP
                v_bcd := MOD(v_temp, 100);
                v_result := TO_CHAR(101 - v_bcd, 'fm0X') || v_result;
                v_temp := FLOOR(v_temp / 100);
            END LOOP;

            v_result := TO_CHAR(v_exponent, 'fmXX') || v_result;

            v_result := v_result || '66';
    END CASE;

    RETURN HEXTORAW(v_result);
END;

Open in new window

0
Eddie ShipmanAll-around developerAuthor Commented:
@sdstuber,
We do not need the Oracle code modified. The Oracle code we have is used in many places in our system so we are not going to change it. What needs to be done is to have a C# function to generate the same ticket values.
0
slightwv (䄆 Netminder) Commented:
>>We do not need the Oracle code modified.

He understands that.

He provided the PL/SQL version of the UTL_RAW code as an example of how it works.  Now we can port that code to C# to simulate what the UTL_RAW code is doing.

If I get time today, I'll see if I can port it over.
0
sdstuberCommented:
>>>>We do not need the Oracle code modified.

>> He understands that.

Yes,  as I stated in my first post,

This still needs to be converted to c#; but will hopefully give a headstart by showing how the conversions happen.

 I'm not trying to write C#, I'm trying to show you the conversion rules so you can write it in C# or any other language.
Essentially it's just base-100 numbers.

12345  is   3 digits in base-100    "1"  "23"  "45"     now convert each of those digits to hex.
Include an exponent to define scale and you're done.
Now, the realities of doing the math with byte and rounding limits mean there are going to be some complications but that's essentially all there is to it.
0
sdstuberCommented:
This isn't perfect, but since it's the very first c# program I've ever written I forgive myself.  :)

I had to google "c# hello world" and "how to compile c#" before I could do anything; but it only took about half an hour to figure out most of the conversion.

There are still some holes as I mentioned above.  I extended my pl/sql to support fractional values, and I've done the same here for the c#.

It WILL fail for some values (I don't know all of them, if I did I'd try to fix it.)
The failures in c# are not the same as those in pl/sql because the numeric types and range of values are different but the causes of the failures are similar - i.e. rounding on very large or very small values.

But - if anyone is still interested and doesn't want to start from the pl/sql as step 1, this will hopefully get you to step 2.

Again, this is my first c# program, so I'm sure my conventions aren't normal and I'm probably not following best practices; but my goal wasn't to become a c# developer, merely to show a possible direction in porting from pl/sql to c#.


using System;

public class num2raw
{  
   public static void Main(string[] args)
   {
      if (args.Length == 0) {
        Console.WriteLine("Please enter a number");
      }
      else {
        Decimal p_number = Decimal.Parse(args[0]);
        string v_result = "";
        Int16 v_exponent;
        Int16 v_bcd;
        Decimal v_temp;
        
        if (p_number == 0) 
            v_result = "80";
        else if (p_number > 0) {
            v_temp = p_number;
            v_exponent = Convert.ToInt16(193 + Math.Floor(Math.Log(Convert.ToDouble(p_number),100)));
            
            while (v_temp != Math.Floor(v_temp))
                v_temp *= 100;
            
            while (v_temp % 100 == 0) 
                v_temp = Math.Floor(v_temp/100);
            
            while (v_temp > 0) {
                v_bcd = Convert.ToInt16(v_temp % 100);
                v_result = String.Format("{0:X2}", v_bcd+1) + v_result;
                v_temp = Math.Floor(v_temp/100);
            }
            
            v_result = String.Format("{0:X2}", v_exponent) + v_result;
        }
        else { // p_number < 0
            v_temp = -p_number;
            v_exponent = Convert.ToInt16(62 - Math.Floor(Math.Log(Convert.ToDouble(v_temp),100)));
            
            while (v_temp != Math.Ceiling(v_temp))
                v_temp *= 100;
            
            while (v_temp % 100 == 0) 
                v_temp = Math.Floor(v_temp/100);            
            
            while (v_temp > 0) {
                v_bcd = Convert.ToInt16(v_temp % 100);
                v_result = String.Format("{0:X2}", 101 -v_bcd) + v_result;
                v_temp = Math.Floor(v_temp/100);
            }
            
            v_result = String.Format("{0:X2}", v_exponent) + v_result;
            
            if (v_result.Length < 42)
                v_result = v_result + "66";
        }
        
        Console.WriteLine(v_result);
      }
   }
}

Open in new window

0
slightwv (䄆 Netminder) Commented:
EddieShipman,

Sorry for not getting time to work on this today but it appears sdstuber has a C# port.

Please check it out and see if it will provide a solution to the original question.

If so, I would like to unaccept this so you can give him the points since he did ALL the heavy lifting.
0
Eddie ShipmanAll-around developerAuthor Commented:
ok, will check it out
0
Eddie ShipmanAll-around developerAuthor Commented:
Ok,
select utl_raw.cast_from_number(1190154) from dual

Open in new window

Returns: c402140237

While his code returns: c102 (modifying the last line to Console.WriteLine(v_result.ToLower());)
0
slightwv (䄆 Netminder) Commented:
>>While his code returns: c102 (modifying the last line to Console.WriteLine(v_result.ToLower());)

I cannot reproduce that.

C:\num2raw>num2raw.exe 1190154
c402140237

Open in new window

0
slightwv (䄆 Netminder) Commented:
I compiled with:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc num2raw.cs

Open in new window

0
Eddie ShipmanAll-around developerAuthor Commented:
I ran in dotnetfiddle.net window
0
sdstuberCommented:
worked for me...

F:\scripts\usb\dotNet>csc num2raw.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.


F:\scripts\usb\dotNet>num2raw 1190154
C402140237
0
sdstuberCommented:
I don't know how to call the function with a parameter in the dotnetfiddle


but simply removing the arguments and embedding the value completed the test for me

https://dotnetfiddle.net/jwN4Du
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>I ran in dotnetfiddle.net window

Can you post the fiddle link?

Haven't used any .Net fiddle's before but the first one I Googled, well, worked:
https://dotnetfiddle.net/5U5aFH
0
Eddie ShipmanAll-around developerAuthor Commented:
Kind of funny, cause I took his code and made function RawToHex and ran. C102, result. Copied his entire code in fiddle, made mod to set variable, ran, C102 result.

Will try post the fiddle link today.
0
Eddie ShipmanAll-around developerAuthor Commented:
Here's my fiddle:
https://dotnetfiddle.net/oC8rwe

Now after looking at the results from both Oracle and the fiddle, it seems that the md5_digest call is not returning the same thing as the DoubleToMD5 function.

md5_digest: 5e979ed9eea6d1381a619e8d1d6ef393
DoubleToMD5: 2df0679bec3b841e5d573a82ecd611fe

I've got some more work to do.
0
Eddie ShipmanAll-around developerAuthor Commented:
@slightwv, I forgot how to reassign the points...
0
Eddie ShipmanAll-around developerAuthor Commented:
Yippee. I got it. I modified the code in the fiddle to get the correct result.

double weeks = (DateTime.Now.Date - new DateTime(1973, 12, 31)).Days / 7;
var pre_ticket = Math.Truncate(weeks * iPID);

Open in new window

0
Eddie ShipmanAll-around developerAuthor Commented:
Whew,  one of the longest threads I've seen.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.