Encrypt the field works on Toad for Oracle but does not work on Toad data point. When the file exported, the encrypted field is gone. Who can tell why and how to fix it? Thanks!
Do you mean encrypted or column that has redaction policy applied to mask the visible values? I checked both in TDP and exporting to excel instance worked correctly. If I am owner of the table or DBA I will see the underlying data whether encrypted or redacted. It exports this way also. If I am not DBA or owner i don’t see encrypted column and see masked redacted column (if fully redacted i see nothing). What I see or don’t see in the grid exports correctly.
Can you provide more detail on the column and what form of exporting you are using?
Thanks Debbie for your response. Yes, I encrypt ID column by coding encrypt (ID) and export the masked value into excel (xlsx or xls). It works perfectly on Toad for Oracle but the encrypt (ID) column is not exported at all when I automate the job on Toad data point. The whole ID column is gone after exporting to excel.
Oracle’s encryption is called Transparent Data Encryption, which means that the data is only encrypted on the disk. When you run a query to select the data, you see everything as if there were no encryption at all.
Redaction (I think this is what you are really talking about) is where you don’t see the actual data when you run a query. For example, you’d see xxx-xx-1234 instead of a real social security number.
Questions:
- Which of these do you mean? Redaction or encryption?
- Are you logging in with the same username from both Toad for Oracle and TDP?
- If you just run the query in TDP, do you see values?
- If #3 is Yes, then it seems that the problem is in TDP’s export process, maybe the redaction is changing data type or something else that TDP isn’t handling correctly
To answer your questions:
- Redaction
- Yes, same logon from both Toad for Oracle and TDP.
- Yes, I can see the values.
- can I use to_char or to_number to convert the data type so that the value can be exported?
Thanks!
How are you exporting?
- Right click Quick Export to Excel instance?
- Export Wizard with SQL?
- Select to file activity in automation?
I have table with three columns. Two have different type of partial redaction and the third has full redaction. When I am connected as the owner of the table and export using export wizard with SQL to Excel I see all of the values of the columns.
When I do the same steps logged on as different user who as select access on the table the export show the three columns with correct redaction.
I need to reproduce your issue in order to find fix or work around. Please provide export steps as well as table ddl so I can work on reproducing the issue.
Hi Debbie, I am having select to file activity in automation and export option to excel file. This step is coding from the temp table that I create from the previous step by execute script. Thanks!
that seems to be a missing piece of info. Can you post your automation script so i can replicate the exact steps. if you do not want to post on forum you can email the script at dpeabody@quest.com
can I use to_char or to_number to convert the data type so that the value can be exported?
Yes, you can use UTL_RAW.CAST_TO_VARCHAR2(your raw data) to convert RAW to VARCHAR2.
Sorry for the delayed response. I am not subscribed to this forum, but I thought I was going to get a notification that you replied since I replied to the thread too. But that didn't happen. 