The following job aims at connecting to an Oracle Database containing Social Security
Numbers and their holders’ name, calling a stored procedure that checks the SSN format
of against a standard ###-##-#### format. Then the verification output results, 1 for
valid format and 0 for wrong format get displayed onto the execution console.

• Drag and drop the following components from the Palette: tOracleConnection,
  tOracleInput, tOracleSP and tLogRow.

• Link the tOracleConnection to the tOracleInput using a Then
  Run connection as no data is handled here.

• And connect the other components using a Row
  Main link as rows are to be passed on as parameter to the SP
  component and to the console.

• In the tOracleConnection, define the details
  of connection to the relevant Database. You will then be able to reuse this
  information in all other DB-related components.

• Then select the tOracleInput and define its
  properties.

• Select the Use an existing connection check
  box and select the tOracleConnection component
  in the list in order to reuse the connection details that you already
  set.

• Select Repository as Property type as the Oracle schema is defined in the DB Oracle
  connection entry of the Repository. If you haven’t recorded the Oracle DB
  details in the Repository, then fill in the
  Schema name manually.

• Then select Repository as Schema, and retrieve the relevant schema
  corresponding to your Oracle DB table.

• In this example, the SSN table has a four-column schema that includes
  ID, NAME,
  CITY and SSNUMBER.

• In the Query field, type in the following
  Select query or select it in the list, if you stored it in the
  Repository.

  select ID, NAME, CITY, SSNUMBER from SSN

• Then select the tOracleSP and define its
  Basic settings.

• Like for the tOracleInput component, select
  Repository in the Property type field and select the Use an
  existing connection check box, then select the relevant entries
  in the respective list.

• The schema used for the tOracleSP slightly
  differs from the input schema. Indeed, an extra column
  (SSN_Valid) is added to the Input schema. This column
  will hold the format validity status (1 or
  0) produced by the procedure.

• In the SP Name field, type in the exact name
  of the stored procedure (or function) as called in the Database. In this use
  case, the stored procedure name is is_ssn.

• The basic function used in this particular example is as follows:

  CREATE OR REPLACE FUNCTION is_ssn(string_in VARCHAR2) RETURN PLS_INTEGER IS -- validating ###-##-#### format BEGIN IF TRANSLATE(string_in, '0123456789A', 'AAAAAAAAAAB') = 'AAA-AA-AAAA' THEN RETURN 1; END IF; RETURN 0; END is_ssn; /

  1 2 3 4 5 6 7 8 9 10 11 12

  CREATE OR REPLACE FUNCTION is_ssn(string_in VARCHAR2) RETURN PLS_INTEGER IS -- validating ###-##-#### format BEGIN   IF TRANSLATE(string_in, '0123456789A', 'AAAAAAAAAAB') =      'AAA-AA-AAAA' THEN     RETURN 1;   END IF;   RETURN 0; END is_ssn; /

• As a return value is expected in this use case, the procedure acts as a
  function, so select the Is function check box.

• The only return value expected is based on the ssn_valid
  column, hence select the relevant list entry.

• In the Parameters area, define the input and
  output parameters used in the procedure. In this use case, only the
  SSNumber column from the schema is used in the
  procedure.

• Click the plus sign to add a line to the table and select the relevant column
  (SSNumber) and type (IN).

• Then select the tLogRow component and click
  Sync Column to make sure the schema is passed on from the preceding tOracleSP component.

• Select the Print values in cells of a table
  check box to facilitate the output reading.

• Then save your job and press F6 to run it.

On the console, you can read the output results. All input schema columns are
displayed even though they are not used as parameters in the stored procedure.

The final column shows the expected return value, whether the SS Number checked is
valid or not.