Database Procedure Calls

Sometimes database procedures need to be accessed from the Xephr® entities. To create a database procedure call, first determine the needed database procedure, and then determine the package name in which it is located.

Once you have this information, determine the variables that need to be passed to the database procedure.

Procedures return multiple values where a function only returns one.

The procedure definition might look something like this:

procedure tender_cash(t_order_in           in varchar2,

                      t_tendered_in        in varchar2,

                      t_change_in          in out varchar2,

                      t_change_string      in out varchar2);

All variables with an 'in' in them need to be passed to the procedure.

Those with an 'in out' need to be passed in and returned from the procedure, so they will need to have the value passed in and returned to the same field on the block.

Hyperlinks and Xephr functions should always use single quotes ( ' ) instead of double quotes ( " ).

Create the database procedure call as follows:

1. When creating this link, the Hyperlink To field will contain the function call, and the Hyperlink Scripted pop-list needs to be set to true.

2. Start with the word CallProcedure

3. Add an open parenthesis (

4. Enter the package name in which the database procedure is defined followed by a period followed by the database procedure name, in quotes, followed by a comma.  package_name.procedure',

5. For each variable listed that must be passed to the procedure, enter the code to obtain the field value that will be passed followed by a comma $$BLOCK.FIELD$$, or $$BLOCK.FIELD.first$$,

• These must be in the same order as they are listed in the procedure definition.

• If the value being passed is being hard-coded instead of coming from the entity, you can instead enter the hard-coded value in quotes followed by a comma.  'value',

• If the value being passed in is the system date from the database, the value is entered as a pound sign followed by the word sysdate, in quotes followed by a comma. '#sysdate'

• If the value being passed in is also being passed out, it needs to come from a block.field reference and should not be hard coded as it will not be able to be returned to the a field on the block if that is the case.

6. For each variable listed that must be passed in and returned from the procedure, enter the code to obtain the field value that will be passed in.  The reference begin with the + character, so that the database call knows to return the value to the defined field and will be followed by a comma $$+BLOCK.FIELD$$, or $$+BLOCK.FIELD.first$$,

• These must be in the same order as they are listed in the procedure definition.

7. Repeat steps 5 and 6 for any additional variables and values that you are passing.  The final value will NOT be followed by a comma.

8. When all values have been entered, add a close parenthesis )

9. Always close with a semi-colon ;

As an example, when calculating the change to return to a customer on the sales_order entity, call the database procedure tender_cash, which is located in the package proc_pos.sql.

The database procedure looks like this:

procedure tender_cash(t_order_in           in varchar2,

                      t_tendered_in        in varchar2,

                      t_change_in          in out varchar2,

                      t_change_string      in out varchar2);

Which means that t_order_in, t_tendered_in, t_change_in, and t_change_string are passed in, and t_change_in and t_change_string will be returned from the procedure to fields on the block.

• Start with the word CallProcedure

CallProcedure

• Add the open parenthesis

CallProcedure(

• Add the package name, period, and database procedure name in quotes followed by a comma.

CallProcedure('proc_pos.tender_cash',

• Add the variable value for the first variable that will be passed to the database procedure, either hard coded or from a block and field on the entity, followed by a comma.  If it will also be returned, you will need to include a plus sign at the beginning of the reference.  In this case, the t_order_in value comes from the order_header block in the order_no field, so no quotes.  Since the value will not be returned to the block and field, no plus sign.

CallProcedure('proc_pos.tender_cash',$$ORDER_HEADER.ORDER_NO$$,

• Add the variable value for the next variable that will be passed to the database procedure, either hard coded or from a block and field on the entity, followed by a comma.  If it will also be returned, you will need to include a plus sign at the beginning of the reference.  In this case, the t_tendered_in value comes from the order_header block in the amt_tendered field, so no quotes.  Since the value will not be returned to the block and field, no plus sign.

CallProcedure('proc_pos.tender_cash', $$ORDER_HEADER.ORDER_NO$$, $$ORDER_HEADER.AMT_TENDERED$$,

• Add the variable value for the next variable that will be passed to the database procedure, either hard coded or from a block and field on the entity, followed by a comma.  If it will also be returned, you will need to include a plus sign at the beginning of the reference. In this case, the t_change_in value comes from the order_header block in the change_given field, so no quotes.  Since the value will be returned to the block and field, we will include a plus sign.

• CallProcedure('proc_pos.tender_cash', $$ORDER_HEADER.ORDER_NO$$, $$ORDER_HEADER.AMT_TENDERED$$, $$+ORDER_HEADER.CHANGE_GIVEN$$,

• Add the variable value for the final variable that will be passed to the database procedure, either hard coded or from a block and field on the entity, followed by a comma.  If it will also be returned, you will need to include a plus sign at the beginning of the reference. In this case, the t_change_string value comes from the order_header block in the change_string field, so no quotes.  Since the value will be returned to the block and field, we will include a plus sign.  Because it is the final variable value, it is NOT followed by a comma.

CallProcedure('proc_pos.tender_cash', $$ORDER_HEADER.ORDER_NO$$, $$ORDER_HEADER.AMT_TENDERED$$, $$+ORDER_HEADER.CHANGE_GIVEN$$, $$+ORDER_HEADER.CHANGE_STRING$$

• Add the close parenthesis.

CallProcedure('proc_pos.tender_cash', $$ORDER_HEADER.ORDER_NO$$, $$ORDER_HEADER.AMT_TENDERED$$, $$+ORDER_HEADER.CHANGE_GIVEN$$, $$+ORDER_HEADER.CHANGE_STRING$$)

• End the statement with a semi-colon.

CallProcedure('proc_pos.tender_cash', $$ORDER_HEADER.ORDER_NO$$, $$ORDER_HEADER.AMT_TENDERED$$, $$+ORDER_HEADER.CHANGE_GIVEN$$, $$+ORDER_HEADER.CHANGE_STRING$$);

Xephr is a registered trademark of NDS Systems, LC.

Copyright © 2007 NDS Systems LC.