Toad World

Getting Started with Toad DevOps Toolkit – Running the PowerShell Demos

Avatar photo
Post author:Written By

In an earlier article, Installing and Configuring Toad DevOps Toolkit, we discussed installing Toad DevOps Toolkit, installing  PowerShell 3.0, and starting  a Toad DevOps Toolkit instance. In a second article, Getting Started with Toad DevOps Toolkit  – Installing  and Configuring Oracle Databases, we discussed creating, configuring and starting two Oracle database instances; one for Source database and the other for Target database for running Toad DevOps Toolkit DevOps capabilities.  Some of the use cases of Toad DevOps Toolkit are:

  • Integrate with an automation server such as Jenkins, Bamboo, or Team Foundation Server to automate the database development, testing and deployment tasks
  • Run unit tests for PL/SQL code to determine their functional accuracy.
  • Improve the performance of DevOps processes
  • Code review with user-defined code analysis rule sets to improve the quality of code
  • Generate reports in HTML and JSON formats

In this third article of the series, we shall run the PowerShell example scripts included with the Toad DevOps Toolkit.  The examples make TDT (Toad Development Toolkit) calls for the following tasks:

  • Validate Oracle Database Connections
  • Perform Code Analysis and Execute Script
  • Compare Database, Schemas and Tables
  • Run Unit Testing and Debug

This article has the following sections.

  • Setting the Environment
  • Connecting to PowerShell
  • Installing the DEMO User
  • Running Other PowerShell Examples

 

Setting the Environment

Toad DevOps Toolkit must be installed and an instance of the toolkit started. PowerShell 3.0 or later must also be installed.  The example scripts are based on PowerShell 5.0 and PowerShell 3.0 may be used. Two Oracle Database instances must be running.

 

Connecting to PowerShell

PowerShell 3.0 or later must be run with Windows administrator privileges. Right-click on Windows PowerShell and click on Run as administrator as shown in Figure 1.

5238.d1

Figure 1. Running PowerShell as Administrator

Administrator shell for Windows PowerShell gets started, as shown in Figure 2.

3681.d2

Figure 2. Administrator Windows PowerShell

Change directory (cd) to the Toad DevOps Toolkit directory in which the PowerShell examples are installed.

cd "C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell"

The current directory gets set to the PowerShell examples directory, as shown in Figure 3.

3681.d3

Figure 3.Current Directory set to PowerShell Examples Directory

 

Installing the DEMO Schema

As mentioned before, two Oracle Database instances are required, a Source and a Target database. The Install batch script included with the PowerShell examples installs the DEMO schema on both the Source and the Target databases.  The user for both Source and Target databases must have SYSDBA privileges.  Run the./INSTALL command from the PowerShell examples directory.

PS C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell> ./INSTALL

Oracle DEMO Schema Install gets started, as shown in Figure 4.  First, the login information for the Source Database must be provided. The first prompt is Enter Source Database Name: as shown in Figure 4.

6305.d4

Figure 4. Enter Source Database Name:

Specify the Source Database Name as XE as shown in Figure 5. Specify Source Database User Name as SYS, which has SYSDBA privileges.  Specify the Password for the SYS user. Click on Enter after adding each value.  After the Source Login information has been provided the Target Database Login information must be provided, as shown by the prompt Enter Target Database Name in Figure 5.

0028.d5

Figure 5. Target Database Login information

Specify the Target Database Name as ORAXE as shown in Figure 6. Specify the Target Database User Name as SYS, which has SYSDBA privileges. Specify the Password for the SYSDBA user. Click on Enter after adding each value. The prompt DEMO schema will be created on XE and ORAXE. Continue (Y/N)?: appears. Specify Y as shown in Figure 6 and select Enter.

0028.d6

Figure 6. DOM schema will be created on XE and ORAXE. Continue (Y/N)?

The install.sql script in the C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell\PowerShell\PSScripts directory starts running, as shown in the output in Figure 7.  First, $TDT connects with the Source Oracle Database Instance and the DEMO user gets created.

 8461.d7

Figure 7. Running install.sql

After creating the DEMO schema on the source database the $TDT instance disconnects from the source database. Subsequently the Target Oracle Database gets connected to, and the DEMO schema gets created on the target database also. After creating the DEMO schema on both the Source and Target databases the $TDT instance disconnects from the databases and updates the configuration file Config.ps1, also in the C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell\PowerShell\PSScripts directory, with information about the installing the DEMO schema including the Source and Target databases, as shown in Figure 8.   The script start and end timestamp gets output.

2185.d8

Figure 8. Output from running the ./INSTALL  PowerShell Batch Script

A more detailed output from running the ./INSTALL PowerShell script is as follows.

C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell>REM --------------------
---------------------------------------------
C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell>REM  Copyright (c) 2017
Quest Software
C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell>REM  Name: Install.bat
 
C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell>REM --------------------
---------------------------------------------
Transcript started, output file is C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples
\PowerShell\PowerShell\PSScripts\..\..\Log\OracleDemoSchemaInstall_Log.txt
-------------------------------------------------------------
Oracle DEMO Schema Install
-------------------------------------------------------------
Getting Source Database Login Information...
Enter Source Database Name: XE
Enter Source Database Username (Requires SYSDBA privs): SYS
Enter Source Database Password: *********
 
Getting Target Database Login Information...
Enter Target Database Name: ORAXE
Enter Target Database Username (Requires SYSDBA privs): SYS
Enter Target Database Password: *********
 
DEMO schema will be created on XE and ORAXE. Continue [Y/N]?: Y
Running Install.sql
 
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 6 20:06:48 2017
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
Connected.
 
User created.
 
 
Grant succeeded.
 
Grant succeeded.
 
User altered.
 
Grant succeeded.
 
Grant succeeded.
 
User altered.
 
Function created.
 
Table created.
 
Connected.
 
User created.
 
Grant succeeded.
 
Grant succeeded.
 
User altered.
Grant succeeded.
Grant succeeded.
User altered.
 
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-------------------------------------------------------------
Updating Config.ps1 script...
    SourceDatabase = XE
    TargetDatabase = ORAXE
-------------------------------------------------------------
-------------------------------------------------------------
Script Start Date/Time: 12/06/2017 20:06:16
Script End Date/Time  : 12/06/2017 20:06:49
-------------------------------------------------------------
Transcript stopped, output file is C:\Program Files\Quest Software\Toad DevOps Toolkit\
    Toad DevOps Toolkit 1.0\examples\PowerShell\Log\OracleDemoSchemaInstall_Log.txt
S C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell>

A log file C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell\Log\OracleDemoSchemaInstall_Log.txt gets created.

If the INSTALL PowerShell script runs partially, creating the DEMO schemas on Source and Target databases but generating some error before completing, the DEMO schema must be dropped in the Source and Target databases before running the INSTALL script again.  The DROP USER statement used to drop the DEMO schema is run in SQL*Plus and must include CASCADE.

SQL> DROP USER DEMO CASCADE;
User dropped.

The DEMO schema may get created on the Source database only, or on both the Source and Target databases, and the INSTALL script might generate an error subsequently.  One of the possible reasons for the INSTALL script generating an error after creating the DEMO schema on the Source database is that a connection does not get established to the Target database. A possible reason for the INSTALLscript generating an error after creating the DEMO schema on both the Source and Target databases is that some required database object is not found.

 

Running other PowerShell Examples

Next, we shall run some of the other PowerShell examples included with Toad DevOps Toolkit.

 

Running ValidationChecks Example

The ValidationChecks example runs tests to determine the connectivity of the Source and Target Databases with an instance of Toad DevOps Toolkit $TDT.  Run the example with the following command from the examples directory.

PS C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell> ./ValidationChecks

The ValidationChecks example validates the following connectivity tests, in which the $TDT object is invoked for each task and test.

  • The Toad.ToadAutoObject exists, which implies that an instance of Toad DevOps Toolkit is available.
  • The Toad.ToadAutoObject instance gets started.
  • The connection to the Source demo database XE gets established. The Source database version gets output.
  • The connection to the Source demo database XE gets disconnected.
  • The connection to the Target demo database ORAXE gets established. The Target database version gets output.
  • The connection to the Target demo database ORAXE gets disconnected.

The output from the ValidationChecks example is shown in Figure 9.

5810.d9

Figure 9. Output from the ValidationChecks example

A log file C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell\Log\20171206202026_Log.txt gets generated.

 

Running CompareDatabases Example

Next, run the CompareDatabases PowerShell script. The CompareDatabases example performs the following tasks or tests with an instance of Toad DevOps Toolkit $TDT being invoked for each task and test.

  • Sets main parameters from Config file parameters.
  • Includes/Excludes objects. Sets object types to include to all types.
  • Runs the parameters supplied from the Config file.
  • Exports properties to JSON Snapshot file DBCompare_Snapshot.json.
  • Imports from JSON Snapshot file.
  • Runs the parameters supplied from the JSON file.
  • Adds roles.
  • Runs an Object set count.
  • Performs a comparison of database objects and outputs a report.

The output from the CompareDatabases example is shown in Figure 10.

4251.d10

Figure 10. CompareDatabases Example Output

 

Running CompareSchemas Example

The CompareSchemas example performs the same comparisons as the CompareDatabases example and some additional tasks, which are as follows.  An instance of Toad DevOps Toolkit $TDT is invoked for each task and test.

  • Sets main parameters from Config file parameters.
  • Includes/Excludes Storage options and Type options in addition to the Object options.
  • Runs the parameters supplied from the Config file.
  • Exports properties to JSON Snapshot file SchemaCompare_Snapshot.json.
  • Imports from JSON Snapshot file.
  • Runs the parameters supplied from the JSON file.
  • Adds roles.
  • Runs an Object set count.
  • Gets difference details in CSV, HTML and TXT formats.
  • Performs a comparison of database objects and outputs a report.

Run the CompareSchemas example with the following command run from the PowerShell examples directory.

PS C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell> ./CompareSchemas

The output from the CompareSchemas example is shown in Figure 11.

4251.d11

Figure 11. CompareSchemas Example Output

 

Running CompareTables Example

The CompareTables example performs the following tasks and tests with an instance of Toad DevOps Toolkit $TDT being invoked for each task and test.

  • Sets main parameters from Config file parameters.
  • Runs the parameters supplied from the Config file.
  • Exports properties to JSON Snapshot file TableCompare_Snapshot.json.
  • Imports from JSON Snapshot file.
  • Runs the parameters supplied from the JSON file.
  • Performs a comparison of database objects and outputs a report.

Run the CompareTables example with the following command run from the PowerShell examples directory.

PS C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell> ./CompareTables

The output from the CompareTables example is shown in Figure 12.

7875.d12

Figure 12. CompareTables  Example Output

 

Running CodeAnalysis Example

The CodeAnalysis example determines the code quality using a RuleSet. A default RuleSet is provided and custom RuleSets may be used. The CodeAnalysis example processes both files and database objects and runs the following tasks.

  • Loads and saves XML properties files
  • Runs Code Analysis
  • Creates reports

Run the CodeAnalysis example with the following command run from the PowerShell examples directory.

PS C:\Program Files\Quest Software\Toad DevOps Toolkit\Toad DevOps Toolkit 1.0\examples\PowerShell> ./CodeAnalysis

The output from the CodeAnalysis example is shown in Figure 13.

6327.d13

Figure 13, Output from the CodeAnalysis Example

 

Running All Demos

All PowerShell demos may be run with the single command ./AllDemos. One demo gets run after another, as shown in Figure 14.

 6327.d14

Figure 14. Output from PowerShell Batch Script AllDemos

 

Conclusion

In this article we discussed running some PowerShell demos with Toad DevOps Toolkit. An instance of the toolkit $TDT gets invoked for each test or task. A log file gets generated for each demo. A report gets generated for each of the comparison examples.  The example PowerShell scripts may be customized or used as such for code analysis and for generating comparison reports.  Having used the example PowerShell scripts with Toad DevOps Toolkit and two Oracle Database instances running on a local machine, some of the other capabilities of Toad DevOps Toolkit may be explored. TDT is supported with Oracle Database instances running on two cloud platform services: Oracle Database Cloud service, and Amazon Elastic Compute Cloud (EC2). TDT may be run using a Jenkins pipeline to automate some of the cmdlets/scripts.

Tags: ,

About the Author

Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.

Notable Replies

  1. says:
    allamyth

    Hi,
    I would really appreciate your help in regards to below issue that I am facing.
    So, I have 2 different servers. One server has TDT installed and other has oracle database installed. I am trying to to make connection using TDT to this database server. I am using below format.
    $Source = $TDT.Connections.NewConnection("userid/pass123@hostname:PORT/databaseschemaName")

    I get below Exception:

    Access violation at address 0000000006BED2C0 in module 'tdt.exe'. Read of address 00000000000000C4

    At C:\Users\cloud-user\Documents\test.ps1:15 char:6

    + $Source = $TDT.Connections.NewConnection("user/pass123@hostname ...

    *+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*

      • CategoryInfo : OperationStopped: (:slight_smile: , COMException*
      • FullyQualifiedErrorId : System.Runtime.InteropServices.COMException*

    Am I missing something in connection String as error points to the line where I am trying to connect to database server. I am really stuck with this issue. I would really appreciate your help to resolve this issue. Thank You!

  2. says:
    jbowman

    Hi Amit,

    In order to have the demos work, you'll need an Oracle client installed on the server on which you want to run TDT. This client should match the same "bit-ness" as TDT (32-bit Oracle client for 32-bit TDT, and 64-bit Oracle client for 64-bit TDT).

    Once you have that installed, you'll need to create an instance of the TDT object within your script before you start using it. You can accomplish that with the following command:

    $TDT = New-Object -ComObject Toad.ToadAutoObject

    This will create an instance of the COM object so that you can use it. From there, you can use the connection string you need, as in your statement above. It's usually a good idea to enclose your script within a try...finally block so that you can make sure that TDT's process is released when you're done.

    An example of this within a script, using your connection string, might be:

    $TDT = New-Object -ComObject Toad.ToadAutoObject

try {
  $Source = $TDT.Connections.NewConnection("userid/pass@databasename")
  
  # Do something with the connection
  $Script = $TDT.Scripts.Add()
  $Script.Connection = $Source
  $Script.IncludeOutput = $TRUE
  $Script.MaxRows = 100
  $Script.InputFile = "C:\Temp\myquery.sql"

  # Execute the script
  $Script.Execute()

  # Output Results
  $ScriptOutput = $Script.OutputText
}
finally {
  $TDT.Quit()
}

    You can find further examples within the TDT installation folder under "Examples\COM\PowerShell". You can also find various tutorial videos and articles on our blog site here:

    If you have additional questions, feel free to post them to the Toad Devops Toolkit forum, which can be located here:

    https://forums.toadworld.com/c/toad-devops-toolkit

    Thanks for your question!

    -John

  3. says:
    allamyth

    Hi John,

    Thanks for taking your time and looking into my issue. Yes, I did miss on installing Oracle client. Now, I am able to connect to database and execute queries after installing Oracle client, So Thank You! I will definitely reach out on TDT forum for any further questions. Have a good day!

    Many Thanks,
    Amit

Continue Discussion