CODEWITHSUNDEEP
pythonoracle-databaseplsqlspecial-characterscx-oracle
Mike Gostomski
Mike Gostomski

Reputation: 138

Unable to save a specific special character (ü) in Oracle database

I'm trying to get special characters (ex: ñ, ü, é) from a Python 3 (Django) app through a Python 2 (Flask) API and into an Oracle database (Oracle 19c Enterprise). This replaces a Java app that had direct database access and was able to save special characters with no issues.

In order to make the API call with a special character in it, I've found that I have to URL-encode the value:

>>> comment = "Joëlle Küsel Núñez-Chaillüé"
>>> urllib.parse.quote(comment).encode('utf-8')
b'Jo%C3%ABlle%20K%C3%BCsel%20N%C3%BA%C3%B1ez-Chaillu%CC%88%C3%A9'

That string gets sent to the Python 2 API. My original attempts involved decoding it in Python 2 before sending it to Oracle, however, any attempt to pass the value into cx_Oracle resulted in 'ascii' codec can't encode characters in position 3-4: ordinal not in range(128)

I attempted to work around it by simply passing the URL-encoded string directly to Oracle:

>>> comment_blob = cx.cursor().var(cx_Oracle.BLOB)
>>> comment_blob.setvalue(0, bytes(u'{}'.format(comment.decode('utf-8'))))

In Oracle, I attempt to decode the value:

v_comment := UTL_RAW.CAST_TO_VARCHAR2( parameter );
v_comment := convert(utl_url.unescape(v_comment), 'WE8ISO8859P15');

This works for everything except ü, which in some cases gets overlayed by the character that follows it (but displays correctly when pasted into any other text editor), and sometimes gets changed to a completely different character. For example:

Joëlle Küsel Núñez-Chaillüé
Joëlle Kÿsel Núñez-Chaillüé

Character 9 changes from ü to ÿ, and although the last two characters look correct here, in SQL Developer, they are combined into a single character (values inserted via the old Java app display correctly in SQL Developer).

Any ideas why I'm having issues with only the ü character?

Upvotes: 0

Views: 1155

Answers (2)

Mike Gostomski
Mike Gostomski

Reputation: 138

I came up with a functioning (though not ideal) work-around to solve my problem. In my Python 3 (Django) app, I convert the string to a list of character codes. I pass the list of codes through the Python 2 API without doing anything to it, and then use PL/SQL to convert it back to regular characters.

Django Code:

comment = str([ord(c) for c in comment]).strip('[]').replace(' ', '')

# Send comment to API as a parameter

Python 2 API:

# Call PL/SQL function passing in the list of character codes as a string

cursor.callfunc(
    'save_comment', cx_Oracle.STRING, [comment]
)

Oracle:

FUNCTION save_comment(p_comment VARCHAR2)
    RETURN VARCHAR2
IS
    v_comment           VARCHAR2(500) := '';
    v_remain            VARCHAR2(500);
    v_char              VARCHAR2(3);
    v_char_code         NUMBER;
BEGIN

    -- Comment comes in as list of character codes
    -- '74,111,235,108,108,101,32,75,252,115,101,108,32,78,250,241'
    v_remain := p_comment;

    WHILE v_remain IS NOT NULL LOOP

        -- Pop the first character code from the list
        IF v_remain LIKE '%,%' THEN
            v_char := substr(v_remain, 1, instr(v_remain, ',')-1);
            v_remain := substr(v_remain, instr(v_remain, ',') + 1);
        ELSE
            v_char := v_remain;
            v_remain := NULL;
        END IF;

        -- Turn character code from VARCHAR2 into NUMBER
        v_char_code := to_number(v_char);

        -- Convert Python char code to Oracle char code
        IF v_char_code >= 192 THEN
            v_char_code := v_char_code + 49856;
        ELSIF v_char_code > 160 THEN
            v_char_code := v_char_code + 49664;
        END IF;

        -- Add this character to the comment
        v_comment := v_comment || chr(v_char_code);
    END LOOP;

    RETURN v_comment;
END;

This handy list of ASCII character codes was a good resource:
https://www.techonthenet.com/ascii/chart.php

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10506

If you have an older cx_Oracle module then you need to specify a character set when you create a connection, for example:

connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", encoding="UTF-8")

If you upgrade to cx_Oracle 8, then this character set is the default, so the encoding option can be omitted. See Setting the Client Character Set.

Two other notes:

  • Plan to upgrade to Python 3, since Python 2 is no longer being developed. Also Python 3 has changed internal handing of text and bytes.

  • Hopefully you're actually using a connection pool, see How to use Python Flask with Oracle Database.

Upvotes: 1

Related Questions