IBM Support

How to create an Oracle database view using Rational Robot

Troubleshooting


Problem

This document presents an example on how to create an Oracle 9i database view with IBM® Rational® Robot. It also describes how to verify the existence of the view.

Resolving The Problem

To create an Oracle database view, follow these steps:

  1. Choose an appropriate ODBC driver. For Oracle 9i you can choose ORAHome92.
  2. Verify that you can create a view with the appropriate database tool. For example, in Oracle databases you can use the following command in Oracle SQL*Plus:

    Create or Replace View <viewname> AS
  3. Compose your Rational Robot script with the following actions:
    • Open the connection to the database.
    • Verify if the connection is open.
    • Create or replace the view.
    • Verify if the view exists.
    • Close the connection.

In the following example:

  • The SQLOpen function opens the connection to the database and returns a connection number
  • The SQLExecQuery function handles the Oracle SQL command and returns the number of rows that it finds. If this is not applicable, it returns -1. The SQL command is:
    Create or Replace View <viewname> As
  • The SQLExecQuery function checks if the view exists.
  • If view exists, the function selects rows from the view and returns the number of rows. This should be greater or equal to 0.
  • The script saves the rows in a file.
  • The SQLClose function closes the database connection.

Note: The select statement in SQLRetrieveToFile cannot return more than 100 columns. This is a limitation of the function.


Sub Main

    ' The script creates a view MyView in the ReqPro Database with
    ' userID=reqpro
    ' and password=reqpro
    Dim Result                 As Integer
    Dim Connection             As Long
    Dim Retcode                As Long
    Dim OutputStr              As String
    Dim Query                  As String
       
    Const ViewCommand          
    _ As String="Create Or Replace View "
    Const View                 As String="MyView "
    Const Qstart               As String="As "
    Const Selection            As String=
              _ "Select  *
              _ From reqpro.rqUserDefinedListValues
             _ Where
              _  reqpro.rqUserDefinedListValues.RequirementID
              _ = 20872;"
    Const PassMessage          As String="View Created or Replaced "
    Const FailMessage          As String="View not created "
    Const Filename             As String="c:\temp\myfile2.txt"    
    Const ColumnDelimiter      As String="%"
   
'   open the connection
    Connection =
     _ SQLOpen(
     _ "DSN=reqpro;
     _ UID=reqpro;
     _ pwd=reqpro",
     _ OutputStr,prompt:=2)
'
    If Connection >= 0 Then
     ' Execute the query
     ' Create Or Replace View MyView AS
     ' Select  * From reqpro.rqUserDefinedListValues
    ' Where reqpro.rqUserDefinedListValues.RequirementID
     ' = 20872;
     
      Query = ViewCommand & View & Qstart & Selection
 
      Retcode = SQLExecQuery(Connection,Query)

      If Retcode < 0 Then
        Query = "Select * From ReqPro." & View
        Retcode = SQLExecQuery(Connection,Query)
        If Retcode >=0 Then
          SQALogMessage sqaPass, PassMessage & Retcode
                                     _  & " rows", ""
          Retcode =
          _ SQLRetrieveToFile(connection:=Connection,
          _ destination:=Filename,
          _ columnNames:=1,columnDelimiter:=ColumnDelimiter)
     Else
          SQALogMessage sqaFail, FailMessage, ""
        End If    
      End If
     
    End If
   'Close the connection
    Retcode = SQLClose(Connection)
   
End Sub

Note:


SQLRetrieveToFile cannot retrieve more than 100 columns. This is limitaton of the product.

[{"Product":{"code":"SSSHDX","label":"Rational Robot"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Scripting","Platform":[{"code":"PF033","label":"Windows"}],"Version":"2003.06.00;2003.06.10;2003.06.12;2003.06.13;2003.06.14;2003.06.15;2003.06.16;7.0;7.0.0.1;7.0.1;7.0.0.3;7.0.1.2;7.0.0.4;7.0.1.3;7.0.2","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
16 June 2018

UID

swg21260707

Page Feedback