A lot is said already about the power and limitations of current AIs like ChatGTP.
During a lazy afternoon I wanted to see it's capabilities and limitations in an easy (at least for me to describe) but real world situation. I try to explain my observations and findings while following my conversation with ChatGTP. The goal is to understand the whole process and learn about it. Any kind of blame or finger pointing isn't the intention.
My goal is to get a function like inet_aton in PL/SQL. This function converts an IPv4 address from any human readable form to it's true nature: a number. It might sound easy to convert something like 127.0.0.1 to it's number by some multiplies of 256, but there are more complicated cases like hex and octal notation, and even a compact notation: 0x7f.1 is also a valid representation.
So my conversation starts:
can you write an equivalent of inet_aton in Oracle PL/SQL language?
Certainly! In Oracle PL/SQL, you can create a function that mimics the functionality of inet_aton, which converts an IPv4 address from its dotted-quad notation to a 32-bit integer. Here's an example implementation:
CREATE OR REPLACE FUNCTION inet_aton(ip_address VARCHAR2) RETURN NUMBER IS
ip_parts SYS.ODCIVARCHAR2LIST;
ip_number NUMBER := 0;
BEGIN
-- Split the IP address into its four parts
ip_parts := SYS.ODCIVARCHAR2LIST(SUBSTR(ip_address, 1, INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 1) + 1, INSTR(ip_address, '.', 1, 2) - INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 2) + 1, INSTR(ip_address, '.', 1, 3) - INSTR(ip_address, '.', 1, 2) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 3) + 1));
-- Convert each part to a number and calculate the final IP number
ip_number := (TO_NUMBER(ip_parts(1)) * 256 * 256 * 256) +
(TO_NUMBER(ip_parts(2)) * 256 * 256) +
(TO_NUMBER(ip_parts(3)) * 256) +
TO_NUMBER(ip_parts(4));
RETURN ip_number;
END;
/
This is a nice answer for "normal" cases. But I want to challenge the AI a little bit:
in the manpage to inet_aton I can see it also handles octal and hex notation. Especially 0x7f.1 is mentioned there. Can you extend the function so it can handle those cases also?
Certainly! I apologize for the oversight. Here's an extended version of the inet_aton function that can handle octal and hex notation as well:
CREATE OR REPLACE FUNCTION inet_aton(ip_address VARCHAR2) RETURN NUMBER IS
ip_parts SYS.ODCIVARCHAR2LIST;
ip_number NUMBER := 0;
base NUMBER := 10; -- Default base is decimal
FUNCTION convertToNumber(ip_part VARCHAR2) RETURN NUMBER IS
BEGIN
-- Determine the base of the IP part
IF ip_part LIKE '0x%' THEN
base := 16; -- Hexadecimal
ip_part := SUBSTR(ip_part, 3); -- Remove the '0x' prefix
ELSIF ip_part LIKE '0%' THEN
base := 8; -- Octal
ip_part := SUBSTR(ip_part, 2); -- Remove the leading '0'
ELSE
base := 10; -- Decimal
END IF;
-- Convert the IP part to a number based on the determined base
RETURN TO_NUMBER(ip_part, 'FM' || CASE base WHEN 8 THEN '990' WHEN 16 THEN '990X' ELSE '999' END);
END;
BEGIN
-- Split the IP address into its four parts
ip_parts := SYS.ODCIVARCHAR2LIST(SUBSTR(ip_address, 1, INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 1) + 1, INSTR(ip_address, '.', 1, 2) - INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 2) + 1, INSTR(ip_address, '.', 1, 3) - INSTR(ip_address, '.', 1, 2) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 3) + 1));
-- Convert each part to a number and calculate the final IP number
ip_number := (convertToNumber(ip_parts(1)) * 256 * 256 * 256) +
(convertToNumber(ip_parts(2)) * 256 * 256) +
(convertToNumber(ip_parts(3)) * 256) +
convertToNumber(ip_parts(4));
RETURN ip_number;
END;
/
This is a nice try, but the syntax isn't correct: in the function
convertToNumber ip_part isn't a variable and so it can't be assigned with a substr. It would also be a logical error if it would be possible. But the idea is clear and I can modify the code easily.
Beside that, a more severe issue is the handling of octal numbers: TO_NUMBER( ..., 'FM990') just does NOT convert an octal string to a number.
Here 2 issues are shown: simple syntax issues will be highlighted by the compiler. But the logical wrong, but syntactical correct usage of TO_NUMBER is much harder to spot and requires proper test-data or a deep understanding of the capabilities by the observing human.
Can you please have a closer look at your sub-function convertToNumber: I face some issues there: first you can not assign to ip_part - a dedicated variable is required there. Second the to_number doesn't convert octal numbers properly. can you please fix this, or show me the link to a working example of to_number for octal?
I apologize for the confusion and the errors in the previous response. Converting octal numbers directly using TO_NUMBER can indeed lead to incorrect results. Unfortunately, Oracle's TO_NUMBER function does not provide a format model for parsing octal numbers.
However, I can offer an alternative solution by implementing a custom function to convert octal numbers to decimal. Here's an updated version of the inet_aton function with the revised convertToNumber function that handles octal notation correctly:
CREATE OR REPLACE FUNCTION inet_aton(ip_address VARCHAR2) RETURN NUMBER IS
ip_parts SYS.ODCIVARCHAR2LIST;
ip_number NUMBER := 0;
base NUMBER := 10; -- Default base is decimal
FUNCTION convertToNumber(ip_part VARCHAR2) RETURN NUMBER IS
hex_to_dec CONSTANT SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', '10', '11', '12', '13', '14', '15');
dec_num NUMBER := 0;
digit CHAR(1);
BEGIN
-- Determine the base of the IP part
IF ip_part LIKE '0x%' THEN
base := 16; -- Hexadecimal
ip_part := SUBSTR(ip_part, 3); -- Remove the '0x' prefix
ELSIF ip_part LIKE '0%' THEN
base := 8; -- Octal
ip_part := SUBSTR(ip_part, 2); -- Remove the leading '0'
ELSE
base := 10; -- Decimal
END IF;
-- Convert the IP part to a number based on the determined base
IF base = 8 THEN
FOR i IN 1..LENGTH(ip_part) LOOP
digit := SUBSTR(ip_part, i, 1);
dec_num := dec_num * 8 + TO_NUMBER(hex_to_dec(TO_NUMBER(digit)));
END LOOP;
ELSE
dec_num := TO_NUMBER(ip_part);
END IF;
RETURN dec_num;
END;
BEGIN
-- Split the IP address into its four parts
ip_parts := SYS.ODCIVARCHAR2LIST(SUBSTR(ip_address, 1, INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 1) + 1, INSTR(ip_address, '.', 1, 2) - INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 2) + 1, INSTR(ip_address, '.', 1, 3) - INSTR(ip_address, '.', 1, 2) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 3) + 1));
-- Convert each part to a number and calculate the final IP number
ip_number := (convertToNumber(ip_parts(1)) * 256 * 256 * 256) +
(convertToNumber(ip_parts(2)) * 256 * 256) +
(convertToNumber(ip_parts(3)) * 256) +
convertToNumber(ip_parts(4));
RETURN ip_number;
END;
/
Here ChatGTP ignores my first comment about the variable. Only my complaint about octal was taken into account. I would still need some manual checks after the compiler fails. Also the conversion of octal numbers seems to be over complicated and "borrowed" from an idea to convert hex numbers - as the name of the variable
hex_to_dec indicates. As octal is within decimal range, this can be omitted.
As I was more interested in concepts than a 100% working code, I asked for a last refinement: in the manpage of inet_aton there is an example which accepts input 0x7f.1 - this is a compressed form where elements of zero are just ignored. Can you extend your code to these special cases also?
Certainly! I apologize for the oversight. Here's an updated version of the inet_aton function that can handle the compressed form of IP addresses where leading zeros are omitted:
CREATE OR REPLACE FUNCTION inet_aton(ip_address VARCHAR2) RETURN NUMBER IS
ip_parts SYS.ODCIVARCHAR2LIST;
ip_number NUMBER := 0;
base NUMBER := 10; -- Default base is decimal
FUNCTION convertToNumber(ip_part VARCHAR2) RETURN NUMBER IS
hex_to_dec CONSTANT SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', '10', '11', '12', '13', '14', '15');
dec_num NUMBER := 0;
digit CHAR(1);
BEGIN
-- Determine the base of the IP part
IF ip_part LIKE '0x%' THEN
base := 16; -- Hexadecimal
ip_part := SUBSTR(ip_part, 3); -- Remove the '0x' prefix
ELSIF ip_part LIKE '0%' THEN
base := 8; -- Octal
ip_part := SUBSTR(ip_part, 2); -- Remove the leading '0'
ELSE
base := 10; -- Decimal
END IF;
-- Convert the IP part to a number based on the determined base
IF base = 8 THEN
FOR i IN 1..LENGTH(ip_part) LOOP
digit := SUBSTR(ip_part, i, 1);
dec_num := dec_num * 8 + TO_NUMBER(hex_to_dec(TO_NUMBER(digit)));
END LOOP;
ELSE
dec_num := TO_NUMBER(ip_part);
END IF;
RETURN dec_num;
END;
BEGIN
-- Split the IP address into its four parts
ip_parts := SYS.ODCIVARCHAR2LIST(SUBSTR(ip_address, 1, INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 1) + 1, INSTR(ip_address, '.', 1, 2) - INSTR(ip_address, '.', 1, 1) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 2) + 1, INSTR(ip_address, '.', 1, 3) - INSTR(ip_address, '.', 1, 2) - 1),
SUBSTR(ip_address, INSTR(ip_address, '.', 1, 3) + 1));
-- Handle the special case of compressed form (leading zeros omitted)
IF LENGTH(ip_parts(1)) = 0 THEN
ip_parts(1) := '0';
END IF;
IF LENGTH(ip_parts(2)) = 0 THEN
ip_parts(2) := '0';
END IF;
IF LENGTH(ip_parts(3)) = 0 THEN
ip_parts(3) := '0';
END IF;
IF LENGTH(ip_parts(4)) = 0 THEN
ip_parts(4) := '0';
END IF;
-- Convert each part to a number and calculate the final IP number
ip_number := (convertToNumber(ip_parts(1)) * 256 * 256 * 256) +
(convertToNumber(ip_parts(2)) * 256 * 256) +
(convertToNumber(ip_parts(3)) * 256) +
convertToNumber(ip_parts(4));
RETURN ip_number;
END;
/
Now the inet_aton function handles the compressed form of IP addresses correctly by checking for empty parts and assigning them the value '0'. This ensures that the leading zeros are considered in the conversion process.
Apologies for the confusion earlier, and thank you for bringing this to my attention.
I like the confidence it shows!
But here I stopped as it seems to get complicated and the answer doesn't improve the way I would expect.
My conclusion:
It's ok to ask ChatGTP for some code, but it can not be trusted at all. Syntax errors might be simple to fix. Wrong implementations of algorithms are much more dangerous.
I also totally miss any kind of error handling, instrumentation, defensive implementations and so on.
For me it seems as if ChatGTP is acting like a newcomer to the coding with high skills in searching the internet for anything useful, but limited capacity to generate a valid result.
It's not sure if this is true in all other areas where AI generates answers, but at least it's an indication for me how to deal with this kind of generated content.
