Teamworks 6.2: Calling Oracle Storec Procedures

First, lets create a table with sample data to work with:

CREATE TABLE BryanTestTable (
ID INT NOT NULL,
Name VARCHAR2(128) NULL
);

INSERT INTO BryanTestTable VALUES(1, 'Bryan');
INSERT INTO BryanTestTable VALUES(2, 'Josh');
INSERT INTO BryanTestTable VALUES(3, 'April');
COMMIT;

>SELECT * FROM BryanTestTable;

ID                     NAME
-------------------------
1                      Bryan
2                      Josh 
3                      April

Now lets create a stored procedure that simply returns all rows from our test table as a refcursor

CREATE OR REPLACE
PROCEDURE GetBryanTestRecord (p_recordset1 OUT SYS_REFCURSOR
              ) AS
BEGIN
  OPEN p_recordset1 FOR
  SELECT  *
    FROM BryanTestTable;

END GetBryanTestRecord;

and test it from oracle before we move on to teamworks.

>VARIABLE r1 REFCURSOR;
>execute GetBryanTestRecord(:r1);
>print r1;

ID                     NAME
-------------------------
1                      Bryan
2                      Josh 
3                      April

Now that we have a working procedure, lets jump into Teamworks.  Lets create a simple service to test calling a stored procedure using an OotB sql service provided by Teamworks.  Start by creating a new service in teamworks.

Next, from the teamworks library pane, navigate to "Teamworks->System->SQL Connector->Services->Call" and drag the service "SQL Call Stored Procedure (SQLResult)" into your new service.  Then drag a "Server Script" widget from the Palette into your new service and link them as shown below.

Now check the data mapping pane tab of the "SQL Call Stored Procedure" service to identify the variables we need (for you, they will be empty for now, and thats okay). 

Now lets define our variables based on the above data mapping requirements.  To simplify our variables, I'll use an sqlStatement variable type (found in "Teamworks->System->SQL Connector->Variable Types") to handle most of the input, a string to handle the datasource name and an sqlResult list type for the output as shown below.

Once the variables are defined, we can initialize them with values in the Server Script object as shown below.  You will need to change the tw.local.ds value to match your datasource.

Finally, go back to the data mapping tab ofthe SQL call service and make sure that you map all your variables as shown.

...and we're done!  Now all we need to do is run the service in debug mode, step through the service and confirm the debug output shows what we expect to see from the stored procedure.  When you reach Item Type "Exit Point", if you didnt hit an oracle/teamworks error, you should see the following XML output for the sqlResult value.

<object type="SQLResult[]">
  <arrayElement size="1">
    <item type="SQLResult">
      <property name="rows" type="IndexedMap[]">
        <arrayElement size="3">
          <item type="IndexedMap">
            <property name="indexedMap" type="Record">
              <property name="NAME" type="String">Bryan</property>
              <property name="ID" type="String">1</property>
            </property>
            <property name="data" type="ANY[]">
              <arrayElement size="2">
                <item type="String">1</item>
                <item type="String">Bryan</item>
              </arrayElement>
            </property>
          </item>
          <item type="IndexedMap">
            <property name="indexedMap" type="Record">
              <property name="NAME" type="String">Josh</property>
              <property name="ID" type="String">2</property>
            </property>
            <property name="data" type="ANY[]">
              <arrayElement size="2">
                <item type="String">2</item>
                <item type="String">Josh</item>
              </arrayElement>
            </property>
          </item>
          <item type="IndexedMap">
            <property name="indexedMap" type="Record">
              <property name="NAME" type="String">April</property>
              <property name="ID" type="String">3</property>
            </property>
            <property name="data" type="ANY[]">
              <arrayElement size="2">
                <item type="String">3</item>
                <item type="String">April</item>
              </arrayElement>
            </property>
          </item>
        </arrayElement>
      </property>
      <property name="type" type="String">RESULT_SET</property>
      <property name="columns" type="SQLResultSetColumn[]">
        <arrayElement size="2">
          <item type="SQLResultSetColumn">
            <property name="writable" type="Boolean">true</property>
            <property name="autoIncrement" type="Boolean">false</property>
            <property name="currency" type="Boolean">true</property>
            <property name="tableName" type="String" />
            <property name="schemaName" type="String" />
            <property name="columnLabel" type="String">ID</property>
            <property name="catalogName" type="String" />
            <property name="searchable" type="Boolean">true</property>
            <property name="columnClassName" type="String">java.math.BigDecimal</property>
            <property name="caseSensitive" type="Boolean">false</property>
            <property name="signed" type="Boolean">true</property>
            <property name="columnDisplaySize" type="Integer">22</property>
            <property name="scale" type="Integer" tw-id="id:0">0</property>
            <property name="columnName" type="String">ID</property>
            <property name="columnTypeName" type="String">NUMBER</property>
            <property name="readOnly" type="Boolean">false</property>
            <property name="definitelyWritable" type="Boolean">false</property>
            <property name="precision" type="Integer">38</property>
            <property name="nullable" type="Integer" tw-ref="id:0" />
          </item>
          <item type="SQLResultSetColumn">
            <property name="writable" type="Boolean">true</property>
            <property name="autoIncrement" type="Boolean">false</property>
            <property name="currency" type="Boolean">false</property>
            <property name="tableName" type="String" />
            <property name="schemaName" type="String" />
            <property name="columnLabel" type="String">NAME</property>
            <property name="catalogName" type="String" />
            <property name="searchable" type="Boolean">true</property>
            <property name="columnClassName" type="String">java.lang.String</property>
            <property name="caseSensitive" type="Boolean">true</property>
            <property name="signed" type="Boolean">true</property>
            <property name="columnDisplaySize" type="Integer">128</property>
            <property name="scale" type="Integer" tw-ref="id:0" />
            <property name="columnName" type="String">NAME</property>
            <property name="columnTypeName" type="String">VARCHAR2</property>
            <property name="readOnly" type="Boolean">false</property>
            <property name="definitelyWritable" type="Boolean">false</property>
            <property name="precision" type="Integer">128</property>
            <property name="nullable" type="Integer" tw-id="id:0">1</property>
          </item>
        </arrayElement>
      </property>
      <property name="columnIndexes" type="TWMap">
        <element>
          <key type="String">NAME</key>
          <value type="Integer" tw-ref="id:0" />
        </element>
        <element>
          <key type="String">ID</key>
          <value type="Integer" tw-ref="id:0" />
        </element>
      </property>
    </item>
  </arrayElement>
</object>