convert raw text to_char in oracle dbeaver

Question

How do i return below encoded result to char

SELECT  utl_encode.base64_encode(utl_raw.cast_to_raw('some_text')) COLM FROM DUAL;

I have tried

SELECT  TO_CHAR(utl_encode.base64_encode(utl_raw.cast_to_raw('some_text'))) COLM FROM DUAL;

-- I get error: SQL Error [932] [42000]: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

Reason why i need it in char/text is because i find it difficult to copy the result from dbeaver since dbeaver recognizes the result as an encrypted data hence encrypts it further or exports empty result.

https://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle — MT0, Feb 10 '20 at 11:19

score 2 · Accepted Answer · answered Feb 10 '20 at 11:17

2

Use UTL_RAW.CAST_TO_VARCHAR2 on the RAW value you've got:

SELECT  utl_raw.cast_to_varchar2(
          utl_encode.base64_encode(
            utl_raw.cast_to_raw('some_text')
          )
        ) COLM
FROM    DUAL;

outputs:

| COLM         |
| :----------- |
| c29tZV90ZXh0 |

db<>fiddle here

answered Feb 10 '20 at 11:17

MT0

143,790
11
59
117

Thanks great answer, can you explain why in general `TO_CHAR('some_text')` brings the error `inconsistent datatypes`: ? – Omari Victor Omosa Feb 10 '20 at 11:25
1

@OmariVictorOmosa `TO_CHAR( 'some text!' )` doesn't give any errors [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a946cd7f50960bb75105929227ae753c). But if you are asking why it give errors after you convert it to a `RAW` data type; that's because `TO_CHAR` can't handle `RAW`s and you need to use a function designed to handle that data type such as `UTL_RAW.CAST_TO_VARCHAR2` to do the conversion. – MT0 Feb 10 '20 at 15:00

convert raw text to_char in oracle dbeaver

1 Answers1