In this section:
Once you have opened a new data flow, you need to identify the data sources you want to use, and add them to the flow. Data sources are represented by synonyms in the navigation pane, which are under an application directory folder when the Synonyms button is selected. The options available when you right-click a synonym are described in Navigation Pane: Synonyms. For guidelines on synonyms used as data sources, see Using Synonyms for Data Sources and Data Targets.
When creating a data flow, information about the data source, such as the column names, is required and supplied when the DataMigrator Server is connected.
When creating or editing a data flow, in order to test your flow, data sources referenced in the flow must be available.
Before you add a data source to a flow, view the columns to verify that it contains the data that you want and that the data is available.
To view a sample of the data that a synonym will produce, right-click the synonym in the navigation pane and select Sample Data. The sample data will appear in the data flow workspace, as shown in the following image.
Note: After a synonym has been dragged into the data flow workspace, you can view its columns, sample data, and transformations. Right-click the synonym and select Toggle. You can view the data by selecting either the Columns, Sample Data, or Test Transforms tab.
An empty data flow appears in the data flow workspace. The Data Flow tab opens by default, with an SQL object in the center of the workspace.
Right-click in the workspace to the left of the SQL object, and select Add Source.
The Select Synonym dialog box opens.
With the data flow open in the workspace:
The Select Synonym dialog box opens.
Note: If you change a data source that has source transformations associated with it, the transformations are preserved, but they will appear red if the new source does not contain all of the columns used in the transformation.
You can use a relational stored procedure as a data source in a flow if you have created a synonym for that stored procedure. For information on creating a synonym, see How to Create a Synonym for a Relational Stored Procedure.
One use for this would be to perform a lookup, for example, passing parameter values to the stored procedure and retrieving an answer set based on the supplied parameter.
This is supported for:
If you have created a synonym for a relational stored procedure, it will appear in the navigation pane, along with all other synonyms.
As an example, we are using an Oracle stored procedure that has empno as a parameter, and that retrieves information associated with that employee number. The Oracle stored procedure goes against the EMP Oracle sample table. We will join the stored procedure to a flat file that contains valid employee numbers.
The sample stored procedure is shown below.
/* create get_emp routine */ CREATE OR REPLACE PACKAGE get_emp AUTHID CURRENT_USER AS PROCEDURE get_emp_proc ( pempno IN scott.emp.empno%TYPE, pename OUT scott.emp.ename%TYPE, pjob OUT scott.emp.job%TYPE, pMgr OUT scott.emp.mgr%TYPE ); END get_emp;
/ show err CREATE OR REPLACE PACKAGE BODY get_emp AS PROCEDURE get_emp_proc ( pempno IN scott.emp.empno%TYPE, pename OUT scott.emp.ename%TYPE, pjob OUT scott.emp.job%TYPE, pMgr OUT scott.emp.mgr%TYPE )
IS CURSOR gRec_cur IS SELECT ename, job, mgr FROM scott.emp WHERE EMPNO = pempno; gRec_rec gRec_cur%ROWTYPE; BEGIN
OPEN gRec_cur; FETCH gRec_cur INTO gRec_rec; IF (gRec_cur%FOUND) THEN pename := gRec_rec.ename ; pjob := gRec_rec.job ; pmgr := gRec_rec.mgr ; END IF; CLOSE gRec_cur;
EXCEPTION WHEN OTHERS THEN ROLLBACK ; RAISE; END get_emp_proc; END get_emp ; / show error GRANT EXECUTE ON get_emp TO public ;
To use a relational stored procedure as a data source:
Note: The flow we are using joins a flat file named ora_emps with the get_emp_get_emp_proc Oracle stored procedure. The flat file has only one column, empno, which is joined to the stored procedure. For each empno in the flat file, the stored procedure runs and retrieves the name, job, and manager information for that empno.
The sample data dialog box opens.
Note: If the stored procedure can have more than one answer set, you need to select the appropriate one.
The sample data appears in the workspace.
Whenever you test sample data, you will need to supply the values for the parameters.
After a synonym has been dragged into the workspace, the following options are available when you right-click the synonym:
Opens a window that allows you to view the columns, sample data, and transformations of the synonym.
Updates the list of columns in a synonym.
Opens the Source Transformations window. For details, see Creating a Source Transformation in a Data Source Object
Opens the synonym in the workspace. The file, segments, and columns appear on the left and their attributes and values appear on the right.
Opens the synonym code in the workspace, where it can be edited.
Shows a sample of the data associated with the synonym.
Provides the data characteristics for synonym columns.
Opens the Select Synonym dialog box, where a different source can be selected.
Deletes the synonym from the data flow.
Opens the general attributes of the synonym, as shown in the following image.
You can update the list of data sources by refreshing the tree. You can update the list of columns in a data source by refreshing the source.
This is useful when data sources are added to the server, or when columns are added to a data source while you are building a data flow, and you want these changes reflected in the data source or column list.
In the navigation pane, right-click the server, the Applications Directories folder, or subfolder, and select Refresh.