Home » RDBMS Server » Server Administration » Change datatype to HEX notation / base 64 (EBS - R12, DB - 11.2.0.3, RHL5)
Change datatype to HEX notation / base 64 [message #589787] Wed, 10 July 2013 13:54 Go to next message
ajpl71
Messages: 4
Registered: July 2013
Junior Member
Hi all:

I am currently in the process of migrating our database from US7ASCII to AL32UTF8 using DMU.

I am stuck at a point where I have encrypted data that, when the conversion happens, will be destroyed. Oracle suggests to move the data which is currently stored in VARCHAR2 datatype, to a "characterset safe way" like hex notation or base64 before converting.

I have no clue as to what that means and I haven't found information that could point me in the right direction.

Could someone please give me a hand?

Thanks and cheers.
Re: Change datatype to HEX notation / base 64 [message #589791 is a reply to message #589787] Wed, 10 July 2013 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check UTL_ENCODE package.

Regards
Michel
Re: Change datatype to HEX notation / base 64 [message #589793 is a reply to message #589791] Wed, 10 July 2013 14:41 Go to previous messageGo to next message
ajpl71
Messages: 4
Registered: July 2013
Junior Member
Thanks Michel.

I'm following the example pertaining to TEXT_DECODE, which I believe resembles more what I want to accomplish, but it doesn't seem to work as it says (US7ASCII is the one that interests me since I'd be replacing the text with the column of the table I need):

-- DOC EXAMPLE
SQL> select UTL_ENCODE.TEXT_DECODE('Here is some text',WE8ISO8859P1,UTL_ENCODE.BASE64) from dual;
select UTL_ENCODE.TEXT_DECODE('Here is some text',WE8ISO8859P1,UTL_ENCODE.BASE64) from dual
*
ERROR at line 1:
ORA-00904: "WE8ISO8859P1": invalid identifier

-- MY TEST EXAMPLE
SQL> select UTL_ENCODE.TEXT_DECODE('Here is some text',US7ASCII,UTL_ENCODE.BASE64) from dual;
select UTL_ENCODE.TEXT_DECODE('Here is some text',US7ASCII,UTL_ENCODE.BASE64) from dual
*
ERROR at line 1:
ORA-00904: "US7ASCII": invalid identifier

Any ideas?

Thanks again and cheers.
Re: Change datatype to HEX notation / base 64 [message #589797 is a reply to message #589793] Wed, 10 July 2013 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You missed the ' around the character set name.

Regards
Michel
Re: Change datatype to HEX notation / base 64 [message #589799 is a reply to message #589797] Wed, 10 July 2013 16:33 Go to previous messageGo to next message
ajpl71
Messages: 4
Registered: July 2013
Junior Member
Thanks, the Oracle documentation didn't have it either, Smile.

Now I'm getting this error:

SQL> select UTL_ENCODE.TEXT_DECODE('Here is some text', 'US7ASCII', UTL_ENCODE.BASE64) from dual;
select UTL_ENCODE.TEXT_DECODE('Here is some text', 'US7ASCII', UTL_ENCODE.BASE64) from dual
*
ERROR at line 1:
ORA-06553: PLS-221: 'BASE64' is not a procedure or is undefined

I've seen 3 different documents and they are all the same, so I don't know what I'm missing this time.

Thanks and cheers.
Re: Change datatype to HEX notation / base 64 [message #589853 is a reply to message #589799] Thu, 11 July 2013 07:54 Go to previous messageGo to next message
joy_division
Messages: 4530
Registered: February 2005
Location: East Coast USA
Senior Member
ajpl71 wrote on Wed, 10 July 2013 17:33

SQL> select UTL_ENCODE.TEXT_DECODE('Here is some text', 'US7ASCII', UTL_ENCODE.BASE64) from dual;
select UTL_ENCODE.TEXT_DECODE('Here is some text', 'US7ASCII', UTL_ENCODE.BASE64) from dual
*
ERROR at line 1:
ORA-06553: PLS-221: 'BASE64' is not a procedure or is undefined


I believe you have to use this in PL/SQL, and not in SQL*Plus.
Re: Change datatype to HEX notation / base 64 [message #590396 is a reply to message #589853] Wed, 17 July 2013 11:38 Go to previous messageGo to next message
ajpl71
Messages: 4
Registered: July 2013
Junior Member
Thanks guys. This is not really what I was looking for because I still need to store it in the database, but I'll keep searching.

Thanks a lot and cheers,
AJ
Re: Change datatype to HEX notation / base 64 [message #590412 is a reply to message #590396] Wed, 17 July 2013 19:08 Go to previous message
BlackSwan
Messages: 22909
Registered: January 2009
Senior Member
http://psoug.org/reference/utl_encode.html

utl_encode.base64_encode(r IN RAW) RETURN RAW;
utl_encode.base64_decode(r IN RAW) RETURN RAW;
Previous Topic: Oracle 10G Client works with Oracle 11G Server
Next Topic: table query
Goto Forum:
  


Current Time: Fri Oct 24 03:57:03 CDT 2014

Total time taken to generate the page: 0.18096 seconds