(c) Nigel Maddocks, 2007
Edit-DB ver 2.05.016, Doc ver 2.05.016
To access the SQL Entry facility, select SQL Entry from Object type in the Object-Type frame, having ensured that the other selections to the left, labelled Database or Schema (and Owner depending on database type), are completed.
The SQL Entry area will appear in the Object-Detail frame. To open it in a new window, use the button. This enables SQL to be entered in one window while keeping the ability to browse database objects in the other.
The Free Text SQL display appears as follows:
The text-area size is proportioned to appear as large as possible while remaining within the browser viewport. If the browser is re-sized, then the text area will be re-sized after the next page submission, i.e. after Execute SQL is pressed.
The SQL entered must conform to the allowed statements and syntax of the database type, otherwise an error will be reported back to you.
Common statements might be SELECT, UPDATE, INSERT, DELETE statements, or CREATE TABLE, ALTER TABLE instructions.
SELECT statments should return records which will be displayed on the page.
UPDATE and DELETE statements should affect a number of records. This number is displayed on the page.
Some database types allow the entering of a number of SQL statements which are run sequentially, by the database server.
In order to run a number of SQL statements in Oracle, enclose the statements within BEGIN ...statements... END; and ensure that each statement ends in a semicolon.
The text-area can be used as a scratch-pad where a number of SQL statements are jotted down. The particular statement to be run can be selected by highlighting it with a mouse or keyboard before pressing Execute SQL.
Calling Stored Procedures
Stored Procedures should be called using the syntax
CALL qualifier.stored-procedure-name(parameter1, ..., parameterN)The qualifier might be unnecessary, or might need to be the schema name, or in the case of Oracle, where the stored procedure might reside within a package, the package name.
The values for output parameters and return values can be displayed if, in the correct positions, the following substitution values are used:
For example, the procedure sp_Get_Employees_For_Role returns a recordset or employees for the parameter Role, but also has an output (int) parameter containing the average salary for that role. Both of these outputs can be displayed by calling the procedure using this syntax.
CALL or EXEC can be in upper or lower case. ?CUR and ?OUT can be in upper or lower case. ?CUR can be replaced by :X0 ... :X9 in upper or lower case and without initialisation.
The No Auto-Commit check box forces the SQL to be run without automatically committing any results.
The Refresh Connection check box forces the SQL to be run using a new database connection, rather than the cached connection. This can be useful when using SQL to call Oracle Stored Procedures which are under current development. In normal circumstances, there is no need to use a new database connection.