SQL4X Manager J

Edit and run Stored Procedures

The integrated Object Manager handles functions, stored procedures and other database objects for MS SQL Server, Sybase and Oracle database server. The Object Manager can be started with Database Navigators Corner Menu (select your database connection, catalog and schema -> open the Corner Menu in the Objects list -> select "Object Manager...").

Edit Stored Procedures and other database objects

Manage Database Objects (Stored Procedures, Functions, Trigger, etc.)

SQL4X Manager J includes a special assistant to manage database objects, display the object source code , change the objects. This feature is supported for Oracle, MS SQL Server and Sybase.

To access the Object Manager use Database Navigator, select your database connection and the catalog/schema your objects are in. Now open the corner menu form the Objects list (see snapshot on the left side) and select "Object Manager..." to open the Object Manager window.

To display your objects select the object type (Function, Java Source, Package, Package Body, Procedure, Trigger, Type or Type Body) from the Type selection and click Reload. All objects of the selected type available in the current schema are displayed in the list.

Display and Edit Object Source Code

To display the object's source code select the object in the list. The source code is displayed on the right side. You can modify the source code and click "Update Object" to save the changes back to your database server.

Renaming Objects

If you change the object name in the source code view and click "Update Object" a new copy of this object is created with the new name. The original object is not modified by this action. To rename a object you need to delete the original after creating a copy.

Adding new Objects

Click the + sign on the right side of the object list to create a new object. The Create Database Object sheet is displayed. Enter the new object's name and type in the sheet and click create. You can now enter the source code for your object in the source code view. Click "Update Object" to actually create the object.

Removing Objects

Select the object you want to delete in the object list and click the - sign on the right side of the object list to drop the selected object.

Running Stored Procedures

Every database vendor has a different method for invoking stored procedures. The methods for Oracle, MS SQL Server and Sybase database servers are described in this chapter. If you are not sure how your database server handles stored procedures take a look at your database vendors manual on stored procedures.

Run Stored Procedures with Oracle database servers

Executing anonymous PL/SQL blocks and stored procedures

Use the SQL Console to run anonymous PL/SQL code blocks. The processing is quite similar to Oracle's SQL*Plus. Statements beginning with declare or begin are passed on to the Oracle database server for processing.

The following script can be executed with SQL Console to demonstrate how PL/SQL blocks are executed:

create table tmpval(first int);

declare
  i int;
begin
  for i in 0..5 loop
    insert into tmpval values (i);
  end loop;
end;
/

select *
from tmpval;

Please note the slash (/) on a line of it's own after the PL/SQL code block to start the execution.

To execute stored procedures you have to enclose the procedure call in an anonymous PL/SQL block like above (begin/end block).

Run Stored Procedures with MS SQL Server and Sybase database servers

Run stored procedures with SQL Viewer and SQL Console

MS SQL Server and Sybase support the "EXEC" command to run stored procedures. SQL Console and SQL Viewer both support the EXEC command and display the result set generated by your stored procedure call. If your stored procedure returns more than one result set SQL Viewer displays the result sets as tabbed view and SQL Console displays the results sets as list.

exec sp_configure

This command executes the sp_configure stored procedure and displays the result set.