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.

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:

1a619e8d1d6ef393

C# code: (testing in dotnetfiddle.net)

73a82ecd611fe

I'm pretty sure I'm missing some code but not sure how to convert it:

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;
```

It returns: c4021402375e979ed9eea6d138C# 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()));
}
}
```

It returns: f12290a2df0679bec3b841e5d5I'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)))
```

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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.

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;
/
```

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.

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

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;
```

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

```
,c4021402375e979ed9eea6d1381a619e8d1d6ef393,f12290a5e979ed9eea6d1381a619e8d1d6ef393
```

and I only need the first part, between the commas.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

to:

--v_twotickets:=v_twoticke

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.

```
lower(rawtohex(utl_raw.cast_from_number(p_passed_number)))
```

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();
}
```

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

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

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...

I'll likely still continue to play with it. Now I sort of have to figure it out...

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...

```
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;
```

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 99999999999999999999999999

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;
```

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.

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.

>> 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.

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);
}
}
}
```

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.

```
select utl_raw.cast_from_number(1190154) from dual
```

Returns: c402140237While his code returns: c102 (modifying the last line to

I cannot reproduce that.

```
C:\num2raw>num2raw.exe 1190154
c402140237
```

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>num2

C402140237

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

https://dotnetfiddle.net/jwN4Du

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 trialCan 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

Will try post the fiddle link today.

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: 5e979ed9eea6d1381a619e8d1d

DoubleToMD5: 2df0679bec3b841e5d573a82ec

I've got some more work to do.

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

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

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.