FAQ > Valid User-Defined SQL Statements for Oracle Database Targets
Valid User-Defined SQL Statements for Oracle Database Targets
When defining Oracle database targets, SQL statements that insert/update/delete data, or a stored procedure that returns no data, can be invoked from the User defined SQL statement tab of the Target Database Table dialog. Errors are issued by DMExpress when using inappropriate syntax or calling an unsupported statement.
The following error messages may be issued by DMExpress when using unsupported expressions or invalid syntax in the User defined SQL statement tab of the Target Database Table dialog:
ORA-06550 – Parsing Oracle user-defined SQL statements generates an ORA-06550 error when the SQL statements are entered on multiple lines.
ORA-00911 – Parsing a single Oracle user-defined SQL statement that is terminated with a semicolon, without BEGIN...END generates an ORA-00911 error.
You can enter the following as SQL text in the User defined SQL Statement tab of the Target Database Table dialog when defining Oracle targets:
SQL Data Manipulation Language (DML) statements such as INSERT, UPDATE, DELETE, and MERGE
When using a stored procedure to define an Oracle target, adhere to the following guidelines:
A stored procedure call must conform to the following syntax:
BEGIN <procedurename>(<inputparameters>); END;
BEGIN...END; are required and case-insensitive.
All the statements must be entered on a single line.
The Oracle stored procedure must have at least one input parameter.
All of the stored procedure input parameters must be of type varchar, varchar2, or date. If any other data type is expected, such as a number, you would need to use the appropriate data type conversion functions in the Oracle stored procedure.
The Oracle stored procedure is executed for each row that is passed to the target. If 100 rows are passed to the target, the procedure is executed 100 times; if no rows are passed to the target, the procedure is not executed.
DMExpress does not return parsing errors if you pass more than the required number of parameters to the stored procedure; verify the definition of the stored procedure.
Parameters are mapped in the order of occurrence, not by name.
In the following example, the Oracle stored procedure citybackup() is invoked with one input parameter:
begin citybackup(:1); end;
For details on user-defined SQL statements for Oracle targets, see Target Database Table dialog in the DMExpress Help.