SQLPlus Utilities

From Vendita MAS
Jump to: navigation, search

There are two SQLPlus oriented utilities for MAS.

Process Description
vendita.oracle.rdbms.cli.sqlplus.execute Run an ad-hoc query against an Oracle Database
vendita.oracle.rdbms.cli.sqlplus.script Capture a SQLPlus script into a MAS namespace for repeated execution

SQPlus Ad Hoc Query

SQLPlus Ad Hoc Query capability is provide with the vendita.oracle.rdbms.cli.sqlplus.execute process.

This process allows a single ad-hoc query to be run against SQLPlus. With this process, MAS first connects to the Unix/Linux server hosting the Oracle database, with SSH. Once this connection is established, a second connection is created to SQLPlus. This ability to nest connections of a feature unique to MAS. This process requires that two MAS accounts be created: a SSH account, and an Oracle account on port 1521. The SSH account must be for the Oracle user on the database server, or any user that has access to the Oracle binaries on the database servers. Both accounts must be created prior to running this process.

New Job form for sqlplus_execute

Parameters for sqlplus_execute

The table below lists the parameters for this process.

Parameter Description Required
shell MAS account for ssh connection to Oracle database server. Should be the "oracle" OS user or other user that owns Oracle binaries. yes
dbms MAS account for Oracle RDBMS connection to database. Should contain a valid database username and password. yes
script SQLPlus commands including SQL, formatting commands, DDL/DML, database control commands. Can also be commands with "@" to run a local file containing sql. yes

When all parameters have been entered press "Run" to run the query. The process output can be viewed under the "jobs" menu.

SQLPlus "Query Import

SQLPlus Query Import capability is through the vendita.oracle.rdbms.cli.sqlplus.script process.

This process is similar to the "query_import" process, but allows direct connection to a SSH connection and an Oracle database connection. sqlplus.sript allows MAS users to capture or store sqlplus queries as MAS processes. MAS processes that are imported are organized with namespaces. Prior to using MAS Query Import, users are advised to create namespaces to organized captured queries. Following the link to learn more about namespaces in MAS.
[Learn more about MAS namespaces]

The graphic below provides an overview of the SQLPlus Script process.

The steps for using sqlplus script are:
  1. Determine a namespace location for process.
  2. Determine a name for the process.
  3. Enter all parameters on form. Parameters are listed in the table below.
  4. Run the process.
  5. Execute a compile from the "gear" menu.
  6. Process can now be run.

The new job form form sqlplus.script is shown below:

sqlplus_script parameters

The parameters for this process are documented in the table below.

Parameter Description Required
name Fully Qualified Name
The parameters for this process require selection of a full qualified name or “FQN”. A FQN consists of a namespace location and a name for the process. The namespace is selected with the "namespace" parameter below. The process name can be any name consisting of letters, numbers, and the underscore character. Note the process name cannot begin with a number.
namespace Namespace
This is the namespace where the process will be created.The namespace needs to exist before the query import process is run. The namespace is selected with the dropdown menu.
description Description of Process
This is a description of the process.
script SQLPlus Script
This is a SQLPlus script consisting of one or more queries, SQLPlus commands for formatting, DML, DDL, database control commands.

When all parameters have been entered, run the process. After the process runs successfully, it must be compiled. Not all MAS users have the compile privilege. By default the "mas" user has the compile privilege. Select the menu in the upper right corner of the screen, to determine if compile is possible. See the screenshot below.

If compile is possible, it will appear in the menu. Select "compile" to run a compile. This will require approximately 5 seconds. After the compile, the new process is ready to use. Select entities and search for the process or navigate to it in the namespace to run the process.