Direct SQL Task Elements (Magic xpa 4.x)
To create a Direct SQL task:
-
From the Task menu, select the SQL Command option. The Direct SQL Command dialog box appears.
-
From the Database field, zoom to the Database List.
-
Select the database from which to run the explicit SQL.
Magic xpa does not check the syntax or semantics of the statement. The underlying database performs all statement processing at Runtime. If an error occurs at this stage, the Magic xpa task terminates with an explanatory message.
Constant strings that are used in the Direct SQL statement should be entered between two single quotation marks.
You can use the SQL command object to perform global updates, global deletes, DDL statements, and PL/ SQL Blocks.
The SQL command may also contain the name of a predefined procedure that was developed and compiled using DBMS facilities (stored procedures). If such a procedure name is specified as the SQL Command, the database procedure is invoked and executed by the RDBMS. These stored procedures can be used as though they were SQL command types. All the rules regarding Direct SQL commands described here apply to stored procedures when called.
You can use a stored procedure by specifying the reserved word exec in the following format:
exec procedure-name properties
If the procedure accepts more than one property, separate the properties with commas. If the procedure body is a Select statement, treat the procedure as if it is a regular Select statement. In other words, select Options/Generate Program. There are three APG characteristics that should be kept in mind when in a stored procedure:
-
APG invokes the stored procedure. If the stored procedure contains statements other than the Select statement, change the other statements into Remarks while using the APG.
-
Stored procedures that are called from Magic xpa receive arguments by value.
-
An output property can only result from a Select statement.
Magic xpa lets you write explicit SQL code by using the Direct SQL task or the DB SQL WHERE clause in a task. The statement produced is sent as-is to the gateway and no additional processing and binding is done. All statements produced in this way are sent to the database in ANSI format. The Unicode format is only supported for return values for an SQL task that produces a result.
You can use a 'WITH' clause in a Direct SQL statement. The clause works like a global temporary table in database systems. It is used to improve the execution speed of complex SQL queries.
You need to use WITH clause when a sub-query is required to be executed multiple times.
The syntax of the query using WITH clause is as follows:
|
WITH alias_name [(col_name [, col_name] ...)]
AS (subquery)
[, alias_name [(col_name [, col_name] ...)] AS (subquery)] ...
|
With clause is supported in Magic xpa Since version: 4.6.
|
|
WITH clause in Oracle is supported from version Oracle 9i R2.
|
Input Arguments
Output Arguments
