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.