OraFAQ Forum: Server Administration » Change datatype to HEX notation / base 64

Home » RDBMS Server » Server Administration » Change datatype to HEX notation / base 64 (EBS - R12, DB - 11.2.0.3, RHL5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Change datatype to HEX notation / base 64 [message #589787]

Wed, 10 July 2013 13:54

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.

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #589791 is a reply to message #589787]

Wed, 10 July 2013 14:09

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Check UTL_ENCODE package.

Regards
Michel

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #589793 is a reply to message #589791]

Wed, 10 July 2013 14:41

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.

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #589797 is a reply to message #589793]

Wed, 10 July 2013 15:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You missed the ' around the character set name.

Regards
Michel

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #589799 is a reply to message #589797]

Wed, 10 July 2013 16:33

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.

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #589853 is a reply to message #589799]

Thu, 11 July 2013 07:54

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #590396 is a reply to message #589853]

Wed, 17 July 2013 11:38

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

Report message to a moderator

Re: Change datatype to HEX notation / base 64 [message #590412 is a reply to message #590396]

Wed, 17 July 2013 19:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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;

Report message to a moderator

Previous Topic:	Oracle 10G Client works with Oracle 11G Server
Next Topic:	table query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 14:29:28 CST 2024