CODEWITHSUNDEEP
oracle-databaseunicodeutf-8type-conversionoracle12c
Criss
Criss

Reputation: 7

Convert Unicode characters to UTF8 (decimal) in Oracle SQL 12c - not working with Convert function

I have a case when I have to change Oracle connectors from 11g to 12c and below query is not working anymore: select getunicodetransl(column) from table. In 11g it worked without error, but in 12c version it gives error "ORA-00904: "GETUNICODETRANSL": invalid identifier".

How can I replace this function, so I can have the same results? For example this function converts value "!$9LQND6" to "33369LQND6", this means it replaced ! with value 33 and $ with value 36, the UTF8 equivalent.

I've tried multiple ways of replacing this function, but I couldn't get the same result. The most correct option from my point of view was "select convert(column,'utf8') from table", but the result is still "!$9LQND6".

Thank you!

Upvotes: 0

Views: 1949

Answers (2)

Thomas Carlton
Thomas Carlton

Reputation: 5968

"!$9LQND6" IS UTF8.

UTF8 refers to more than 1 million characters. So it's safe to say that almost all special characters you know are included in UTF8. Hence "!$9LQND6" will always remain as it's in UTF8

The coding function you are looking for is not a standard conversion. Note also that when you convert "!$9LQND6" to "33369LQND6" you can not convert it back to it's original value. When you get 3336 you don't know if these have to be just numbers or the ascii code for some special characters...

The closest thing you can get is a function that browses all the characters :

    Declare
        Char_ varchar2(10);
        Output varchar2(1000);
    begin    
        for i in 1..length(Text)
        loop
            Char_  := substr(Text,i,1);
            if upper(Char_)  not in ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') then 
                Output := Output || ascii(Char_);
            else
                Output := Output|| Char_;
            end if;
        end loop
        return Output;
    end;

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

There is no such function in Oracle. Someone created that function in your 11g database. Get the code and deploy it in your 12c

Upvotes: 0

Related Questions