SFTP
You can use Secure File Transfer Protocol (SFTP) to transfer a Structured Query Language (SQL) script to Reporting. Once the user directory is created and assigned to you and the file is sent to user directory. Reporting processes your script, using the following procedure:
- Scans user directory at regular intervals.
- Reads the script the user sent (via SFTP) into the user directory.
- Completes the appropriate security verification.
- Retrieves the requested data from Reporting, using the user SQL query.
- Generates an output file, in standard SQL*Plus output format, containing the result of user SQL query and it will send you an email if you requested email notifications.
After the script is executed, the output file remains in user directory. Use SFTP to retrieve the output file and analyze the results.
SQL*NET
SQL*NET you may use a third party software, or develop you own application to access Reporting via SQL*NET. Additionally, Reporting that is accessed via SQL*NET currently utilizes the Microsoft SQL Server 2019 or a higher version.
Script Guidelines
Send (via SFTP) the SQL and/or shell scripts into your Reporting home directory by following the guidelines below.
Shell Scripts
Use the following guidelines for shell scripts:
- The SQL scripts query the Reporting environment.
- Shell scripts are used to maintain the user environment.
- The syntax for shell scripts is explained in the following SQL Scripts information.
- Currently, only the rm command, that is, the remove file command, is supported in shell scripts. Within the rm command, the following meta characters are not allowed:
|
; (Semicolon) |
, (Comma) |
& (Ampersand) |
| (Pipe; Vertical) |
|
< (Less Than) |
> (Greater) |
/ (Slash) |
\ (Backslash) |
- All shell scripts must end with .sh.
- Error files are written if there is a syntax error in a shell script.
SQL Scripts
Use the following guidelines for SQL scripts:
- Only standard SQL commands should be included between the script header and footer. All SQL script file names must end in .SQL. For a list of standard SQL commands, SQL*NET you can research this topic further by searching on the internet and using search engines such as Google, Bing, etc.
- SQL scripts must conform to SQL*Plus (a superset of ANSI SQL).
- Output files are written upon successful completion of SQL scripts. Error files are written if there is a syntax error in an SQL script or if a SQL script completes with an error.
Naming Conventions
Use the naming conventions and format for SQL and shell scripts, output files and error files shown below:
Headers and Trailers
Use the appropriate header and trailer formats in your script(s), as shown below.
SQL Header and Trailer Lines
|
SQL Script Header: EMAIL: <emailaddress1@host> ... SQL START Enter your SQL script here. |
|
SQL Script Trailer: SQL END |
The EMAIL tag and email address arguments are optional. If the email tag is included, the tag must precede the SQL START tag. Email arguments must be separated by white space
Shell Header and Trailer Lines
|
Shell Script Header: EMAIL<emailaddress1@host>... SH START rm -<option> <filename> ... |
|
Shell Script Trailer: SH END |
The EMAIL tag and email address arguments are optional. If the EMAIL tag is included, the tag must precede the SH START tag. Email arguments must be separated by white space.
Email Notifications
The email option is available for requesting an email notification after execution of the shell or SQL script. The email message includes the:
- script name
- date/time of the script execution
- name of any generated output or error files