5(c). Procedure Reference Information - <procedure-reference-information>

In addition to inserting data into tables directly, Connect XML-2-DB also supports invoking stored procedures that can validate the data and then insert the data into database tables. For each database procedure that has to be invoked, a procedure reference must be defined in the mapping file by creating a <procedure-reference-information> element. Once these procedures have procedure references defined for them, they can be referred to in the other mapping rules.

For SQL Server, the element <procedure-reference-information> has the format:

       <!ELEMENT procedure-reference-information    EMPTY>
       <!ATTLIST procedure-reference-information
                 proc-reference  CDATA #REQUIRED
                 proc-name       CDATA #REQUIRED
                 database-name   CDATA #IMPLIED
                 object-owner    CDATA #IMPLIED >

For Oracle, the element <procedure-reference-information> has the format:

       <!ELEMENT procedure-reference-information    EMPTY>
       <!ATTLIST procedure-reference-information
                 proc-reference  CDATA #REQUIRED
                 proc-name       CDATA #REQUIRED
                 package-name    CDATA #IMPLIED
                 object-owner    CDATA #IMPLIED >

The attribute proc-reference, of the element <procedure-reference-information>, is a user-defined reference name that will be used to refer to this procedure in other mapping rules.

For Oracle databases, the attribute object-owner must have the schema name. It defaults to the login id that you connected to, which is provided in the database configuration file. For SQL Server databases, the attribute object-owner must have the object owner name. It defaults to the login id that you connected to, which is provided in the database configuration file.

The attribute proc-name must be set to the name of the procedure. For Oracle databases, the value of proc-name must be in uppercase.

For SQL Server, the element <procedure-reference-information> has an additional attribute - database-name. This is an optional attribute, whose value defaults to the database that the user connected to. This is the database in the connection string in the database configuration file.

For Oracle, the element <procedure-reference-information> has an additional attribute - package-name. This is an optional attribute. If it is not provided, the procedure being invoked is assumed to be a standalone procedure which is not part of any Oracle package. If the package-name attribute is provided, its value must be specified in uppercase.

Some examples of procedure-reference-information elements are listed below:

      <!--       For SQL Server       -->
      <!--  AddBillingAddress is a procedure reference that refers to the procedure 
	 Billing.TESTUSER.AddAddress  -->	  
      <procedure-reference-information
              proc-reference="AddBillingAddress"
              proc-name="AddAddress"
              database-name="Billing"
              object-owner="TESTUSER">
      </procedure-reference-information>
	  
      <!--       For SQL Server       -->
      <!--  AddSalesAddress is a procedure reference that refers to the procedure 
	 Sales.TESTUSER.AddAddress  -->	  
      <procedure-reference-information
              proc-reference="AddSalesAddress"
              proc-name="AddAddress"
              database-name="Sales"
              object-owner="TESTUSER">
      </procedure-reference-information>

      <!--       For Oracle          -->
      <!--  ADD_USER1_ADDRESS is a procedure reference that refers to the procedure 
	 USER1.ADD_ADDRESS  -->	  
      <procedure-reference-information
              proc-reference="ADD_USER1_ADDRESS"
              proc-name="ADD_ADDRESS"
              object-owner="USER1">
      </procedure-reference-information>
	  
      <!--       For Oracle          -->
      <!--  ADD_USER2_ADDRESS is a procedure reference that refers to the procedure 
	 ADD_ADDRESS in the package BILLING_PACKAGE and in the schema USER2-->	  	  
      <procedure-reference-information
              proc-reference="ADD_USER2_ADDRESS"
              proc-name="ADD_ADDRESS"
              package-name="BILLING_PACKAGE"
              object-owner="USER2">
      </procedure-reference-information>