Use SFTP to transfer an SQL script to Reporting application. Once the user directory is created and assigned to user and the file is sent to user directory. Reporting application processes user 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 application, using the user SQL query.
- Generates an output file, in standard SQL*Plus output format, containing the result of user SQL query and sends the user an email if the user requested email notification.
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 users may use a third party software, or develop their 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.
Send (via SFTP) the SQL and/or shell scripts into the user Reporting home directory by following the guidelines below.
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 Chapter 2.1.4 SQL Scripts.
- 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:
| (Pipe; Vertical)
< (Less Than)
- All shell scripts must end with .sh.
- Error files are written if there is a syntax error in a shell script.
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 users 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.
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> ...
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.
The email option is available for requesting 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