Recall history file

Toad for Oracle & DevOps Toolkit
1

I have played enough with toad to know that most files are located in C:\Users\user_name\AppData\Roaming\Quest Software\Toad for Oracle\version_number\User Files. Which file is the recall file? Windows is not being helpful with searching.

2

See this post

The file is SavedSQL.XML, but the SQL in it is encrypted.

3

Do you know why the encrypt that file but not the backup file? Figured they would encrypt both files or neither files.

4

The SQLText in both files is encrypted.

5

Also, the SQL isn't exactly encrypted. It's base64 encoded as a means to ensure the SQL will not break XML. A CDATA section would have also been sufficient.

6

Ahh, good point Mike.

So user could use any online Base64Decode website such as https://www.base64decode.org/ to decode it.

7

This one ~eb9442.tmp. The one that is created when you don't save the things in your editor or when toad crashes.

8

The Editor backup files are not encoded in base64 because they are simple text files and the contents can exist verbatim without issue. The SQL Recall history file uses base64 encoding because the file is XML and a SQL statement containing <, >, or other special characters will product invalid XML unless it is encoded in some way. Toad uses base64, but a simple CDATA section would have worked as well.

9

And it would cause some problem if you left the SQL Recall history file as a simple text file also?

10

Not if we used CDATA.

What is your use case here? The SQL Recall history file is a Toad configuration file and using it outside of Toad was not a concern when it was created.

11

I ran a couple of commands and they did not get added to my recall history. So then I started searching for other commands that I knew I had ran to see if they were also missing from my recall history.

12

How did you run these commands? SQL Recall only captures statements executed via Execute/Compile Statement at Caret (F9). It does not capture statements run via Execute as Script (F5) nor anything that is run outside of the Editor, e.g. Query Builder.

Also, check your SQL Recall filters to be sure nothing set is limiting the statements shown.

13

Also, since nothing can be done to make the SQL Recall history file plain text in any previously released version of Toad you will need to use another mechanism to search the contents. PowerShell would work wonderfully here. You can load the file, use XPath to select the node containing the statement text, and then decode the base64 value.

14

I did it with the execute/ compile button on the toolbar. No filters set.

Do you by any chance know what the powershell command is?

15

This script will create a new XML file where the SQL text and executed attribute have been decoded into a human-readable format. Adjust lines 1 and 2 to set the source and output filenames accordingly. The source filename is currently set to the default Toad 2024 R2 file path. Do not overwrite your source file with the output of this script. The output file will not be readable by Toad.

$sourceFilename = Join-Path $env:APPDATA "Quest Software\Toad for Oracle\24.2\User Files\SavedSQL.xml"
$destinationFilename = Join-Path $env:USERPROFILE "Downloads\ConvertedSavedSQL.xml"

$xml = New-Object System.Xml.XmlDocument
$xml.Load($sourceFilename)

# Convert the SQL text to plain text
$nodes = $xml.SelectNodes("//SQL")

foreach ($node in $nodes) {
    $decodedText = [System.Text.Encoding]::UTF8.GetString([Convert]::FromBase64String($node.InnerText))
    $cdata = $xml.CreateCDataSection($decodedText)
    $node.InnerXml = ""
    $node.AppendChild($cdata) | Out-Null
}

# Convert the timestamp to a human-readable format
$baseDate = New-Object System.DateTime(1899, 12, 30)
$nodes = $xml.SelectNodes("//*[@executed]")

foreach ($node in $nodes) {
    $executedValue = [float]$node.Attributes["executed"].Value
    $convertedDate = $baseDate.AddDays([math]::Floor($executedValue))
    $convertedDate = $convertedDate.AddSeconds(($executedValue % 1) * 86400)
    $formattedDate = $convertedDate.ToString()
    $node.Attributes["executed"].Value = $formattedDate
}

$xml.Save($destinationFilename)

I am using PowerShell Core. The script should work on older versions of PowerShell as well.

PS C:\Users\Administrator> $PSVersionTable.PSVersion

Major  Minor  Patch  PreReleaseLabel BuildLabel
-----  -----  -----  --------------- ----------
7      4      1